Oracle (2) - 인덱스의 종류 (B-TREE)

B-TREE 인덱스의 종류

< B-TREE 인덱스 >

1. Unique Index

• 인덱스 안에 있는 컬럼 KEY 값에 중복되는 데이터가 없습니다.
• Unique 제약조건과 유사하며, Unique 제약조건 사용시에도 자동으로 Unique Index가 생성됩니다.
• (오라클 기준) 기본키를 생성해도 자동으로 Unique Index를 생성하게 되는데 이때 Unique나 기본키 객체명과 동일하게 생성됩니다.

생성 방법

SQL > CREATE UNIQUE INDEX [인덱스명] ON [테이블명][COLUMN ASC|DESC, COLUMN ASC|DESC...];

예시)
SQL > CREATE UNIQUE INDEX IDX_DEPT ON DEPT(DEPT_KEY ASC);

---

2. Non Unique Index

• 중복되는 데이터가 들어가야 하는 경우 사용합니다. (KEY가 중복된 값이 들어갈 수 있습니다.)

생성 방법

SQL > CREATE INDEX [인덱스명] ON [테이블명][COLUMN ASC|DESC, COLUMN ASC|DESC ...];

예시)
SQL > CREATE INDEX IDX_DEPT ON DEPT(DEPT_KEY ASC);

---

3. Function Based Index (FBI : 함수기반 인덱스)

• 인덱스는 WHERE 절에 오는 조건 컬럼이나, 조인에 쓰이는 컬럼으로 만들어야 합니다.
• 인덱스를 사용하려면 WHERE 절의 조건을 절대로 다른 형태로 가공해서 사용하면 안됩니다.

생성 방법

SQL > CREATE INDEX [인덱스명]  ON [테이블명][조건문];

예시)
SQL > CREATE INDEX IDX_DEPT ON DEPT(DEPT_KEY+1);

주의사항

1. 임시적인 해결책은 될 수 있어도 근본적인 해결방안은 아닙니다.
2. 생성시에 설정한 쿼리와 조건이 다르면 인덱스를 다시 생성해야 합니다.
3. FBI는 기존의 인덱스를 활용할 수 없습니다.

---

4. Descending Index

• 내림차순으로 인덱스를 생성합니다.
• 큰 값을 많이 조회하는 SQL에 생성하는 것이 좋습니다.

생성 방법

SQL > CREATE INDEX [인덱스명] ON [테이블명][COLUMN DESC];

예시)
SQL > CREATE INDEX IDX_DETP ON DEPT(DEPT_KEY DESC);

주의사항

1. 하나의 메뉴에 오름차순과 내림차순을 한번에 조회할 경우 두 개의 인덱스를 만들면 DML의 성능에 악영향을 미칩니다.
2. 힌트를 사용하여 오름차순과 내림차순으로 조회할 수 있습니다.

---

5. Composite Index (결합 인덱스)

• 인덱스 생성시에 두개 이상의 컬럼을 합쳐서 생성하는 인덱스입니다.
• 주로 WHERE 절의 조건이 되는 컬럼이 2개 이상 AND로 연결되는 경우 사용합니다.
• 자주 사용하는 컬럼을 앞에 위치시키는 것이 효율이 좋습니다.

생성 방법

SQL > CREATE INDEX [인덱스명] ON [테이블명][COLUMN1, COLUMN2];

예시)
SQL > CREATE INDEX IDX_EMP_COMPOSITE ON EMP(GENDER, DNAME);

생성된 인덱스는 생성시 기술한 컬럼 순서로 정렬되어 데이터를 저장합니다.
GENDER를 기준으로 오름차순 정렬한 뒤, 같은 값일 때 DNAME을 오름차순으로 정렬하여 인덱스를 생성합니다.

---

참조

• https://rongscodinghistory.tistory.com/113