SQL*Loader와 인덱스

SQL*Loader 적재 작업을 인덱스 관리 측면에서 살펴보자.

아래는 SQL*Loader의 도움말이다. skip_unusable_indexes, skip_index_maintenance 파라미터가 인덱스와 관련된 것을 확인할 수 있다.

-- 1
C:\>C:\>sqlldr

SQL*Loader: Release 19.0.0.0.0 - Production on 수 11월 27 13:10:18 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

사용법: C:\>sqlldr keyword=value [,키워드=값,...]

적절한 키워드:
...
skip_unusable_indexes -- 사용할 수 없는 인덱스 또는 인덱스 분할 영역 허용 안함/허용  (기본값 FALSE)
skip_index_maintenance -- 인덱스 유지 관리 안함, 영향을 받은 인덱스를 사용 불가로 표시  (기본값 FALSE)

skip_unusable_indexes 파라미터는 기본값이 false로 설명되어 있지만 동일한 명칭의 초기화 파라미터 설정값으로 동작한다.

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

VALUE
-----
TRUE

1개의 행이 선택되었습니다.

테스트를 위해 아래와 같이 테이블을 생성하자. t1_x1 인덱스는 NONUNIQUE 인덱스로 UNUSABLE 상태로 생성했다.

-- 3
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE INDEX t1_x1 ON t1 (c1) UNUSABLE;

아래와 같이 컨트롤 파일과 데이터 파일을 생성하자.

-- 4-1 : c1.ctl
LOAD DATA
APPEND INTO TABLE t1
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
    c1
  , c2
)

-- 4-2 : d1.dat
1,1

아래는 skip_unusable_indexes 파라미터의 테스트 결과다. Conventional Load인 경우 파라미터 설정값과 무관하게 데이터가 적재되고, Direct Load인 경우에는 파라미터를 true로 설정해야 데이터가 적재되는 것을 확인할 수 있다.

-- 5-1
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat skip_unusable_indexes=true

1 행이(가) 성공적으로 로드되었습니다.

-- 5-2
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat skip_unusable_indexes=false

1 행이(가) 성공적으로 로드되었습니다.

-- 5-3
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_unusable_indexes=true

1 행이(가) 성공적으로 로드되었습니다.

-- 5-4
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_unusable_indexes=false

SQL*Loader-951: 1회/로드 초기화 호출시 오류
ORA-26028: SCOTT.T1_X1 인덱스는 사용할 수 없는 상태에 있습니다

다음 테스트를 위해 테이블을 재생성하자. t1_u1 인덱스는 UNIQUE 인덱스로 UNUSABLE 상태로 생성했다.

-- 6
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE UNIQUE INDEX t1_u1 ON t1 (c1) UNUSABLE;

아래는 5번과 동일한 테스트를 수행한 결과다. 파라미터 설정값과 무관하게 데이터가 적재되지 않는다. UNIQUE 인덱스인 경우 skip_unusable_indexes 파라미터가 동작하지 않는 것을 알 수 있다.

-- 7-1
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat skip_unusable_indexes=true

0 행이(가) 성공적으로 로드되었습니다.

-- 7-2
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat skip_unusable_indexes=false

0 행이(가) 성공적으로 로드되었습니다.

-- 7-3
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_unusable_indexes=true

0 행이(가) 성공적으로 로드되었습니다.

-- 7-4
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_unusable_indexes=false

SQL*Loader-951: 1회/로드 초기화 호출시 오류
ORA-26028: SCOTT.T1_U1 인덱스는 사용할 수 없는 상태에 있습니다

아래는 7-1, 7-2, 7-3 테스트의 로그 파일 내용이다.

-- 8
레코드 1: 거부됨 - T1 테이블에 오류가 있습니다.
ORA-01502: 인덱스 'SCOTT.T1_U1'또는 인덱스 분할영역은 사용할 수 없은 상태입니다

아래는 skip_index_maintenance 파라미터의 테스트 결과다. Direct Load인 경우 파라미터를 true로 설정하면 데이터가 적재되는 것을 확인할 수 있다.

-- 9-1
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat skip_index_maintenance=true

0 행이(가) 성공적으로 로드되었습니다.

-- 9-2
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat skip_index_maintenance=false

0 행이(가) 성공적으로 로드되었습니다.

-- 9-3
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_index_maintenance=true

1 행이(가) 성공적으로 로드되었습니다.

-- 9-4
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_index_maintenance=false

0 행이(가) 성공적으로 로드되었습니다.

아래는 9-1, 9-2, 9-4 테스트의 로그 파일 내용이다.

-- 10
ORA-26026: 유일 SCOTT.T1_U1 인덱스는 처음과 같은 사용불가능한 상태입니다

SQL*Loader-2026: SQL Loader가 계속할 수 없으므로 로드가 중단되었습니다.

로드가 완료되더라도 인덱스는 UNUSABLE 상태다.

-- 11
SELECT status FROM user_indexes WHERE index_name = 'T1_U1';

STATUS
--------
UNUSABLE

1개의 행이 선택되었습니다.

다음 테스트를 위해 테이블을 삭제하고 파티션 테이블을 생성하자. t1_pk 인덱스는 제약 조건에 의해 자동 생성된다. PK 제약조건과 인덱스에 대한 내용은 이전 글을 참조하자.

-- 12
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1 NUMBER NOT NULL, c2 NUMBER)
PARTITION BY RANGE (c1) (
    PARTITION p1 VALUES LESS THAN (2)
  , PARTITION p2 VALUES LESS THAN (3)
  , PARTITION p3 VALUES LESS THAN (MAXVALUE));

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (c1);

CREATE INDEX t1_x1 ON t1 (c2) LOCAL;

아래와 같이 Direct Load로 skip_index_maintenance 파라미터를 true로 설정하고 데이터를 적재하자.

-- 13
C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_index_maintenance=true

1 행이(가) 성공적으로 로드되었습니다.

PK 제약 조건이 DISABLED NOT VALIDATED 상태로 변경되고, t1_pk 인덱스가 삭제된 것을 확인할 수 있다. 파티션 인덱스는 적재 대상 파티션만 UNUSABLE 상태로 변경된다.

-- 14-1
SELECT status, validated FROM user_constraints WHERE constraint_name = 'T1_PK';

STATUS   VALIDATED
-------- -------------
DISABLED NOT VALIDATED

1개의 행이 선택되었습니다.

-- 14-2
SELECT index_name, partition_name, status FROM user_ind_partitions WHERE index_name IN ('T1_PK', 'T1_X1');

INDEX_NAME PARTITION_NAME STATUS
---------- -------------- --------
T1_X1      P1             UNUSABLE
T1_X1      P2             USABLE
T1_X1      P3             USABLE

3 행이 선택되었습니다.

인덱스 관리 측면에서 아래의 과정으로 SQL*Loader 적재 작업을 수행하는 편이 바람직하다.

-- 15
ALTER TABLE t1 MODIFY CONSTRAINT t1_pk DISABLE KEEP INDEX;
ALTER INDEX t1_pk UNUSABLE;
ALTER INDEX t1_x1 UNUSABLE;

C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_index_maintenance=true

ALTER INDEX t1_pk REBUILD;
ALTER INDEX t1_x1 REBUILD;
ALTER TABLE t1 MODIFY CONSTRAINT t1_pk ENABLE;

파티션 테이블인 경우에는 아래와 같이 수행하면 된다.

-- 16
ALTER TABLE t1 MODIFY CONSTRAINT t1_pk DISABLE KEEP INDEX;
ALTER INDEX t1_pk MODIFY PARTITION p1 UNUSABLE;
ALTER INDEX t1_x1 MODIFY PARTITION p1 UNUSABLE;

C:\>sqlldr userid=tuna/tuna@ora12cr2 control=c1.ctl data=d1.dat direct=true skip_index_maintenance=true

ALTER INDEX t1_pk REBUILD PARTITION p1;
ALTER INDEX t1_x1 REBUILD PARTITION p1;
ALTER TABLE t1 MODIFY CONSTRAINT t1_pk ENABLE;