Oracle Index Partitioning (인덱스 파티션)

Oracle의 Index Partitioning(인덱스 파티션)은 대용량 테이블의 파티션 테이블에 대한 인덱스를 효율적으로 관리하는 기법입니다.
테이블이 파티션되어 있으면, 인덱스도 같은 방식으로 파티션하면 성능과 관리 효율이 향상됩니다.

1. 인덱스 파티션의 개념

• 파티션된 테이블에 대한 인덱스를 파티션 방식에 맞춰 생성하는 기술
• 쿼리 성능 최적화 및 인덱스 관리 용이성 제공
• 각 파티션별로 독립적인 인덱스를 관리하거나 전체 인덱스를 유지하면서 일부만 접근 가능

✔ 장점

• 불필요한 인덱스 접근을 방지하여 성능 최적화
• 개별 파티션별 인덱스 재구성 가능
• 병렬 처리 및 프루닝(Pruning) 지원

2. 인덱스 파티션의 종류

Oracle은 테이블의 파티션 방식에 따라 인덱스를 다르게 설정할 수 있습니다.

(1) LOCAL PARTITIONED INDEX (로컬 파티션 인덱스)

• 테이블의 파티션과 동일한 방식으로 인덱스도 파티션됨
• 각 파티션마다 독립적인 인덱스가 생성됨
• 테이블과 함께 관리 가능 (자동 파티션 추가/삭제)

📌 예제: RANGE PARTITIONED TABLE + LOCAL INDEX

CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    order_date DATE,
    amount NUMBER
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

CREATE INDEX orders_idx ON orders(customer_id)
LOCAL;

✔ 특징

• orders 테이블이 order_date 기준으로 RANGE PARTITION되어 있으므로, orders_idx 인덱스도 각 파티션별로 생성됨.
• 각각의 인덱스는 독립적으로 유지되어 특정 파티션 조회 시 불필요한 인덱스 접근이 최소화됨.

(2) GLOBAL PARTITIONED INDEX (글로벌 파티션 인덱스)

• 테이블의 파티션과 관계없이 인덱스를 별도로 파티션화
• 전 테이블을 하나의 인덱스로 관리하되, 특정 키 값 기준으로 파티션 분할

📌 예제: GLOBAL HASH PARTITIONED INDEX

CREATE INDEX orders_gidx ON orders(customer_id)
GLOBAL PARTITION BY HASH (customer_id)
PARTITIONS 4;

✔ 특징

• 고객 ID(customer_id) 기준으로 인덱스를 4개의 해시 파티션으로 나눔.
• 테이블의 파티션과 관계없이 별도로 인덱스를 관리.

(3) GLOBAL NON-PARTITIONED INDEX (글로벌 논파티션 인덱스)

• 테이블이 파티션되어 있어도 인덱스를 전체 테이블 대상으로 하나만 생성
• 파티션과 관계없이 일반 인덱스처럼 동작
• 특정 파티션만 조회할 경우 불필요한 인덱스 접근이 발생할 수 있음

📌 예제: 글로벌 논파티션 인덱스

CREATE INDEX orders_gnidx ON orders(customer_id) GLOBAL;

✔ 특징

• 전체 orders 테이블을 대상으로 하나의 인덱스만 존재.
• 특정 파티션만 검색하더라도 전체 인덱스를 스캔할 가능성이 있음 (성능 저하 가능).

---

3. 인덱스 프루닝 (Index Partition Pruning)

Index Partition Pruning(인덱스 절단)은 불필요한 인덱스 파티션을 자동으로 제외하여 성능을 최적화하는 기법입니다. Pruning은 참고로 원래 가지치기 라는 뜻이 있습니다.
Oracle은 로컬 인덱스를 사용할 경우 자동으로 인덱스 프루닝을 수행합니다.

(1) LOCAL PARTITIONED INDEX에서 프루닝 예제

SELECT * FROM orders WHERE order_date BETWEEN TO_DATE('2024-01-01', 'YYYY-MM-DD')
                                       AND TO_DATE('2024-12-31', 'YYYY-MM-DD');

✔ 결과:

• p_2024 파티션만 검색 → 해당 파티션의 인덱스만 접근 → 불필요한 인덱스 검색 제거 (프루닝)

(2) GLOBAL PARTITIONED INDEX에서 프루닝 예제

SELECT * FROM orders WHERE customer_id = 12345;

✔ 결과:

• 고객 ID 기준 해시 값으로 특정 인덱스 파티션만 검색
• 전체 인덱스를 검색하지 않으므로 성능 향상

4. 인덱스 관리 및 최적화

(1) 인덱스 파티션 추가

테이블에 새로운 파티션을 추가하면 LOCAL INDEX는 자동 추가되지만, GLOBAL INDEX는 수동으로 관리해야 함.

📌 로컬 인덱스 자동 추가

ALTER TABLE orders ADD PARTITION p_2025 VALUES LESS THAN (TO_DATE('2026-01-01', 'YYYY-MM-DD'));

• LOCAL INDEX는 자동으로 p_2025에 대한 인덱스도 생성됨.

📌 글로벌 인덱스는 수동 재구성 필요

ALTER INDEX orders_gidx REBUILD;

(2) 인덱스 파티션 삭제

📌 특정 파티션의 인덱스만 삭제

ALTER INDEX orders_idx DROP PARTITION p_2023;

(3) 인덱스 파티션 재구성

📌 인덱스 파티션만 개별적으로 재구성

ALTER INDEX orders_idx REBUILD PARTITION p_2024;

• 특정 파티션의 인덱스를 다시 생성하여 성능 최적화.

5. 로컬 인덱스 vs 글로벌 인덱스 비교

구분	로컬 파티션 인덱스 (LOCAL)	글로벌 파티션 인덱스 (GLOBAL)
테이블 파티션과 일치 여부	✅ 동일한 파티션 방식 적용	❌ 독립적인 방식 적용
프루닝 지원 여부	✅ 지원 (자동 최적화)	✅ 지원 (조건 충족 시)
관리 편의성	✅ 테이블 파티션 추가/삭제 시 자동 반영	❌ 직접 재구성 필요
특정 파티션만 인덱싱 가능	✅ 가능 (독립적 유지)	❌ 전체 인덱스 유지
범위 조회 최적화	✅ 특정 파티션 인덱스만 조회	❌ 전체 인덱스를 검색 가능

6. 결론

✅ Oracle Index Partitioning은 대용량 데이터에서 인덱스 성능을 최적화하는 필수적인 기법
✅ 로컬 인덱스는 테이블과 동일한 파티션 구조를 유지하여 관리가 용이하며, 프루닝을 통한 최적화 가능
✅ 글로벌 인덱스는 특정 조건에서 유리하지만, 관리가 어렵고 전체 인덱스 스캔 가능성이 존재
✅ 업무 환경에 맞게 적절한 인덱스 파티션을 선택하는 것이 중요! 🚀