Oracle 데이터베이스에서 파티션 인덱스 최적화는 파티션 테이블과 연관된 인덱스의 성능을 향상시키기 위한 필수적인 작업입니다. 올바른 인덱스 전략은 쿼리 성능, 관리 용이성, DML 성능 등을 크게 개선할 수 있습니다. 아래는 파티션 인덱스 최적화 방법입니다.
1. 인덱스 유형 선택
파티션 테이블에 대해 올바른 인덱스 유형을 선택하는 것이 최적화의 핵심입니다.
1.1 로컬 인덱스(Local Index)
• 각 파티션에 대해 별도의 인덱스를 유지. • 장점: • 파티션 프루닝(Partition Pruning)을 통해 특정 파티션만 검색. • 관리가 쉽고, 파티션 추가/삭제 시 인덱스를 재구성할 필요가 없음. • 적합한 경우: • 쿼리가 특정 파티션만 타겟팅하는 경우.
CREATE INDEX idx_sales_local ON sales(sale_date) LOCAL;
1.2 글로벌 인덱스(Global Index)
• 모든 파티션에 걸쳐 하나의 통합된 인덱스를 유지. • 장점: • 파티션을 넘어선 쿼리(예: 집계나 조인)가 많은 경우 적합. • 단일 인덱스를 활용해 범위 검색 성능을 최적화. • 단점: • 파티션을 추가/삭제/병합할 때 인덱스를 재구성해야 함.
CREATE INDEX idx_sales_global ON sales(sale_date) GLOBAL;
1.3 글로벌 파티션 인덱스(Global Partitioned Index)
• 글로벌 인덱스를 파티션화하여 관리 용이성과 성능을 모두 개선. • 적합한 경우: • 글로벌 검색 쿼리를 자주 사용하지만 데이터 관리 효율성도 중요한 경우.
CREATE INDEX idx_sales_global_partitioned ON sales(sale_date) GLOBAL PARTITION BY RANGE (sale_date) (
PARTITION p1 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);
2. 쿼리 패턴에 따른 설계
인덱스 설계는 쿼리에서 사용하는 조건에 따라 달라집니다.
2.1 파티션 키와 동일한 열에 인덱스 생성
• WHERE 절에 자주 사용되는 파티션 키에 인덱스를 생성하면 파티션 프루닝을 극대화할 수 있습니다. • 예:
CREATE INDEX idx_sales_partition_key ON sales(sale_date) LOCAL;
2.2 파티션 키가 아닌 열 인덱싱
• 파티션 키 외의 열이 WHERE, JOIN, GROUP BY, ORDER BY에서 자주 사용되면 인덱스를 추가로 생성. • 예:
CREATE INDEX idx_sales_amount ON sales(amount) LOCAL;
3. 인덱스 유지보수 최소화
파티션 테이블의 관리 작업(예: 파티션 추가/삭제/병합)으로 인해 인덱스 유지보수 비용이 증가할 수 있습니다. 이를 최소화하려면:
3.1 로컬 인덱스를 선호
• 파티션 관리 작업 시 글로벌 인덱스는 재구성이 필요하지만 로컬 인덱스는 영향을 받지 않습니다.
ALTER TABLE sales DROP PARTITION p_old;
3.2 글로벌 인덱스 재구성 자동화
• 글로벌 인덱스를 사용하는 경우, ONLINE 옵션으로 다운타임 없이 인덱스를 재구성.
ALTER INDEX idx_sales_global REBUILD ONLINE;
3.3 자동 인덱스 관리 도구 사용
• Oracle의 Automatic Indexing 기능을 활용하여 최적의 인덱스를 자동 생성 및 관리.
4. 로우 이동 방지
글로벌 인덱스를 사용하는 경우, 파티션 간 데이터 이동(Row Movement)이 발생하면 인덱스가 비효율적으로 작동할 수 있습니다. 이를 방지하려면: • ROW MOVEMENT를 비활성화:
ALTER TABLE sales DISABLE ROW MOVEMENT;
5. 인덱스 파티셔닝과 서브파티셔닝
5.1 서브파티션 인덱스
• 복합 파티션 테이블의 경우, 서브파티션별로 인덱스를 나누어 더 세밀한 관리 가능.
CREATE INDEX idx_sales_subpartition ON sales(region) LOCAL;
5.2 인덱스 압축
• 데이터 중복이 많은 경우 인덱스를 압축하여 저장 공간을 절약하고 성능을 향상.
CREATE INDEX idx_sales_compressed ON sales(sale_date) LOCAL COMPRESS 2;
6. 파티션 프루닝(Partition Pruning) 활용
• 쿼리가 특정 파티션만 검색하도록 설계하면 성능이 크게 향상됩니다. • 프루닝이 효과적으로 작동하려면: • WHERE 조건이 파티션 키와 직접적으로 연결. • EXPLAIN PLAN으로 파티션 스캔 동작 확인:
EXPLAIN PLAN FOR SELECT * FROM sales WHERE sale_date >= TO_DATE('2023-01-01', 'YYYY-MM-DD'); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
7. 통계 수집
정확한 실행 계획 생성을 위해 정기적으로 인덱스와 파티션 통계를 수집하세요.
BEGIN
DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'INDEX_NAME');
END;
8. DML 작업과 인덱스 영향 최소화
8.1 대량 삽입 전 인덱스 비활성화
• 대량 데이터 삽입 시 인덱스를 비활성화한 후 다시 활성화하여 삽입 속도를 높임.
ALTER INDEX idx_sales_local UNUSABLE; ALTER INDEX idx_sales_local REBUILD;
8.2 파티션 단위 DML 처리
• 데이터를 파티션별로 나누어 작업하여 성능 최적화.
9. 인덱스 모니터링
• V$OBJECT_USAGE를 사용하여 인덱스 활용도를 모니터링하고 사용되지 않는 인덱스를 제거.
ALTER INDEX idx_sales_local MONITORING USAGE; SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = 'IDX_SALES_LOCAL'; ALTER INDEX idx_sales_local NOMONITORING USAGE;
10. 쿼리 힌트를 사용한 인덱스 최적화
• 쿼리에서 특정 인덱스를 강제 사용하거나 제외하는 힌트를 활용.
SELECT /*+ INDEX(sales idx_sales_local) */ * FROM sales WHERE sale_date = TO_DATE('2023-01-01', 'YYYY-MM-DD');
위의 방법을 따르면 파티션 테이블과 인덱스의 성능을 최적화할 수 있습니다. 로컬 인덱스를 선호하되 쿼리 패턴과 작업 부하에 맞는 설계가 핵심입니다.