[Oracle] 인덱스 종류 그리고 인덱스를 이용하는 & 이용못하는 쿼리

오라클 데이터베이스를 다시 공부하다보니 여태 체크 못한 것이 있더라.

앞으로계속 공부해가면서 틈새를 메워야겠다.

목차

• 오라클 인덱스 종류
• 인덱스를 이용하는 & 못 이용하는 쿼리

 

오라클 인덱스 종류

1. B-tree 인덱스

가장 일반적으로 사용하는 인덱스 유형입니다.

범위 검색 및 정확한 값 검색에 최적화되어 있습니다.

 

- 타는 예시: EMPLOYEE_ID 컬럼에 B-tree 인덱스가 설정되어 있다면, 정확한 값 검색을 위해 인덱스를 타게 됩니다.

SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID = 12345;

 

- 타지 않는 예시

EMPLOYEE_ID 컬럼에 연산이 포함되어 인덱스를 타지 못합니다.

B-tree 인덱스는 컬럼 값의 직접적인 비교가 필요합니다.

SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID + 1 = 12346;

생성문

CREATE INDEX idx_employee_id ON EMPLOYEES (EMPLOYEE_ID);

 

2. 비트맵 인덱스(Bitmap Index)

값의 종류가 적고 반복되는 경우(예: 성별, 상태 등)에 적합합니다.

다중 값 검색 및 집합 연산에 유리하지만, 트랜잭션이 많은 환경에는 적합하지 않습니다.

OLAP 환경에서 주로 사용

 

- 타는 예시: STATUS 컬럼이 비트맵 인덱스로 설정되어 있고, ACTIVE, INACTIVE처럼 값이 몇 가지로 고정되어 있다면 인덱스를 탈 수 있습니다.

SELECT * FROM EMPLOYEES
WHERE STATUS = 'ACTIVE';

 

- 타지 않는 예시

트랜잭션이 많은 환경에서는 비트맵 인덱스가 오버헤드를 일으켜 인덱스를 사용하지 않을 수 있습니다.

트랜잭션 빈도가 낮은 경우 비트맵 인덱스가 효과적입니다.

SELECT * FROM EMPLOYEES
WHERE STATUS = 'ACTIVE'
AND DEPARTMENT_ID = 10;

 

생성문

CREATE BITMAP INDEX idx_status ON EMPLOYEES (STATUS);

 

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

컬럼의 값을 함수로 변환한 결과를 저장하는 인덱스입니다.

예를 들어, UPPER(LAST_NAME) 같은 함수 변환을 인덱스에 포함할 수 있습니다.

타는 예시: UPPER(LAST_NAME)에 대한 함수 기반 인덱스가 있다면, 이 쿼리는 인덱스를 타게 됩니다.

SELECT * FROM EMPLOYEES
WHERE UPPER(LAST_NAME) = 'KIM';

 

타지 않는 예시

함수 기반 인덱스가 UPPER(LAST_NAME)에 적용된 경우, 원본 컬럼을 직접 조회하는 이 쿼리는 인덱스를 타지 않습니다.

SELECT * FROM EMPLOYEES
WHERE LAST_NAME = 'KIM';

 

생성문

CREATE INDEX idx_upper_last_name ON EMPLOYEES (UPPER(LAST_NAME));

 

4. 역방향 키 인덱스(Reverse Key Index):

순차적으로 증가하는 숫자를 무작위로 배치해 성능 저하를 방지하는 데 사용.

키 값을 반전시켜 인덱스를 구성하여 시퀀셜 키 삽입으로 인한 성능 저하를 줄이는 데 사용.

주로 고유한 값이 순차적으로 증가하는 경우(예: 시퀀스 기반 키) 사용합니다

- 타는 예시: ORDER_ID가 순차적으로 증가하는 시퀀스를 통해 삽입될 때 역방향 키 인덱스를 활용하여 성능을 높일 수 있습니다.

SELECT * FROM ORDERS
WHERE ORDER_ID = 98765;

 

- 타지 않는 예시

범위 검색에서는 역방향 키 인덱스를 사용할 수 없습니다.

이 인덱스는 정확한 값 검색에만 유리합니다.

SELECT * FROM ORDERS
WHERE ORDER_ID > 90000;

 

생성문

CREATE INDEX idx_reverse_order_id ON ORDERS (ORDER_ID) REVERSE;

 

5. 도메인 인덱스(Domain Index):

Oracle Text나 Oracle Spatial 등과 같은 특정 도메인에 맞춰 생성.

특정 데이터 타입이나 애플리케이션 요구에 맞춰 정의한 사용자 정의 인덱스.

텍스트 데이터, 공간 데이터 등의 특수 데이터를 검색하는 데 사용합니다.

 

- 타는 예시: TEXT 컬럼에 텍스트 도메인 인덱스가 설정된 경우, CONTAINS 함수를 통해 텍스트 검색 시 인덱스를 사용할 수 있습니다.

SELECT * FROM DOCUMENTS
WHERE CONTAINS(TEXT, 'Oracle') > 0;

 

- 타지 않는 예시

텍스트 내용의 길이와 관련된 조건은 도메인 인덱스의 도움을 받지 못할 수 있습니다.

도메인 인덱스는 특정 함수나 연산에 최적화된 경우에만 사용됩니다.

SELECT * FROM DOCUMENTS
WHERE LENGTH(TEXT) > 1000;

 

생성문

예시)

• Oracle Text를 이용한 텍스트 검색 인덱스를 생성.
• TEXT 컬럼에 대해 CTXSYS.CONTEXT 도메인 인덱스를 생성하여 텍스트 검색이 가능

CREATE INDEX idx_text_content ON DOCUMENTS (TEXT)

INDEXTYPE IS CTXSYS.CONTEXT;