[ORACLE] INDEX() 성능 향상을 위한 인덱스 종류와 예제 완전 정복

[Oracle] INDEX

 

1. INDEX란 무엇인가?

Oracle에서 INDEX(인덱스)는 테이블의 데이터를 빠르게 검색할 수 있도록 도와주는 객체입니다. 인덱스는 책의 목차처럼 특정 데이터를 빠르게 찾을 수 있도록 구조화되어 있으며, 쿼리 성능을 향상하는 데 중요한 역할을 합니다.

2. 인덱스의 작동 원리

인덱스는 테이블의 칼럼 값을 기준으로 별도의 구조(B-tree, Bitmap 등)에 저장되며, 검색 시 전체 테이블을 스캔하는 것이 아니라 인덱스를 통해 필요한 데이터를 빠르게 조회합니다.

3. 인덱스의 종류

종류	설명	사용 예
B*Tree 인덱스	가장 일반적인 인덱스. 균형 잡힌 트리 구조	일반적인 WHERE 절 검색
Bitmap 인덱스	값의 종류가 적은 컬럼에 유리. 공간 효율적	성별, 지역 코드 등
Function-Based 인덱스	컬럼에 함수가 적용된 경우에 사용	UPPER(name), TO_CHAR(date)
Composite 인덱스	2개 이상의 컬럼을 결합하여 구성	(first_name, last_name)
Unique 인덱스	중복을 허용하지 않는 인덱스	주민등록번호, 이메일

4. 기본 인덱스 생성 예제

CREATE INDEX idx_emp_name ON employees(last_name);

employees 테이블의 last_name 칼럼에 대한 B*Tree 인덱스를 생성합니다.

5. 복합 인덱스 생성 예제

CREATE INDEX idx_emp_fullname ON employees(first_name, last_name);

first_name과 last_name 칼럼을 동시에 검색할 때 성능을 향상합니다.

6. Function-Based 인덱스 예제

CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

WHERE UPPER(last_name) = 'KIM'과 같은 쿼리에 효과적입니다.

7. Bitmap 인덱스 예제

CREATE BITMAP INDEX idx_gender ON employees(gender);

gender와 같이 값의 종류가 적은 칼럼에서 빠른 결과를 제공합니다.

8. 인덱스 사용 확인 방법

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'Lee';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

쿼리 실행 계획을 통해 인덱스가 사용되었는지 확인할 수 있습니다.

9. 인덱스 제거

DROP INDEX idx_emp_name;

불필요하거나 사용되지 않는 인덱스는 제거하여 성능을 유지할 수 있습니다.

10. 인덱스 관련 시스템 뷰

• DBA_INDEXES: 모든 인덱스 정보
• USER_INDEXES: 사용자 인덱스 목록
• DBA_IND_COLUMNS: 인덱스에 포함된 칼럼 정보
• V$OBJECT_USAGE: 인덱스 사용 여부 추적

11. 인덱스 튜닝 팁

• WHERE 조건에 자주 사용되는 칼럼에 인덱스를 적용
• 데이터 분포도가 높은 칼럼에 B*Tree, 낮은 칼럼에는 Bitmap 인덱스 적용
• LIKE 검색 시 와일드카드가 앞에 오면 인덱스 무효화 주의
• 자주 조인되는 칼럼에도 인덱스를 고려
• 인덱스 과다 생성은 오히려 성능 저하 유발

12. 인덱스가 성능에 미치는 영향

적절한 인덱스는 쿼리 응답 시간을 획기적으로 줄일 수 있지만, 과도한 인덱스는 DML 작업(INSERT, UPDATE, DELETE)에 부하를 주기 때문에 반드시 필요성과 활용도를 고려하여 설계해야 합니다.

출처

• Oracle 공식 문서: https://docs.oracle.com/en/database/
• Oracle Performance Tuning Guide
• 실전 Oracle 인덱스 최적화 - 박용석 저