파티션 유지관리와 대량 데이터 처리: EXCHANGE·DROP·TRUNCATE

핵심 요약

파티션 테이블에서는 많은 행을 한 건씩 INSERT, UPDATE, DELETE하는 대신 파티션 전체를 하나의 관리 단위로 교체하거나 비우거나 제거할 수 있습니다. 이 방식이 유효하려면 업무 대상과 파티션 경계가 정확히 일치해야 합니다.

행 단위 DML
→ 대상 Row 탐색
→ Row별 Undo·Redo와 Index Entry 변경
→ Row Lock·Trigger·Constraint 처리

파티션 유지관리 DDL
→ 대상 Partition·Segment 식별
→ Segment 소속 또는 Partition Metadata 변경
→ Row별 처리량을 크게 줄일 수 있음

세 작업의 의미는 다음과 같습니다.

EXCHANGE PARTITION
→ Partition과 별도 Table의 데이터 Segment를 양방향 교환

TRUNCATE PARTITION
→ Partition 정의는 유지하고 모든 Row를 제거

DROP PARTITION
→ Partition의 모든 Row와 Partition 정의를 함께 제거

단, EXCHANGE가 항상 순수한 Metadata 작업인 것은 아닙니다. Validation, Enabled Primary Key·Unique Constraint, Local·Global Index 유지, 통계 처리에 따라 추가 작업이 발생합니다. DROP·TRUNCATE ... UPDATE INDEXES는 지원되는 Heap Table에서 비동기 Global Index 유지로 빠르게 끝날 수 있지만, Global Index에는 ORPHANED_ENTRIES가 남아 후속 정리가 필요할 수 있습니다.

학습 목표

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

1. 행 단위 DML과 파티션 유지관리 DDL의 비용 구조를 구분한다.
2. EXCHANGE, TRUNCATE, DROP의 데이터와 Partition 정의 처리 차이를 설명한다.
3. Staging Table을 이용한 Partition Exchange Load 절차를 설계한다.
4. WITH VALIDATION, WITHOUT VALIDATION, ORA_PARTITION_VALIDATION의 역할을 구분한다.
5. INCLUDING INDEXES가 Local Index에 적용되는 범위를 설명한다.
6. UPDATE INDEXES의 EXCHANGE와 DROP·TRUNCATE 차이를 설명한다.
7. 비동기 Global Index 유지와 ORPHANED_ENTRIES 정리 절차를 판단한다.
8. Partition 일부 Row만 변경할 때 일반 DML 또는 재생성 후 Exchange를 선택한다.
9. Direct-Path Insert와 NOLOGGING의 성능 이득과 복구 위험을 함께 판단한다.
10. 교체 전후 Row 수·경계·Index·통계·업무 결과를 검증한다.

1. 대량 행 단위 DML이 비싸지는 이유

월 단위 Range Partition Table에서 한 달치 5천만 Row를 삭제한다고 가정합니다.

DELETE FROM sales
WHERE sale_date >= DATE '2025-01-01'
  AND sale_date <  DATE '2025-02-01';

DELETE는 대상 Row를 찾는 것 외에도 Row와 관련 Index Entry마다 작업을 수행합니다.

Table Row 삭제
→ Data Block 변경
→ Undo·Redo 생성
→ 모든 관련 Index Entry 삭제
→ Constraint·Trigger·Row Lock 처리

반면 2025년 1월 Row가 정확히 P202501 하나에만 있고 그 Partition의 모든 Row를 제거해도 된다면 다음 DDL을 검토할 수 있습니다.

ALTER TABLE sales
TRUNCATE PARTITION p202501
UPDATE INDEXES;

이 선택은 다음 조건이 모두 성립할 때 안전합니다.

삭제 대상 = Partition 전체
보존할 Row = Partition 안에 없음
Referential Constraint·Index·복구 정책 = DDL 수행 가능

Partition 안에서 일부 Row만 보존해야 한다면 TRUNCATE나 DROP을 바로 사용할 수 없습니다.

2. DELETE·TRUNCATE·DROP·EXCHANGE 비교

TRUNCATE와 DROP은 행별 DELETE Trigger를 실행하는 방식이 아닙니다. 운영에서는 짧은 DDL 시간만 보지 말고 DDL Lock, Transaction 경계, Global Index 후속 작업, Standby와 Backup까지 포함한 전체 절차를 평가해야 합니다.

3. Partition Exchange의 동작 원리

EXCHANGE PARTITION은 Partition과 별도 Table이 가리키는 데이터 Segment를 서로 교환합니다.

교체 전
SALES.P202507 → Segment A: 기존 운영 데이터
SALES_STG     → Segment B: 새로 준비한 데이터

교체 후
SALES.P202507 → Segment B: 새 운영 데이터
SALES_STG     → Segment A: 교체되어 나온 기존 데이터

따라서 기존 Partition 데이터가 즉시 삭제되는 것이 아닙니다. 교체되어 나온 데이터는 Staging Table에 남으므로 검증, 아카이빙, 원복에 사용할 수 있습니다. Exchange 과정에서 Object의 Logging 속성도 보존됩니다.

대표 활용

Partition Exchange Load
→ 새 기간 데이터를 Staging에 적재·검증한 뒤 운영 Partition과 교환

대량 UPDATE 대체
→ 변경 후 최종 결과를 Staging에서 다시 생성한 뒤 교환

아카이빙
→ 오래된 운영 Partition을 빈 Table과 교환해 별도 보관

EXCHANGE는 Row를 한 건씩 복사하는 작업과 비용 구조가 다르지만, UPDATE INDEXES, Validation, Constraint 처리로 인해 실행시간이 늘 수 있습니다.

4. 교체용 Staging Table 만들기

Exchange는 Column 순서와 Data Type뿐 아니라 Invisible Column, Virtual Expression Column 등 Column 속성의 호환성이 중요합니다. 지원되는 환경에서는 다음 문법을 우선 검토합니다.

CREATE TABLE sales_stg
FOR EXCHANGE WITH TABLE sales;

FOR EXCHANGE WITH TABLE은 대상 Table의 Column 순서와 속성에 맞는 교체용 Table을 생성합니다. 단, Index는 자동 생성하지 않습니다. INCLUDING INDEXES를 사용할 계획이라면 대응되는 Staging Index를 별도로 준비해야 합니다.

Staging 적재

INSERT /*+ APPEND */ INTO sales_stg
SELECT order_id,
       sale_date,
       customer_id,
       amount,
       status
FROM   sales_source
WHERE  sale_date >= DATE '2025-07-01'
  AND  sale_date <  DATE '2025-08-01';

교체 전 업무 검증

SELECT COUNT(*)        AS row_count,
       MIN(sale_date)  AS min_date,
       MAX(sale_date)  AS max_date,
       SUM(amount)     AS total_amount
FROM   sales_stg;

최솟값과 최댓값만으로 중간의 잘못된 Row를 모두 잡을 수 있는 것은 아닙니다. 필요하면 Partition Key Predicate로 범위 밖 Row를 직접 검색하거나 ORA_PARTITION_VALIDATION을 사용합니다.

5. EXCHANGE PARTITION과 Validation

기본 예시는 다음과 같습니다.

ALTER TABLE sales
EXCHANGE PARTITION p202507
WITH TABLE sales_stg
INCLUDING INDEXES
WITHOUT VALIDATION
UPDATE INDEXES;

실제 Clause 지원과 순서는 Object 유형과 Oracle 버전에 맞게 확인합니다.

WITH VALIDATION

Staging의 각 Row가 대상 Partition에 올바르게 매핑되는지 검사합니다. 대량 데이터에서는 시간이 필요하지만 잘못된 Partition 배치를 방지합니다.

WITHOUT VALIDATION

일반적으로 Partition 경계 검사를 생략하므로 Data Dictionary 갱신 중심의 빠른 작업이 될 수 있습니다. 그러나 사용자가 다음을 보장해야 합니다.

Staging의 모든 Row가 대상 Partition 경계에 속함
다른 기간·목록 값이 섞이지 않음
업무 Constraint와 중복 규칙을 충족함

Enabled Primary Key 또는 Unique Constraint가 관련되어 있으면 무결성을 유지하기 위해 WITHOUT VALIDATION을 지정해도 WITH VALIDATION처럼 검사가 수행될 수 있습니다. 따라서 문구만 보고 순수 Metadata 작업이라고 단정하지 말고 실제 Constraint 상태와 수행시간을 확인합니다.

잘못 배치된 Row 찾기

Oracle의 ORA_PARTITION_VALIDATION SQL 함수를 이용하면 Row가 지정 Partition에 올바르게 매핑되는지 확인하는 진단을 구성할 수 있습니다. 이 함수 또는 명시적인 경계 Predicate를 통해 범위 밖 Row를 교체 전에 제거해야 합니다.

Interval Partition 주의

Interval Partition을 Exchange하려면 대상 Partition이 먼저 Data Dictionary에 실제 생성되어 있어야 합니다. 아직 Materialize되지 않은 미래 Interval이라면 해당 Partition을 생성·식별한 뒤 Exchange해야 하며, System-generated Partition은 FOR (Partition Key 값) 문법으로 지정할 수 있습니다.

6. Local Index와 INCLUDING INDEXES

INCLUDING INDEXES는 대상 Partition의 Local Index Partition과 Staging Table의 호환되는 Nonpartitioned Index를 함께 교환합니다.

Table Partition P202507
↔ SALES_STG Table Segment

Local Index Partition P202507
↔ SALES_STG의 대응 Index Segment

주의할 점은 다음과 같습니다.

• CREATE TABLE ... FOR EXCHANGE WITH TABLE은 Index를 만들지 않는다.
• 대응 Index의 Column·순서·속성이 Exchange 요건과 호환되어야 한다.
• INCLUDING INDEXES는 Global Index를 Staging Index와 교환한다는 뜻이 아니다.
• Local Index를 포함하지 않거나 구조가 맞지 않으면 대상 Index Partition 상태를 별도로 확인해야 한다.

7. Global Index 유지: EXCHANGE와 DROP·TRUNCATE의 차이

Global Index는 여러 Table Partition의 ROWID를 하나의 Index 구조에서 가리킬 수 있으므로 Partition Maintenance의 영향을 받습니다.

EXCHANGE

Exchange에서 UPDATE INDEXES를 지정하지 않으면 대상 Table의 Global Index 또는 Global Index Partition이 UNUSABLE이 될 수 있습니다. UPDATE INDEXES를 사용하면 Exchange 시점에 Global Index를 유지하지만, Index Entry 변경에 Undo·Redo와 시간이 필요할 수 있어 Exchange가 더 이상 매우 빠른 작업이 아닐 수 있습니다.

빠른 Exchange 후 Global Index Rebuild
vs
Exchange 중 UPDATE INDEXES로 가용성 유지

어느 쪽이 유리한지는 Partition 크기, Global Index 크기, 허용 중단시간, Redo량으로 판단합니다.

DROP·TRUNCATE

지원되는 Heap Table에서 다음 DDL은 UPDATE INDEXES를 지정하면 비동기 Global Index 유지가 기본적으로 적용될 수 있습니다.

ALTER TABLE sales
DROP PARTITION p202401
UPDATE INDEXES;

ALTER TABLE sales
TRUNCATE PARTITION p202507
UPDATE INDEXES;

이 경우 Global Index를 UNUSABLE로 만들지 않고 Partition DDL을 Metadata 중심으로 빠르게 처리할 수 있습니다. 그러나 Global Index 내부에는 제거된 Row를 가리키던 Stale Entry가 남아 ORPHANED_ENTRIES='YES'로 표시될 수 있습니다.

비동기 유지의 제한과 정리

비동기 Global Index 유지는 대표적으로 다음 조건에서 제한됩니다.

• Heap Table에서만 지원
• Object Type Column Table 미지원
• Domain Index가 있는 Table 미지원
• SYS 사용자 작업 미지원

후속 정리는 자동 Scheduler Job 또는 다음 방법으로 수행할 수 있습니다.

SYS.PMO_DEFERRED_GIDX_MAINT_JOB
DBMS_PART.CLEANUP_GIDX
ALTER INDEX ... COALESCE CLEANUP
ALTER INDEX ... REBUILD [PARTITION]

상태를 확인합니다.

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

SELECT index_name,
       partition_name,
       status,
       orphaned_entries
FROM   user_ind_partitions
WHERE  index_name LIKE 'SALES%';

STATUS='VALID'만 확인하고 종료하지 말고 ORPHANED_ENTRIES와 정리 일정까지 확인합니다.

8. DROP PARTITION과 TRUNCATE PARTITION

Partition 정의까지 제거

ALTER TABLE sales
DROP PARTITION p202401
UPDATE INDEXES;

적합한 경우는 다음과 같습니다.

해당 기간 데이터를 영구 제거
Partition 이름·경계도 더 이상 불필요
보존·감사·복구 정책 충족

Local Index가 있으면 대응 Local Index Partition도 함께 제거됩니다. Table에 Partition이 하나만 남아 있다면 마지막 Partition만 Drop할 수 없으므로 Table 자체의 처리 방식을 검토해야 합니다.

Partition 정의를 유지하고 비우기

ALTER TABLE sales
TRUNCATE PARTITION p202507
UPDATE INDEXES;

P202507의 경계와 정의는 유지되고 모든 Row가 제거되며 대응 Local Index Partition도 비워집니다. 같은 기간 적재를 다시 수행하거나 고정된 Partition 틀을 유지해야 할 때 적합합니다.

Referential Integrity 주의

다른 Table의 Row가 제거 대상 Row를 참조하고 있으면 TRUNCATE PARTITION이 제한될 수 있습니다. Constraint를 임의로 비활성화하기 전에 참조 Row 존재 여부와 재활성화 가능성을 확인해야 합니다. 소량이면 DELETE로 Constraint와 Trigger를 정상 적용하는 편이 안전할 수 있습니다.

9. Partition 일부 Row만 변경하는 경우

다음 요구는 TRUNCATE PARTITION으로 처리할 수 없습니다.

DELETE FROM sales
WHERE sale_date >= DATE '2025-07-01'
  AND sale_date <  DATE '2025-08-01'
  AND status = 'CANCELLED';

정상 주문을 보존해야 하기 때문입니다.

삭제·수정 비율이 작음
→ Index와 Access Path를 이용한 일반 DML

삭제·수정 비율이 매우 큼
→ 남길 최종 Row를 Staging에 생성
→ Partition과 Exchange
→ 교체되어 나온 기존 Segment를 보관 또는 제거

같은 조건이 반복됨
→ 업무 삭제 단위에 맞춘 Subpartition·Partition Key 재설계 검토

Exchange 방식은 전체 Row를 다시 만드는 비용, Staging 공간, Global Index와 동시성까지 포함해 일반 DML과 비교해야 합니다.

10. Direct-Path Insert와 NOLOGGING

Staging 대량 적재에는 Direct-Path Insert를 검토할 수 있습니다.

INSERT /*+ APPEND */ INTO sales_stg
SELECT *
FROM   sales_source
WHERE  sale_date >= DATE '2025-07-01'
  AND  sale_date <  DATE '2025-08-01';

APPEND는 INSERT ... SELECT에서 Direct-Path Insert를 지시합니다. Direct Path는 기존 Block의 빈 공간을 재사용하기보다 Segment 끝에 새 Block을 추가하고 Data File에 직접 기록하므로 빠를 수 있습니다. 그러나 다음을 확인해야 합니다.

• Target Table의 Trigger·Referential Constraint 등 Direct Path 제한
• 같은 Transaction에서 Target을 다시 읽거나 변경할 수 있는지
• Segment 공간과 High Water Mark 증가
• Index 유지·생성 비용
• Parallel DML 설정과 Lock 영향
• 적재 후 통계정보

제한을 위반하면 Oracle이 메시지 없이 Conventional Insert로 전환할 수 있는 경우도 있으므로 Hint 존재만으로 Direct Path가 수행되었다고 단정하지 않습니다.

NOLOGGING의 정확한 의미

NOLOGGING은 모든 Redo가 사라진다는 뜻이 아닙니다.

Conventional Insert
→ Object가 NOLOGGING이어도 Data 변경 Redo 생성

Direct-Path Insert + NOLOGGING
→ 조건을 충족하면 Data 변경 Redo를 줄일 수 있음
→ Metadata 복구용 Redo는 생성

FORCE LOGGING
→ NOLOGGING Object의 Direct-Path 작업도 Data Redo 생성 가능

복구가 필요한 데이터에 NOLOGGING을 사용했다면 작업 후 Backup과 Standby 일관성 절차가 필요할 수 있습니다. 속도만 보고 적용해서는 안 됩니다.

11. 통계정보와 실행계획 안정성

Exchange 직후 운영 Partition의 Row 수와 분포가 크게 달라질 수 있습니다. Partition DDL이 빨리 끝나도 Optimizer Statistics가 부정확하면 후속 SQL의 Plan이 나빠질 수 있습니다.

확인 항목은 다음과 같습니다.

Partition NUM_ROWS·BLOCKS
Partition Column NDV·Histogram
Local Index Partition Statistics
Global Statistics와 Incremental Statistics
Cursor Invalidations와 대표 SQL Plan

Partition Exchange에서 Incremental Statistics Synopsis까지 유지하려면 Nonpartitioned Staging Table에 통계를 수집할 때 Table Preference의 INCREMENTAL=TRUE, INCREMENTAL_LEVEL=TABLE 조건을 맞추는 정책을 검토합니다. 조건이 맞지 않으면 교체 후 Partition Statistics 또는 Synopsis를 다시 수집해야 할 수 있습니다.

12. 운영 적용 절차

[1. 설계]
1) 작업 대상이 Partition 전체와 정확히 일치하는지 확인
2) DELETE·TRUNCATE·DROP·EXCHANGE 중 의미가 맞는 작업 선택
3) DDL Lock·Global Index·Constraint·Recovery 정책 결정

[2. Staging 준비]
4) FOR EXCHANGE WITH TABLE로 구조 생성
5) 필요한 대응 Index 별도 생성
6) APPEND·Parallel·Logging 정책에 맞춰 데이터 적재
7) Row 수·경계·중복·합계·Hash Total 검증

[3. 교체·제거]
8) WITH/WITHOUT VALIDATION과 UPDATE INDEXES 결정
9) EXCHANGE·DROP·TRUNCATE 실행
10) 교체되어 나온 Segment를 원복 가능 상태로 유지

[4. 사후 검증]
11) 업무 Row 수·합계·NULL·중복 재검증
12) Local·Global Index STATUS와 ORPHANED_ENTRIES 확인
13) Partition·Index·Incremental Statistics 확인
14) 대표 SQL Plan과 실제 응답시간 확인
15) Global Index Cleanup·Backup·Staging 정리 수행

판단 기준 정리