Oracle 인덱스 정리: 실전 예제와 설계 팁
Oracle에서 인덱스는 성능 최적화의 핵심입니다. 어떤 컬럼에, 어떤 방식으로 인덱스를 적용하느냐에 따라 쿼리 속도가 극적으로 달라질 수 있습니다. 이 글에서는 실전에서 바로 사용할 수 있는 인덱스 생성 예제와 설계 팁을 정리합니다.
기본 인덱스 생성 문법
-- 일반 B-tree 인덱스
CREATE INDEX idx_emp_hiredate ON emp(hiredate);
-- 유니크 인덱스
CREATE UNIQUE INDEX idx_emp_empno ON emp(empno);
-- 복합 인덱스
CREATE INDEX idx_emp_job_dept ON emp(job, deptno);
-- 내림차순 인덱스
CREATE INDEX idx_emp_hiredate_desc ON emp(hiredate DESC);
-- 비트맵 인덱스
CREATE BITMAP INDEX idx_emp_gender ON emp(gender);
-- 함수 기반 인덱스
CREATE INDEX idx_emp_upper_name ON emp(UPPER(name));
자주 쓰는 인덱스 예제
- 검색 가속: WHERE 조건, JOIN 키에 맞춰 생성
- 정렬 최적화: ORDER BY, BETWEEN, 최근 N건 조회
- 함수 기반 검색: 대소문자 통일, 표현식 기반 검색
- NULL 처리: NVL 등 표현식으로 인덱스 생성
설계 팁
- 선택도 높은 컬럼을 선두에 배치
- 쿼리 패턴에 맞춘 컬럼 순서
- 커버링 인덱스로 테이블 액세스 최소화
- 저카디널리티 컬럼은 비트맵 인덱스 고려 (DW 환경)
- DML이 많은 경우 인덱스 수를 최소화
- 함수 기반 인덱스는 표현식 일치 필수
인덱스 관리 및 확인
-- 인덱스 목록 조회
SELECT index_name, uniqueness, status FROM user_indexes WHERE table_name = 'EMP';
-- 인덱스 컬럼 구성 확인
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMP'
ORDER BY index_name, column_position;
-- 통계 갱신
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => USER, tabname => 'EMP', cascade => TRUE);
END;
/
자주 발생하는 오류와 해결
- ORA-01408: 유사 인덱스 중복 → 기존 인덱스 확인
- ORA-00955: 인덱스 이름 중복 → 이름 변경
- ORA-00001: UNIQUE 제약 위반 → 데이터 정제 또는 NON-UNIQUE 사용
- 함수 기반 인덱스 미사용: 표현식 불일치 → 쿼리 수정 필요
- OLTP에서 비트맵 인덱스 성능 저하: 병행성 문제 → B-tree로 전환 고려
마무리
인덱스는 단순한 성능 도구가 아니라, 데이터 구조와 쿼리 전략을 반영하는 설계 요소입니다. 쿼리 패턴을 분석하고, 읽기/쓰기 성능 균형을 고려한 인덱스 설계가 중요합니다.
필요하다면 쿼리 예시나 테이블 구조를 기반으로 맞춤 인덱스 설계도 도와드릴 수 있습니다. 댓글이나 문의로 남겨주세요!
댓글 없음:
댓글 쓰기