파티션 인덱스 설계: Local·Global·Prefixed·Unique

핵심 요약

파티션 테이블의 Index는 먼저 Table Partition과 같은 경계로 대응하는 Local Index와, Table Partition 경계와 독립적으로 전체 Row를 관리하는 Global Index로 구분합니다. 그다음 Partitioned Index의 Partition Key가 Index Column의 왼쪽 선두를 이루는지에 따라 Prefixed·Nonprefixed를 구분합니다.

분류 축 1: Table Partition과의 대응 관계
Local  → Table과 Equipartitioning, Partition 단위 운영에 유리
Global → Table 경계와 독립, 전역 조회·유일성에 유리할 수 있음

분류 축 2: Index Partition Key의 Column 위치
Prefixed    → Index Partition Key가 Index Column의 왼쪽 선두
Nonprefixed → 왼쪽 선두가 아님

Oracle이 지원하는 대표 Partitioned B-Tree Index 형태는 다음과 같습니다.

Local Prefixed
Local Nonprefixed
Global Prefixed

Global Nonprefixed Partitioned Index는 지원하지 않습니다. Global Partitioned Index의 Partition Key는 Index Column List의 왼쪽 선두여야 합니다. 반면 Global Nonpartitioned Index는 Index Partition 자체가 없으므로 일반적으로 하나의 전역 B-Tree로 이해합니다.

Local·Global 중 하나가 항상 우수한 것은 아닙니다. 다음 항목을 함께 비교해야 합니다.

• 조회 Predicate와 Partition Pruning 가능성
• Partition Key 없는 전역 단건·소량 조회 빈도
• Unique Constraint가 요구하는 유일성 범위
• 여러 Partition을 아우르는 정렬·Top-N 요구
• Partition DROP·TRUNCATE·EXCHANGE·MOVE 빈도
• DML 비용, 장애 범위, Rebuild와 통계 운영
• 실제 Starts·A-Rows·Buffers·A-Time

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. Local Index의 Equipartitioning과 1:1 대응 관계를 설명한다.
2. Global Nonpartitioned Index와 Global Partitioned Index를 구분한다.
3. Local·Global과 Prefixed·Nonprefixed가 서로 다른 분류 축임을 설명한다.
4. Local Prefixed·Nonprefixed Index의 Column 순서 차이를 설명한다.
5. Global Nonprefixed Partitioned Index가 지원되지 않는 이유를 구조적으로 이해한다.
6. Local Unique Index에 Table Partition Key가 포함되어야 하는 이유를 설명한다.
7. Table Partition Pruning과 Global Index Partition Pruning을 구분한다.
8. Partition Key 없는 조회에서 Local Index의 반복 Probe 비용을 판단한다.
9. Partition Maintenance가 Local·Global Index에 미치는 영향을 비교한다.
10. 실제 Cursor 통계와 Dictionary 상태로 Index 설계를 검증한다.

1. 파티션 테이블의 Index 선택지

월별 Range Partition Table을 가정합니다.

SALES
├─ P202501
├─ P202502
└─ P202503

이 Table에는 다음 형태의 B-Tree Index를 설계할 수 있습니다.

Local Partitioned Index
→ Table Partition과 같은 개수·경계로 자동 대응
→ 각 Index Partition은 대응 Table Partition의 Row만 가리킴

Global Nonpartitioned Index
→ 하나의 전역 Index Segment가 모든 Table Partition의 Row를 가리킴

Global Partitioned Index
→ Index 자체의 Key와 경계로 독립적으로 Partition
→ 하나의 Index Partition이 여러 Table Partition의 ROWID를 포함할 수 있음

파티션 Table에 LOCAL 또는 GLOBAL PARTITION BY ...를 지정하지 않고 일반 B-Tree Index를 만들면 기본적으로 Global Nonpartitioned Index입니다.

CREATE INDEX sales_gix
ON sales(customer_id);

2. Local Partitioned Index와 Equipartitioning

CREATE INDEX sales_lix
ON sales(sale_date, customer_id)
LOCAL;

Local Index는 Table과 Equipartitioning됩니다. Table Partition이 생성·삭제되면 대응 Local Index Partition도 함께 생성·삭제됩니다.

SALES.P202501 ↔ SALES_LIX.P202501
SALES.P202502 ↔ SALES_LIX.P202502
SALES.P202503 ↔ SALES_LIX.P202503

한 Local Index Partition의 Entry는 정확히 하나의 대응 Table Partition에 저장된 Row만 가리킵니다. 각 Index Partition은 별도의 Segment이므로 한 조각의 장애나 UNUSABLE 상태가 다른 조각과 분리될 수 있습니다.

장점

• Table Partition과 Index Partition의 관리 단위가 일치
• Partition별 Rebuild·Coalesce·통계 수집 가능
• Partition 단위 적재·삭제·교체와 Rolling Maintenance에 유리
• 장애·복구·병렬 작업 범위를 특정 Partition으로 제한 가능

주의할 점

• 여러 Local Index Partition을 합친 하나의 전역 Key 순서는 없음
• Table Partition을 줄이지 못하면 여러 Index Partition을 반복 Probe할 수 있음
• Partition 수가 많으면 작은 수직 탐색의 누적 Buffer가 커질 수 있음
• 전역 Unique 요구는 Partition Key 포함 여부를 별도로 검토해야 함

3. Local·Global과 Prefixed·Nonprefixed는 다른 분류 축이다

Local과 Global은 Table Partition과 Index Partition의 대응 관계를 설명합니다. Prefixed와 Nonprefixed는 Index Partition Key가 Index Column List의 왼쪽 선두를 이루는지를 설명합니다.

Table이 sale_date로 Partition되어 있다고 가정합니다.

Local Prefixed Index

CREATE INDEX sales_lix_pref
ON sales(sale_date, customer_id)
LOCAL;

Local Index의 Partition Key인 sale_date가 Index Column의 왼쪽 선두입니다. 날짜 Predicate가 있다면 Table·Index Partition Pruning과 Index Range Scan을 자연스럽게 연결할 수 있습니다.

WHERE sale_date >= DATE '2025-07-01'
  AND sale_date <  DATE '2025-08-01'
  AND customer_id = :customer_id

Local Nonprefixed Index

CREATE INDEX sales_lix_nonpref
ON sales(customer_id, sale_date)
LOCAL;

Table Partition Key는 Index Key에 포함되어 있지만 왼쪽 선두가 아니므로 Local Nonprefixed입니다. Partition Key가 Index Key에 아예 없어도 Local Nonprefixed가 될 수 있습니다.

고객 조건이 Index 선두이므로 한 달로 먼저 Pruning된 범위에서는 효율적인 후보가 될 수 있습니다.

WHERE sale_date >= :from_date
  AND sale_date <  :to_date
  AND customer_id = :customer_id

반대로 날짜 조건이 없다면 다음 비용을 확인해야 합니다.

WHERE customer_id = :customer_id

Table Partition Pruning 불가
→ 여러 Local Index Partition에서 같은 customer_id Probe
→ Index Partition 수만큼 수직 탐색과 RowID Access가 반복될 수 있음

Nonprefixed가 항상 나쁜 것은 아닙니다. 최근 한 달·특정 Partition을 항상 먼저 제한하는 업무인지, 전체 기간 조회가 빈번한지를 기준으로 판단합니다.

Global Prefixed Partitioned Index

CREATE INDEX sales_gpix
ON sales(customer_id, sale_date)
GLOBAL PARTITION BY HASH (customer_id)
PARTITIONS 8;

Global Index Partition Key인 customer_id가 Index Column List의 왼쪽 선두입니다. Global Partitioned Index는 Range 또는 Hash로 만들 수 있으며, Partition Key는 Index Column List의 왼쪽 선두여야 합니다.

지원: Global Prefixed Partitioned Index
미지원: Global Nonprefixed Partitioned Index

4. Global Nonpartitioned Index와 Global Partitioned Index

Global Nonpartitioned Index

CREATE INDEX sales_gix
ON sales(customer_id);

하나의 B-Tree Segment가 모든 Table Partition의 customer_id를 전역 순서로 관리합니다.

customer_id = 100
→ 전역 B-Tree 탐색
→ 결과 ROWID는 여러 월 Table Partition을 가리킬 수 있음

날짜 조건 없는 고객 단건·소량 조회에서는 여러 Local Index Partition을 Probe하는 것보다 유리할 수 있습니다.

Global Partitioned Index

CREATE INDEX sales_gpix
ON sales(customer_id, sale_date)
GLOBAL PARTITION BY HASH (customer_id)
PARTITIONS 8;

Global Index의 Partition 경계는 Table Partition 경계와 독립적입니다.

Table Partition
→ sale_date 월별 Range

Global Index Partition
→ customer_id Hash

하나의 Global Index Partition은 여러 월 Table Partition의 ROWID를 포함할 수 있습니다. Index Partition Pruning으로 탐색할 Index 조각을 줄여도, 얻은 ROWID가 여러 Table Partition으로 흩어질 수 있으므로 Table Access의 Clustering Factor와 Random I/O를 함께 측정합니다.

5. Table Partition Pruning과 Index Partition Pruning

두 Pruning은 기준 Object와 Key가 다릅니다.

Table Partition Pruning
→ Table Partition Key와 SQL Predicate 비교

Global Index Partition Pruning
→ Global Index Partition Key와 SQL Predicate 비교

Local Index는 Table과 Equipartitioning되므로 Table Partition이 Pruning되면 대응 Local Index Partition도 함께 줄어듭니다. 날짜 조건이 없으면 여러 Local Index Partition이 후보로 남을 수 있습니다.

Global Partitioned Index는 Table이 날짜로 Partition되어 있어도 customer_id 조건으로 Index Partition을 줄일 수 있습니다.

WHERE customer_id = :customer_id

Global Hash Index Partition Pruning
→ customer_id가 매핑되는 Index Partition 계산
→ 해당 Index Partition에서 ROWID 탐색
→ ROWID가 가리키는 Table Partition Access

Pruning과 Access Path는 별개의 판단입니다. Partition을 하나로 줄였더라도 그 안에서 Index Range Scan·Full Scan 중 어떤 방식이 유리한지는 Selectivity와 비용에 따라 결정됩니다.

6. Unique Index와 Partition Key

Local Index Partition은 자기 Table Partition의 Row만 보므로, 각 조각 안에서만 중복을 확인할 수 있습니다.

Table Partition Key: sale_date
전역 Unique 업무 Key: sale_id

sale_id만으로 Local Unique Index를 만들면 서로 다른 월 Partition의 같은 sale_id를 한 Index 조각에서 비교할 수 없습니다. Local Unique Index로 전체 Table의 유일성을 보장하려면 Table Partition Key가 Index Key 집합의 일부여야 합니다.

CREATE UNIQUE INDEX sales_luix
ON sales(sale_id, sale_date)
LOCAL;

Partition Key가 반드시 첫 Column일 필요는 없습니다. 다음 두 문장을 구분합니다.

Local Unique 가능 조건
→ Table Partition Key가 Index Key에 포함

Local Prefixed 조건
→ Table Partition Key가 Index Key의 왼쪽 선두

따라서 (sale_id, sale_date) LOCAL UNIQUE는 Unique 규칙을 만족하지만 sale_date가 선두가 아니므로 Local Nonprefixed일 수 있습니다.

전역적으로 sale_id 하나만 Unique여야 한다면 Global Unique Index가 더 직접적인 후보입니다.

CREATE UNIQUE INDEX sales_guix
ON sales(sale_id);

대신 Partition Maintenance 시 전역 구조의 유지 비용과 가용성 정책을 함께 설계해야 합니다.

7. Partition Maintenance와 Index 상태

Local Index

Table Partition ADD
→ 대응 Local Index Partition 자동 생성

Table Partition DROP
→ 대응 Local Index Partition 자동 삭제

Table Partition TRUNCATE
→ 대응 Local Index Partition의 Entry도 제거

Local Index Partition은 Table Partition과 함께 관리되므로 작업 범위를 예측하기 쉽습니다. 다만 MOVE·SPLIT·EXCHANGE 등은 Operation과 Clause에 따라 Local Index Partition이 UNUSABLE이 될 수 있으므로 실제 DDL 문법과 상태를 확인합니다.

Global Index

Global Index는 제거·이동되는 Table Row의 ROWID Entry를 전역 구조에서 유지해야 합니다. 많은 Partition Maintenance Operation에서 UPDATE INDEXES를 지정하면 Index를 DDL과 함께 갱신하여 사용 가능한 상태를 유지할 수 있습니다.

ALTER TABLE sales
DROP PARTITION p202501
UPDATE INDEXES;

Trade-off는 다음과 같습니다.

UPDATE INDEXES 사용
→ Index 가용성 유지
→ DDL 중 Index 유지 작업·Redo·Undo·자원 사용 증가 가능

UPDATE INDEXES 미사용
→ Operation에 따라 Global Index 또는 Partition이 UNUSABLE 가능
→ 이후 Rebuild 또는 재생성 필요

DROP·TRUNCATE의 비동기 Global Index 유지

Heap Table의 DROP PARTITION·TRUNCATE PARTITION은 지원 조건에서 Global Index 유지가 Metadata 중심의 비동기 방식으로 처리될 수 있습니다. 호환성을 위해 UPDATE INDEXES Clause는 여전히 지정합니다.

Partition DROP·TRUNCATE + UPDATE INDEXES
→ Global Index를 사용 가능한 상태로 유지
→ 제거된 Row를 가리키는 Entry가 Orphaned Entry로 남을 수 있음
→ Scheduler Job 또는 ALTER INDEX ... COALESCE/CLEANUP으로 후속 정리

다음 Dictionary에서 정리 필요 여부를 확인할 수 있습니다.

SELECT index_name,
       partitioned,
       status,
       orphaned_entries
FROM   user_indexes
WHERE  table_name = 'SALES';

Global Partitioned Index의 조각별 상태는 USER_IND_PARTITIONS와 USER_PART_INDEXES를 함께 확인합니다.

SELECT index_name,
       partition_name,
       status,
       num_rows,
       leaf_blocks
FROM   user_ind_partitions
WHERE  index_name LIKE 'SALES%'
ORDER BY index_name, partition_position;

비동기 유지 여부와 제약은 Table 종류·Domain Index·Operation에 따라 달라질 수 있으므로 운영 DDL 전 공식 문서와 테스트 환경에서 검증합니다.

8. 정렬·Top-N과 여러 Local Index Partition

각 Local Index Partition 안에서는 Key가 정렬되어 있지만, 서로 다른 Segment를 합친 전체에는 하나의 연속된 Key 순서가 없습니다.

P202501 Index → customer_id 1, 2, 3, ...
P202502 Index → customer_id 1, 2, 3, ...

두 Partition 전체
→ 하나의 연속된 customer_id B-Tree 순서가 아님

여러 Partition을 대상으로 ORDER BY customer_id 또는 전역 Top-N을 수행하면 Partition별 결과의 Merge나 별도 Sort가 필요할 수 있습니다.

Global B-Tree는 전역 Key 순서를 활용할 수 있지만, Table Access가 여러 Partition으로 흩어져 Random I/O가 커질 수 있습니다. Sort 생략 이득과 Table Access 비용을 함께 비교합니다.

9. Bitmap Index의 Partition 제한

Partitioned Table의 Bitmap Index는 Local로 만들어야 합니다. Global Bitmap Index는 Partitioned Table에 허용되지 않습니다.

CREATE BITMAP INDEX sales_channel_bix
ON sales(channel_code)
LOCAL;

Bitmap Index는 분석계의 낮은 Cardinality 조건 결합에 유리할 수 있지만, 빈번한 Row DML과 높은 동시성이 있는 OLTP에는 부적합할 수 있습니다. 따라서 Bitmap의 Local 제한과 B-Tree의 Local·Global 선택을 별개로 판단합니다.

10. 대표 SQL별 설계 판단

최근 한 달 고객 주문

WHERE sale_date >= DATE '2025-07-01'
  AND sale_date <  DATE '2025-08-01'
  AND customer_id = :customer_id

Table Partition 하나 Pruning
→ Local Nonprefixed (customer_id, sale_date) 후보
→ 해당 한 조각에서 customer_id Range Scan

전체 기간 고객 단건 조회

WHERE customer_id = :customer_id

Local Index
→ 많은 Index Partition Probe 가능

Global Index
→ 전역 B-Tree 또는 Global Hash Partition 한 조각 탐색 가능

월 단위 대량 적재·삭제가 매우 중요

Local Index
→ Partition 단위 운영·장애 격리에 유리

Global Index
→ 전역 조회 이득과 UPDATE INDEXES·Cleanup 비용을 별도 측정

전체 Table에서 sale_id 전역 Unique

Global Unique Index
→ sale_id만으로 직접 전역 유일성 보장

Local Unique Index
→ Table Partition Key를 Index Key에 포함해야 함

11. 실제 실행과 운영 상태 검증

설계 판단은 DDL 정의만으로 끝내지 않습니다.

실행계획과 Runtime 통계

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    NULL,
    NULL,
    'ALLSTATS LAST +PARTITION +PREDICATE +ALIAS +NOTE'
  )
);

확인 순서는 다음과 같습니다.

1. Table Partition Pruning 범위
2. Local 또는 Global Index와 Index Partition Pruning 범위
3. Index Row Source의 Starts
4. A-Rows ÷ Starts
5. Buffers ÷ Starts
6. Table Access의 Random I/O와 Clustering Factor 영향
7. Sort·Merge 발생 여부

Local Index의 Starts가 많고 Buffers ÷ Starts가 작더라도 총 Buffers가 크면 반복 Probe가 병목일 수 있습니다. 반대로 Global Index가 한 번 탐색되더라도 넓은 ROWID 분산으로 Table Access Buffer가 크면 전체 비용이 더 나쁠 수 있습니다.

Dictionary 상태

SELECT index_name,
       index_type,
       partitioned,
       status,
       orphaned_entries
FROM   user_indexes
WHERE  table_name = 'SALES';

SELECT index_name,
       locality,
       alignment,
       partitioning_type,
       subpartitioning_type
FROM   user_part_indexes
WHERE  index_name LIKE 'SALES%';

SELECT index_name,
       partition_name,
       status,
       num_rows,
       leaf_blocks
FROM   user_ind_partitions
WHERE  index_name LIKE 'SALES%'
ORDER BY index_name, partition_position;

운영 판단에서는 실행시간뿐 아니라 DDL 소요시간, Global Index 가용성, Orphaned Entry 정리, Rebuild 시간, 통계와 Cursor 영향까지 포함합니다.

선택 기준 표

진단·적용 절차

1. SQL을 Partition Key 포함 조회와 미포함 조회로 분류합니다.
2. 각 유형의 실행 빈도·반환 Row·기간 범위를 측정합니다.
3. Local Prefixed·Local Nonprefixed·Global 후보를 각각 만듭니다.
4. Unique 요구가 Partition 내부인지 전체 Table인지 확인합니다.
5. Table·Index Partition Pruning과 Starts·Buffers를 비교합니다.
6. 여러 Partition 정렬·Top-N의 Sort 또는 Merge 비용을 확인합니다.
7. 대표 Partition DDL을 테스트해 Index 상태와 소요시간을 측정합니다.
8. ORPHANED_ENTRIES, Rebuild·Cleanup, 통계 갱신을 운영 절차에 포함합니다.
9. 조회 이득과 DML·DDL·가용성 비용을 합산해 선택합니다.
10. 데이터 증가와 Partition 수 변화 후 같은 항목을 재검증합니다.