Oracle 인덱스 정리: 실전 예제와 설계 팁

 

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로 전환 고려

마무리

인덱스는 단순한 성능 도구가 아니라, 데이터 구조와 쿼리 전략을 반영하는 설계 요소입니다. 쿼리 패턴을 분석하고, 읽기/쓰기 성능 균형을 고려한 인덱스 설계가 중요합니다.

필요하다면 쿼리 예시나 테이블 구조를 기반으로 맞춤 인덱스 설계도 도와드릴 수 있습니다. 댓글이나 문의로 남겨주세요!