인덱스 관리 지침

인덱스 생성 및 관리 지침

오라클 데이터베이스에서 인덱스는 쿼리 성능을 획기적으로 향상시킬 수 있는 핵심적인 요소입니다. 하지만 인덱스를 무분별하게 사용하면 오히려 성능 저하를 초래할 수 있습니다. 따라서 인덱스를 효과적으로 생성하고 관리하는 것이 중요합니다. 이 글에서는 오라클 데이터베이스에서 인덱스를 생성, 관리하고 성능을 최적화하는 방법에 대한 실질적인 지침과 다양한 예시를 제공합니다.

1. 인덱스 생성 시 고려 사항

인덱스를 생성하기 전에 다음 사항들을 신중하게 고려해야 합니다.

  • 인덱싱할 테이블 및 컬럼 선택: 테이블에서 가장 자주 사용되는 쿼리에서 WHERE 절에 사용되는 컬럼을 우선적으로 인덱싱합니다.
  • 인덱스 컬럼 순서: 다중 컬럼 인덱스에서 가장 선택적인(cardinality가 높은) 컬럼을 먼저 배치합니다.
  • 인덱스 유형 선택: B-트리 인덱스, 비트맵 인덱스, 함수 기반 인덱스 등 다양한 유형의 인덱스가 있습니다. 쿼리 패턴과 데이터 특성에 맞는 최적의 인덱스 유형을 선택합니다.
  • 인덱스 크기 및 스토리지 매개변수 설정: 인덱스 크기는 쿼리 성능에 영향을 미치므로 적절한 크기로 설정하고, 테이블스페이스를 지정하여 스토리지 관리를 용이하게 합니다.
  • 인덱스 재구성 필요성: 데이터 변경이 잦은 테이블의 인덱스는 조각화될 수 있으므로 주기적으로 재구성을 고려합니다.

2. 인덱스 유형별 생성 예시

오라클 데이터베이스에서 다양한 유형의 인덱스를 생성하는 방법을 예시 코드를 통해 자세히 설명합니다.

2.1. B-트리 인덱스 생성

가장 일반적인 인덱스 유형으로, 대부분의 경우 기본적으로 사용됩니다.


CREATE INDEX idx_employees_last_name ON employees(last_name);

설명: employees 테이블의 last_name 컬럼에 idx_employees_last_name이라는 B-트리 인덱스를 생성합니다.

2.2. 다중 컬럼 인덱스 생성

여러 컬럼을 기반으로 인덱스를 생성하여 WHERE 절에 여러 조건이 함께 사용되는 쿼리 성능을 향상시킵니다.


CREATE INDEX idx_employees_dept_job ON employees(department_id, job_id);

설명: employees 테이블의 department_id와 job_id 컬럼에 idx_employees_dept_job이라는 다중 컬럼 인덱스를 생성합니다.

2.3. 비트맵 인덱스 생성

Cardinality가 낮은 컬럼(예: 성별, 상태)에 적합하며, 데이터 웨어하우스 환경에서 주로 사용됩니다.


CREATE BITMAP INDEX idx_customers_gender ON customers(gender);

설명: customers 테이블의 gender 컬럼에 idx_customers_gender이라는 비트맵 인덱스를 생성합니다.

2.4. 함수 기반 인덱스 생성

컬럼의 특정 함수 결과에 대한 쿼리 성능을 향상시키기 위해 사용됩니다.


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

설명: employees 테이블의 last_name 컬럼을 대문자로 변환한 결과에 idx_employees_upper_name이라는 함수 기반 인덱스를 생성합니다.

3. 인덱스 관리 및 유지보수

인덱스를 생성한 후에도 지속적인 관리 및 유지보수가 필요합니다.

  • 인덱스 사용 모니터링: V$INDEX_USAGE 뷰를 사용하여 인덱스 사용 여부를 모니터링하고, 사용되지 않는 인덱스는 제거합니다.
  • 인덱스 재구축: ALTER INDEX REBUILD 명령을 사용하여 인덱스를 재구축하고 조각화를 해소합니다. 특히 데이터 변경이 잦은 테이블의 인덱스는 주기적인 재구축이 필요합니다. 
    
ALTER INDEX idx_employees_last_name REBUILD;
  • 통계 정보 갱신: DBMS_STATS 패키지를 사용하여 테이블 및 인덱스 통계 정보를 갱신합니다. 정확한 통계 정보는 쿼리 옵티마이저가 최적의 실행 계획을 수립하는 데 필수적입니다. 
    
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'hr', TABNAME => 'employees');

4. 실전 팁

  • 과도한 인덱스 사용 지양: 테이블당 인덱스 수를 최소화하고, 쿼리 성능에 실질적인 영향을 미치는 인덱스만 유지합니다.
  • 결합 인덱스의 활용: 여러 컬럼을 함께 조회하는 쿼리의 경우, 결합 인덱스를 활용하여 성능을 극대화합니다.
  • 함수 기반 인덱스의 신중한 사용: 함수 기반 인덱스는 특정 쿼리 성능을 향상시키지만, DML 작업 시 오버헤드를 발생시킬 수 있으므로 신중하게 사용합니다.
  • 파티셔닝과 인덱싱의 조화: 파티셔닝된 테이블에서 로컬 인덱스를 사용하여 파티션 pruning을 활성화하고, 쿼리 성능을 향상시킵니다.

5. 추가 정보

인덱스 관리는 오라클 데이터베이스 성능 최적화의 중요한 부분입니다. 이 글에서 제공된 지침과 팁을 활용하여 데이터베이스 환경에 맞는 최적의 인덱스 전략을 수립하고, 쿼리 성능을 향상시키시기 바랍니다.

더 자세한 내용은 다음 오라클 문서를 참고하십시오.

위로 스크롤