- Oracle 에서 인덱스를 최적화하려면, 쿼리 성능, 저장소 요구사항, 유지 관리 비용 간의 균형을 맞추는 것이 중요합니다.
1. 작업 부하 이해하기
• 가장 자주 실행되는 쿼리를 분석합니다. • 인덱스는 다음과 같은 열에 집중적으로 적용하세요: • WHERE 절. • JOIN 조건. • ORDER BY 및 GROUP BY 절.
2. 적절한 인덱스 유형 선택
• B-Tree 인덱스: 기본값이며 가장 일반적으로 사용됨. 선택도가 높은 쿼리에 적합. • 비트맵 인덱스(Bitmap Indexes): 낮은 카디널리티(적은 고유 값)를 가지며 읽기 작업이 많은 경우 유용(예: 데이터 웨어하우스). • 고유 인덱스(Unique Indexes): 열의 고유성을 보장하며 고유 값에 대한 쿼리 성능을 향상. • 복합 인덱스(Composite Indexes): 여러 열로 필터링하는 쿼리에 유용. • 함수 기반 인덱스(Function-Based Indexes): 계산된 값(예: UPPER(column_name))에 대해 인덱싱 가능. • 역방향 키 인덱스(Reverse Key Indexes): 높은 동시성 환경에서 순차적인 삽입으로 인한 병목 현상을 방지.
3. 필요한 경우에만 인덱스 생성
• 과도한 인덱싱을 피하세요. 인덱스는 저장소를 차지하며 DML 작업(INSERT, UPDATE, DELETE)을 느리게 만듭니다. • Oracle의 자동 워크로드 리포지토리(AWR) 또는 SQL 모니터링 도구를 사용하여 사용하지 않는 인덱스를 제거합니다.
4. 인덱스 압축 활용
• 반복되는 값이 많은 인덱스의 경우 **압축(compression)**을 사용하여 저장 공간을 줄입니다. • 예:
CREATE INDEX idx_compressed ON table_name(column1, column2) COMPRESS 1;
5. 파티셔닝과 인덱스 조합
• 파티셔닝된 테이블을 사용할 경우, 관리 용이성을 위해 **로컬 인덱스(local index)**를 고려하세요. • 파티션 간 쿼리의 경우 **글로벌 인덱스(global index)**를 사용합니다.
6. 인덱스 유지 관리
• 단편화된 인덱스 재구성: 단편화된 인덱스를 정기적으로 점검하고 재구성하여 성능을 최적화합니다.
ALTER INDEX index_name REBUILD;
• Oracle의 Automatic Segment Advisor를 사용하여 단편화를 식별합니다.
• USER_INDEXES 및 USER_IND_PARTITIONS 뷰를 통해 인덱스 상태를 모니터링하세요.
7. DML 작업에 대한 인덱스 영향 최소화
• 자주 업데이트되는 열에 인덱스를 생성하는 것을 피하세요(쿼리에서 꼭 필요한 경우 제외). • 변경이 많은 테이블의 경우, 데이터가 안정화될 때까지 인덱스 생성을 연기하세요.
8. 커버링 인덱스 사용
• 자주 액세스되는 열을 인덱스에 포함하여 테이블 액세스 없이 쿼리를 충족시킵니다.
CREATE INDEX idx_covering ON table_name(column1, column2, column3);
9. 인덱스 사용 모니터링 및 분석
• V$SQL_PLAN 및 DBA_INDEX_USAGE 뷰를 사용하여 인덱스 활용도를 평가하세요. • 사용되지 않는 인덱스를 삭제합니다:
DROP INDEX index_name;
10. 선택도가 낮은 열은 인덱싱 피하기
• 고유 값이 적은 열(예: 성별: “M” 또는 “F”)은 인덱싱하지 마세요. 단, 복합 인덱스의 일부로 사용할 경우는 제외.
11. 가상 컬럼(Virtual Columns) 활용
• 가상 컬럼과 함수 기반 인덱스를 사용하여 유지 관리를 간소화하고 성능을 향상합니다.
ALTER TABLE table_name ADD (virtual_column AS (column1 * column2)); CREATE INDEX idx_virtual ON table_name(virtual_column);
12. 온라인 인덱스 작업 사용
• 운영 환경에서는 온라인 인덱스 생성 및 재구성을 사용하여 다운타임을 최소화합니다:
CREATE INDEX index_name ON table_name(column) ONLINE; ALTER INDEX index_name REBUILD ONLINE;
13. 통계 관리
• 인덱스 통계를 정기적으로 수집하여 옵티마이저가 올바른 결정을 내릴 수 있도록 지원합니다:
BEGIN
DBMS_STATS.GATHER_INDEX_STATS('schema_name', 'index_name');
END;
14. 인덱스 모니터링 도구 사용
• 인덱스 사용을 활성화하고 모니터링합니다:
ALTER INDEX index_name MONITORING USAGE; SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'index_name'; ALTER INDEX index_name NOMONITORING USAGE;
15. 인덱스와 쿼리 최적화를 병행
• 인덱스를 최대한 활용할 수 있도록 쿼리를 재작성하세요: • 옵티마이저를 안내하기 위해 INDEX 힌트를 사용. • 함수 기반 인덱스를 사용하지 않는 한, 인덱싱된 열에 함수 적용을 피함. • 인덱싱된 열에 적절한 데이터 유형 사용.
위 모범 사례를 따르면 인덱스를 효율적으로 활용하고 성능을 최적화하며 데이터베이스를 유지 관리하기 쉽게 만들 수 있습니다.