대량 적재 운영 전략: Index·Constraint·UNUSABLE·재구축

핵심 요약

대량 적재의 목표는 단순히 INSERT 구간을 가장 짧게 만드는 것이 아닙니다. 서비스 중단 시간, 조회 가능성, 무결성 공백, Index 재구축 시간, 추가 공간, Redo·Standby·Backup, 통계정보, 실패 후 재시작 시간을 합친 전체 운영 시간을 최소화해야 합니다.

대표 전략은 두 가지입니다.

유지 전략
= Table 적재
+ 각 Row의 Index 유지
+ Constraint 즉시 검사

재구축 전략
= Object 상태 변경
+ Table 적재
+ 데이터 검증
+ Index 재구축
+ Constraint 재활성화·검증
+ Statistics·Backup·서비스 전환

작은 증분 적재와 온라인 업무에서는 유지 전략이 단순하고 안전합니다. 매우 큰 초기 적재나 격리 가능한 Batch에서는 일부 Nonunique Index를 UNUSABLE로 만들거나 제약을 계획적으로 비활성화한 뒤 한 번에 재구축하는 방식이 유리할 수 있습니다. 그러나 Unique·Primary Key, Partitioned Index, NOLOGGING, DDL의 암묵적 Commit은 별도로 검토해야 합니다.

학습 목표

1. 유지 적재와 사후 재구축 적재의 전체 비용을 비교한다.
2. UNUSABLE Index의 Optimizer·DML·Segment 동작을 설명한다.
3. SKIP_UNUSABLE_INDEXES와 Unique Index의 예외를 구분한다.
4. ENABLE·DISABLE과 VALIDATE·NOVALIDATE의 두 축을 해석한다.
5. Primary Key·Unique Constraint와 지원 Index의 생명주기를 설명한다.
6. Direct-Path Insert의 Index 유지 시점과 공간 특성을 이해한다.
7. Parallel·Online·NOLOGGING Rebuild의 Trade-off를 판단한다.
8. Staging과 EXCHANGE PARTITION의 전환 절차를 설계한다.
9. DDL의 암묵적 Commit을 고려해 실패·재시작 절차를 만든다.
10. 적재 후 데이터·Index·Constraint·Statistics·Backup을 검증한다.

1. 유지 전략과 재구축 전략의 손익분기점

1.1 유지 전략이 유리한 경우

• 적재량이 기존 Table에 비해 작음
• 적재 중에도 조회·DML을 계속 제공해야 함
• 무결성 공백을 허용할 수 없음
• Index 재구축용 추가 공간과 I/O를 확보하기 어려움
• 실패 시 하나의 DML Transaction으로 Rollback해야 함

1.2 재구축 전략이 유리할 수 있는 경우

• 초기 적재 또는 매우 큰 Batch
• 대상 Object를 작업 Window 동안 격리할 수 있음
• 재구축용 CPU·I/O·TEMP·추가 Segment 공간을 확보할 수 있음
• 재적재·재구축·서비스 전환 절차가 자동화되어 있음
• 작업 후 검증·Backup Window를 확보할 수 있음

1.3 고정 비율을 사용하지 않는다

적재량이 Table의 10%를 넘으면 Index를 내린다와 같은 고정 규칙은 안전하지 않습니다. 다음 값을 Test Data와 실제 Storage에서 측정합니다.

유지 전략 총시간
= 적재 시간 + Index 유지 + Constraint·Trigger 검사 + Commit

재구축 전략 총시간
= 상태 변경 + 적재 + 데이터 검증
+ Index 생성·재구축 + Constraint Validate
+ Statistics + Backup·Standby 확인 + 서비스 전환

같은 Row 수라도 Index 개수·폭, Clustering, Unique·Bitmap·Global 여부, 기존 Segment 크기, Rebuild DOP, I/O 대역폭에 따라 결과가 달라집니다.

2. UNUSABLE Index의 정확한 의미

2.1 Optimizer와 DML

ALTER INDEX sales_customer_ix UNUSABLE;

일반적으로 UNUSABLE Index는 다음 상태가 됩니다.

• Optimizer의 정상 Access Path에서 제외됨
• DML에서 Index Entry를 유지하지 않음
• 다시 사용하려면 Rebuild 또는 Drop·Create 필요
• Partitioned Index의 일부 Partition만 UNUSABLE이면 나머지 Partition은 계속 유효할 수 있음

기존 Index를 일반 방식으로 UNUSABLE로 표시하면 할당된 Index Segment 공간이 즉시 해제됩니다. 반면 ALTER INDEX ... UNUSABLE ONLINE은 DML을 허용하기 위해 Segment를 Drop하지 않습니다. Partition Maintenance로 Global Index가 UNUSABLE이 된 경우에도 Segment가 남을 수 있으므로, Dictionary 상태와 Segment를 함께 확인합니다.

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

Local·Global Partitioned Index는 다음 View도 확인합니다.

SELECT index_name, partition_name, status
FROM   user_ind_partitions
WHERE  index_name = 'SALES_CUSTOMER_IX';

2.2 SKIP_UNUSABLE_INDEXES

SKIP_UNUSABLE_INDEXES=TRUE는 기본값입니다. 이때 Nonunique Unusable Index가 있는 Table의 Select·Insert·Update·Delete는 일반적으로 진행할 수 있고, 해당 Index는 유지되지 않습니다.

그러나 다음 예외가 중요합니다.

• Unique Constraint를 강제하는 Unusable Index는 오류 보고를 건너뛰지 않음
• Hint가 Unusable Index 사용을 강제하면 ORA-01502가 발생할 수 있음
• FALSE이면 Unusable Index가 있는 Table에 대한 DML이 실패할 수 있음

따라서 SKIP_UNUSABLE_INDEXES는 Unique 무결성을 우회하는 수단이 아닙니다.

SELECT value
FROM   v$parameter
WHERE  name = 'skip_unusable_indexes';

2.3 일부 Index Partition만 UNUSABLE인 경우

Partitioned Index에서 한 Partition만 UNUSABLE이면 다른 Partition은 계속 사용할 수 있습니다. Optimizer는 접근 대상 Partition에 따라 유효한 Index Partition을 사용하고, Unusable Partition은 다른 경로로 읽는 Table Expansion을 선택할 수도 있습니다. 따라서 전체 Index의 STATUS만 보지 말고 Partition 상태와 실제 Predicate 범위를 함께 확인합니다.

3. Constraint 상태의 두 축

Constraint 상태는 다음 두 질문으로 해석합니다.

ENABLE / DISABLE   : 새 DML을 검사하는가?
VALIDATE / NOVALIDATE : 기존 데이터 전체가 유효하다고 보장하는가?

3.1 ENABLE NOVALIDATE

ALTER TABLE sales
ENABLE NOVALIDATE CONSTRAINT sales_customer_fk;

이 상태에서는 이후 새 DML은 검사하지만 기존 Row 전체가 유효하다는 보장은 없습니다. 이후 ENABLE VALIDATE로 전환할 때 단일 Constraint 검증은 Parallel로 수행할 수 있고 읽기·쓰기·다른 DDL을 막지 않는 방식으로 진행할 수 있습니다. 다만 실제 작업시간과 자원 사용량은 별도로 측정합니다.

3.2 DISABLE VALIDATE

DISABLE VALIDATE는 단순히 DML이 조금 제한될 수 있다는 상태가 아닙니다. Constraint는 Disabled이지만 기존 데이터가 유효하다는 상태를 유지하며, Unique·Primary Key의 지원 Index가 Drop될 수 있습니다. SQL*Loader나 Partition Exchange 같은 Data Warehouse 적재에 유용하지만, 일반 INSERT·UPDATE·DELETE는 허용되지 않습니다.

3.3 Primary Key·Unique Constraint와 지원 Index

Unique 또는 Primary Key Constraint를 Enable할 때 적합한 Index가 없으면 Database가 Unique Index를 생성할 수 있습니다. 이 Constraint를 Disable하면 해당 Unique Index가 Drop될 수 있습니다.

ALTER TABLE sales
DISABLE CONSTRAINT sales_pk KEEP INDEX;

KEEP INDEX는 재활성화 시 Index 재생성 비용을 줄일 수 있습니다. 그러나 보존된 Index가 Unique Index이면 Constraint를 Disable해도 Index 자체가 중복 Key를 허용하지 않습니다. 반복적인 Enable·Disable이 필요하면서 Disable 기간에는 중복 허용이 필요한 설계라면, 미리 Nonunique Index를 만들고 이를 Constraint 지원 Index로 사용하는 방식을 검토합니다. Nonunique Index는 Constraint를 Disable해도 자동 Drop되지 않습니다.

또한 Disabled Primary Key·Unique Constraint를 참조하는 Foreign Key는 Enable할 수 없습니다. Parent와 Child Constraint의 전환 순서를 설계해야 합니다.

4. Direct-Path Insert와 Index 유지

INSERT /*+ APPEND */ INTO sales
SELECT * FROM sales_stage;

Direct-Path Insert는 기존 빈 공간을 일반적인 방식으로 탐색하기보다 Segment의 High-Water Mark 위에 데이터를 추가합니다. Parallel Direct-Path Insert는 Nonpartitioned Table에서 DOP별 Temporary Segment를 만들 수 있으므로 Conventional Insert보다 추가 공간이 더 필요할 수 있습니다.

Index가 있는 Table에 Direct-Path Insert를 수행하면 Index 유지가 사라지는 것이 아닙니다. Oracle은 적재가 끝날 때 Index Maintenance를 수행합니다.

Table Data 적재
→ Direct-Path 구간 완료
→ Index Entry 일괄 유지

대형 적재에서는 Index를 미리 UNUSABLE로 만들고 적재 후 Rebuild하여 이 비용을 피할 수 있습니다. 그러나 Unique Constraint 지원 Index, 온라인 조회, 재구축 공간과 실패 복구를 먼저 검증해야 합니다.

5. Index 재구축 전략

5.1 기본 Rebuild

ALTER INDEX sales_customer_ix REBUILD;

성공적으로 Rebuild하면 UNUSABLE Index가 USABLE로 바뀝니다. 전체 Partitioned Index는 한 번에 Rebuild할 수 없으므로 Partition 또는 Subpartition별로 수행합니다.

ALTER INDEX sales_customer_ix
REBUILD PARTITION p202607;

5.2 Parallel Rebuild와 속성 복원

ALTER INDEX sales_customer_ix
REBUILD PARALLEL 8;

PARALLEL 8은 이번 Rebuild만 병렬화하는 것이 아니라 Index 자체의 기본 병렬 속성도 변경합니다. 작업 후 일반 조회·DML에 원하지 않는 병렬도가 남지 않도록 정책에 따라 복원합니다.

ALTER INDEX sales_customer_ix NOPARALLEL;

높은 DOP가 항상 빠른 것은 아닙니다. CPU·Storage·TEMP·동시 Batch와 PX Server 확보량을 측정합니다.

5.3 Online Rebuild

ALTER INDEX sales_customer_ix REBUILD ONLINE;

Online Rebuild 중에는 Base Table DML을 허용할 수 있지만 DDL은 제한되고, Online Build 중 Parallel DML은 지원되지 않습니다. 동시 DML이 많을수록 작업 시간이 증가할 수 있으므로 ONLINE은 비용이 없는 옵션이 아닙니다.

5.4 NOLOGGING과 복구

ALTER INDEX sales_customer_ix
REBUILD PARALLEL 8 NOLOGGING;

NOLOGGING은 Redo를 0으로 만드는 옵션이 아닙니다. Dictionary 변경과 Extent 무효화 정보 등 최소 Redo는 남습니다. Database 또는 Tablespace가 FORCE LOGGING이면 NOLOGGING은 무시됩니다.

NOLOGGING 작업 전에 생성한 Backup만으로 Media Recovery를 수행하면 해당 Object Block을 재생성하지 못할 수 있습니다. 필요한 Object라면 작업 후 Backup을 수행하고 Data Guard의 Logging Mode와 Standby 적용 상태를 확인합니다.

6. DDL은 Rollback 경계가 아니다

ALTER INDEX, ALTER TABLE, CREATE INDEX 같은 DDL은 실행 직전에 현재 Transaction을 암묵적으로 Commit하고, DDL 자체도 성공하면 Commit됩니다. 따라서 다음 절차를 하나의 Transaction으로 Rollback할 수 있다고 가정하면 안 됩니다.

DML 적재
→ ALTER INDEX REBUILD
→ 검증 실패
→ 전체 ROLLBACK  (불가능한 가정)

DDL 전후의 상태는 이미 Commit될 수 있습니다. 적재 Batch의 Commit 경계, DDL 재시작 Script, 완료 Flag, 실패 시 Object 상태별 복구 절차를 별도로 설계합니다.

7. Staging과 Partition Exchange

운영 Table을 직접 장시간 변경하기보다 Staging에서 적재·검증·Index 생성을 완료한 뒤 짧게 전환할 수 있습니다.

1. Staging 구조 준비
2. Source 대량 적재
3. Row 수·합계·Key 중복·범위 검증
4. Staging Index 생성
5. Constraint 상태 확인
6. Statistics 확인
7. Partition Exchange
8. 서비스 SQL·Backup·Standby 검증

ALTER TABLE sales
EXCHANGE PARTITION p202607
WITH TABLE sales_stage
INCLUDING INDEXES
WITH VALIDATION;

Exchange는 Source 데이터를 Target에 복사하는 단방향 작업이 아닙니다. Partition과 Staging Table의 Segment를 서로 교환하므로 기존 Partition Data는 Staging 쪽으로 이동합니다.

INCLUDING INDEXES는 호환되는 Local Index Partition과 Staging Index를 함께 교환합니다. Global Index의 유지비용은 별도로 판단합니다.

WITHOUT VALIDATION은 일반적으로 Data Dictionary 변경 중심의 빠른 작업이지만, 관련 Table에 Enabled Primary Key 또는 Unique Constraint가 있으면 무결성 유지를 위해 WITH VALIDATION처럼 검증될 수 있습니다. 빠른 전환을 기대한다면 Test Schema에서 실제 조건을 재현해야 합니다.

8. Statistics와 서비스 전환

Direct-Path INSERT INTO ... SELECT와 CTAS에서는 Online Statistics Gathering이 자동으로 수행될 수 있습니다. 그러나 다음 이유로 무조건 최신 통계라고 가정하지 않습니다.

• 모든 적재 형태가 자동 수집 대상은 아님
• Histogram·Partition·Index 통계 요구가 별도일 수 있음
• 적재 후 추가 DML 또는 Exchange가 있었을 수 있음

SELECT num_rows, last_analyzed
FROM   user_tab_statistics
WHERE  table_name = 'SALES';

필요하면 명시적으로 수집합니다.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => USER,
    tabname    => 'SALES',
    cascade    => TRUE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

9. 운영 절차

9.1 작업 전

1. 적재량·서비스 Window·RTO·RPO를 확정합니다.
2. Index별 Unique·Nonunique, Local·Global, Partition 상태를 목록화합니다.
3. Constraint의 STATUS, VALIDATED, 지원 Index를 확인합니다.
4. Trigger·Materialized View Log·Standby·Backup 정책을 확인합니다.
5. 유지 방식과 재구축 방식의 전체 시간을 같은 Data Set으로 Test합니다.
6. DDL 암묵적 Commit을 반영한 재시작 Point를 정의합니다.

9.2 작업 중

1. Rows/sec, Redo/Row, Undo, Wait Event를 수집합니다.
2. Direct-Path 여부와 실제 DOP를 확인합니다.
3. 공간·TEMP·PX Server·Standby Lag를 감시합니다.
4. 실패 시 완료된 DML Batch와 완료된 DDL을 각각 기록합니다.

9.3 작업 후

SELECT index_name, status, visibility, degree, logging
FROM   user_indexes
WHERE  table_name = 'SALES';

SELECT constraint_name, constraint_type, status, validated
FROM   user_constraints
WHERE  table_name = 'SALES';

SELECT COUNT(*) AS row_count,
       COUNT(DISTINCT sale_id) AS distinct_key_count,
       MIN(sale_date), MAX(sale_date), SUM(amount)
FROM   sales;

다음을 모두 통과해야 서비스 전환을 완료합니다.

• Data Count·합계·중복·범위
• Index와 Index Partition STATUS
• Constraint STATUS·VALIDATED
• Statistics와 대표 SQL Plan
• Index DOP·Logging 속성 원복
• Backup 완료와 Standby 정합성

10. 혼동하기 쉬운 판단