DevelopOracle

Oracle - 인덱스 최적화

Oracle 인덱스 최적화

오라클 데이터베이스에서 인덱스를 효과적으로 생성하고 활용하는 방법을 다룹니다.

인덱스는 쿼리 성능을 크게 향상시킬 수 있는 중요한 데이터베이스 객체입니다. 적절한 인덱스 선택과 활용은 쿼리 튜닝의 핵심입니다.

적절한 인덱스 생성

-- 단일 컬럼 인덱스
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 복합 인덱스 (선택도가 높은 컬럼을 앞에)
CREATE INDEX idx_emp_dept_sal ON employees(department_id, salary);

-- 함수 기반 인덱스
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));

-- 비트맵 인덱스 (Cardinality가 낮은 컬럼)
CREATE BITMAP INDEX idx_gender ON employees(gender);

인덱스 사용 유도

-- 나쁜 예: 인덱스 컬럼에 함수 사용
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- 좋은 예: 함수 기반 인덱스 사용 또는 검색값 변환
SELECT * FROM employees WHERE last_name = UPPER('Smith');

-- 나쁜 예: 인덱스 컬럼 변형
SELECT * FROM employees WHERE salary * 12 > 100000;

-- 좋은 예: 조건 값 변형
SELECT * FROM employees WHERE salary > 100000 / 12;

인덱스 종류별 특징

B-Tree 인덱스 (기본)

-- 범위 검색에 효과적
CREATE INDEX idx_emp_salary ON employees(salary);

SELECT * FROM employees WHERE salary BETWEEN 5000 AND 10000;

비트맵 인덱스

-- Cardinality가 낮은 컬럼에 효과적
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_emp_status ON employees(status);

-- 여러 조건의 AND/OR 연산에 유리
SELECT * FROM employees
WHERE gender = 'M' AND status = 'ACTIVE';

함수 기반 인덱스

-- 함수 결과값에 인덱스 생성
CREATE INDEX idx_upper_name ON employees(UPPER(last_name));
CREATE INDEX idx_year_hire ON employees(EXTRACT(YEAR FROM hire_date));

SELECT * FROM employees WHERE UPPER(last_name) = 'KING';

복합 인덱스 설계 원칙

-- 1. 선택도가 높은 컬럼을 앞에
-- 2. 등호(=) 조건을 앞에, 범위 조건을 뒤에
-- 3. 자주 사용되는 컬럼 조합

CREATE INDEX idx_emp_composite ON employees(
  department_id,  -- 등호 조건
  job_id,        -- 등호 조건
  salary         -- 범위 조건
);

-- 효율적인 사용
SELECT * FROM employees
WHERE department_id = 10
  AND job_id = 'SA_REP'
  AND salary > 5000;

인덱스 통계 확인

-- 인덱스 목록 조회
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

-- 인덱스 컬럼 확인
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- 인덱스 상세 통계
SELECT index_name,
       blevel,           -- B-Tree 레벨
       leaf_blocks,      -- 리프 블록 수
       num_rows,         -- 인덱스 행 수
       distinct_keys,    -- 고유 키 수
       clustering_factor -- 클러스터링 팩터
FROM user_indexes
WHERE table_name = 'EMPLOYEES';

인덱스 사용 확인

-- 실행 계획에서 인덱스 사용 여부 확인
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 인덱스가 사용되지 않는 이유 확인
-- 1. 통계 정보 부족
-- 2. 인덱스 컬럼 변형
-- 3. 데이터 분포 문제
-- 4. Full Table Scan이 더 효율적

인덱스 재구성

-- 인덱스 재구성 (Online)
ALTER INDEX idx_emp_dept REBUILD ONLINE;

-- 인덱스 재생성
DROP INDEX idx_emp_dept;
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 파티션 인덱스 재구성
ALTER INDEX idx_sales_date REBUILD PARTITION p_2024_q1 ONLINE;

불필요한 인덱스 찾기

-- 사용되지 않는 인덱스 모니터링 시작
ALTER INDEX idx_emp_dept MONITORING USAGE;

-- 사용 여부 확인
SELECT * FROM v$object_usage
WHERE index_name = 'IDX_EMP_DEPT';

-- 모니터링 중지
ALTER INDEX idx_emp_dept NOMONITORING USAGE;

인덱스 설계 가이드라인

인덱스를 생성해야 하는 경우

  • WHERE 절에 자주 사용되는 컬럼
  • JOIN 조건에 사용되는 컬럼
  • ORDER BY 절에 사용되는 컬럼
  • 선택도(Selectivity)가 높은 컬럼
  • 테이블 크기가 큰 경우

인덱스를 생성하지 말아야 하는 경우

  • 테이블이 작은 경우 (수천 건 이하)
  • 컬럼 값이 자주 변경되는 경우
  • Cardinality가 매우 낮은 컬럼 (비트맵 제외)
  • 테이블의 대부분 행을 조회하는 경우

인덱스 성능 비교

-- Full Table Scan
SELECT /*+ FULL(e) */ * FROM employees e
WHERE department_id = 10;

-- Index Range Scan
SELECT /*+ INDEX(e idx_emp_dept) */ * FROM employees e
WHERE department_id = 10;

-- Index Fast Full Scan
SELECT /*+ INDEX_FFS(e idx_emp_dept_sal) */
       department_id, salary
FROM employees e;

-- 실행 시간 측정
SET TIMING ON;

관련 문서

Share