SQL Loader의 direct path load와 Index unusable

SQL loader라는 기능이 있습니다.

데이터를 이관하는 방법 중에 하나인데요, 이 방법은 일반 INSERT와 같은 방식인 conventional path load와 direct path load방식 두가지 방법으로 이용할 수 있습니다.

direct path load 방식으로 이용할 때, index가 unusable 상태로 바뀌는데 이 현상에 대해서 테스트를 통해 정확히 정리해보도록 하겠습니다.

 

두 방식의 특징을 간단하게 정리하면 아래와 같습니다.

아래 특징 외에도 더 많은 특징들이 있습니다. 필요하신 분들은 출처의 URL을 참고해주시길 바랍니다.

 

 

출처 :https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_modes.htm#SUTIL009

 

Conventional and Direct Path Loads.pdf

0.60MB

 

 

Conventional Path

설정된 일정량의 버퍼를 채우면 SQL Insert 문장을 이용해 로딩하는 방식.

Array insert로 동작하여 일반 insert와 부하가 같음.

Row-level locking을 지원

 

Direct Path

버퍼 캐시를 거치지 않고 디스크에 직접 씀

HWM의 윗부분의 block에 data를 load함. 완전히 새로운 block을 할당받아 data를 입력.

테이블 전체의 lock이 걸림. (TM-X mode lock).

 

 

Direct path load 시에 index가 unusable로 변환되는 이유?

Direct path load로 작동시에 Index를 갱신하는 부분에서 찾을 수 있습니다.

1) 데이터를 로딩하면서 인덱스 키에 해당되는 데이터는 TEMP영역에 저장됩니다.

2) 데이터 로딩이 끝난 후(commit) 기존의 인덱스와 Temp영역의 인덱스를 합치는(Merge)작업이 수행됩니다.

3) 데이터가 합쳐진 후 최종적인 인덱스가 완성됩니다.

 

2번 작업을 수행할 때 데이터 중복이나 테이블 스페이스 공간이 부족하면 Unusable로 빠지게 됩니다. direct path load 시에 unique,primary key, not null 제약조건은 데이터 로딩 이후에 최종 인덱스가 생성되는 시점에 검증이 이루어지기 때문입니다.

 

출처 : http://www.axiominfo.co.kr/default/article/column.php?com_board_basic=read_form&com_board_idx=126&&com_board_search_code=&com_board_search_value1=&com_board_search_value2=&com_board_page=4&&com_board_id=2&&com_board_id=2 

엑시엄에 오신것을 환영합니다

 

 

제가 궁금했던 것은 세가지 였습니다.

1.PK에 위배되는 데이터(중복 데이터)를 삽입 시에 DIRECT PATH와 CONVENTIONAL PATH LOAD 둘다 INDEX가 unusable로 빠지는지?

2.중복데이터가 없는 경우에도 Index가 unusable로 빠지는지?

3.direct=true 옵션없이도 direct path load 방식을 사용할 수 있는지?

 

사전준비

테이블을 만들고 데이터를 준비해줍니다.

(데이터는 사전에 테스트용으로 만들어놓은 테이블을 복사하여 사용하였습니다)

#테스트 테이블 생성
CREATE TABLE YSBAE.ORD_LOADER
(
    ORD_NO              NUMBER(10),
    ORD_DT              VARCHAR2(8),
    ORD_HMS             VARCHAR2(6),
    SHOP_NO             VARCHAR2(10),
    UPPER2              VARCHAR2(2),
    UPPER_CASE          VARCHAR2(10),
    LOWER_CASE          VARCHAR2(10),
    ALPHABET            VARCHAR2(400),
    ALPHABET_NUMERIC    VARCHAR2(400)
);

#데이터 삽입(1500만건)
INSERT INTO YSBAE.ORD_LOADER
SELECT * FROM DEV.ORD;

#PK 인덱스 생성 및 PK 제약조건 추가
CREATE UNIQUE INDEX YSBAE.ORD_LOADER_PK
ON ORD_LOADER (ORD_NO) 
;

ALTER TABLE ORD_LOADER
ADD CONSTRAINT ORD_LOADER_PK PRIMARY KEY (ORD_NO);

 

SQL loader용 data 파일 준비

Orange Tool을 이용해서 파일을 받았습니다. 다른 tool을 이용해서 받아도 상관없습니다.

#csv 파일안의 data 수
#1500만건이 있지만 data를 내려 받을때 전부다 내려 받아지지 않음.
$ wc -l *.csv
11729425 ORD_LOADER.csv
 
11,729,425건 임.

SQL loader용 ctl 파일 생성

#ctl 파일 생성
$vi ord_loader.ctl

load data
infile '/home/oracle/dba/ysbae/ORD_LOADER.csv'
append
into table ysbae.ord_loader
fields terminated by ','
(
    ORD_NO          ,
    ORD_DT          ,
    ORD_HMS         ,
    SHOP_NO         ,
    UPPER2          ,
    UPPER_CASE      ,
    LOWER_CASE      ,
    ALPHABET        ,
    ALPHABET_NUMERIC
)

준비는 끝났습니다. 테스트를 진행하겠습니다.

 

1.direct=true 옵션 없이 sqlldr 사용했을 시

수행 전 object 확인

SQL> select owner,table_name as name,status From dba_tables where table_name='ORD_LOADER'
union all
select owner,index_name as name,status from dba_indexes where table_name='ORD_LOADER';

OWNER    NAME           STATUS   
-------- -------------- -------- 
YSBAE    ORD_LOADER     VALID   
YSBAE    ORD_LOADER_PK  VALID   

2 rows selected.


SQL> select count(*) from ORD_LOADER;

COUNT(*)  
--------- 
 15000000

 

conventional path load의 경우 중복데이터 삽입 시 데이터 삽입이 0건입니다. 그리고 direct=true 옵션을 사용하지 않으면 conventional path load로 작동합니다.

sqlldr ysbae/ysbae control=/home/oracle/dba/ysbae/ord_loader.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Sep 14 11:30:21 2022

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

Path used:      Conventional
Commit point reached - logical record count 64

Table YSBAE.ORD_LOADER:
  0 Rows successfully loaded.

Check the log file:
  ord_loader.log
for more information about the load.

 

2.direct=true옵션 사용하고 sqlldr 사용 시

수행전 object 상태 확인

SQL> select owner,table_name as name,status From dba_tables where table_name='ORD_LOADER'
union all
select owner,index_name as name,status from dba_indexes where table_name='ORD_LOADER';

OWNER    NAME           STATUS   
-------- -------------- -------- 
YSBAE    ORD_LOADER     VALID   
YSBAE    ORD_LOADER_PK  VALID   

2 rows selected.


SQL> select count(*) from ORD_LOADER;

COUNT(*)  
--------- 
 15000000

 

Direct path load로 작동 시 데이터는 잘 삽입되었습니다.

$ sqlldr ysbae/ysbae control=/home/oracle/dba/ysbae/ord_loader.ctl direct=true

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Sep 14 13:12:30 2022

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

Path used:      Direct

Load completed - logical record count 11729426.

Table YSBAE.ORD_LOADER:
  11729425 Rows successfully loaded.

Check the log file:
  ord_loader.log
for more information about the load.

 

Index 상태는 Unusable 상태로 변환되었고, 자동으로 VALID상태로 변환되지는 않습니다.

SQL> select owner,table_name as name,status From dba_tables where table_name='ORD_LOADER'
union all
select owner,index_name as name,status from dba_indexes where table_name='ORD_LOADER';

OWNER    NAME            STATUS   
-------- --------------- -------- 
YSBAE    ORD_LOADER      VALID   
YSBAE    ORD_LOADER_PK   UNUSABLE

2 rows selected.


SQL> select count(*) from ORD_LOADER;

COUNT(*)  
--------- 
 26729425

1 rows selected.

 

3.중복 데이터가 없는 상태에서 Direct path load로 수행해보기

데이터가 있는 상태에서 INDEX를 rebuild 하여 정상화를 시도하면 에러가 발생합니다.

데이터 truncate 후에 다시 시도합니다.

#데이터가 있는 상태에서 rebuild 시 에러 발생
alter index ORD_LOADER_PK rebuild;

ORA-01452 발생

#truncate
TRUNCATE TABLE ORD_LOADER;

# index rebuild
alter index ORD_LOADER_PK rebuild;

 

SQL> select owner,table_name as name,status From dba_tables where table_name='ORD_LOADER'
union all
select owner,index_name as name,status from dba_indexes where table_name='ORD_LOADER';

OWNER      NAME              STATUS   
---------- ----------------- -------- 
YSBAE      ORD_LOADER        VALID   
YSBAE      ORD_LOADER_PK     VALID

 

direct path load로 sqlldr 수행

$sqlldr ysbae/ysbae control=/home/oracle/dba/ysbae/ord_loader.ctl direct=true

SQL*Loader: Release 12.2.0.1.0 - Production on Wed Sep 14 14:07:13 2022

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

Path used:      Direct

Load completed - logical record count 11729426.

Table YSBAE.ORD_LOADER:
  11729425 Rows successfully loaded.

Check the log file:
  ord_loader.log
for more information about the load.

확인

SQL> select owner,table_name as name,status From dba_tables where table_name='ORD_LOADER'
union all
select owner,index_name as name,status from dba_indexes where table_name='ORD_LOADER';

OWNER   NAME            STATUS   
------- --------------- -------- 
YSBAE   ORD_LOADER      VALID   
YSBAE   ORD_LOADER_PK   VALID   

2 rows selected.

 

INDEX가 정상상태로 계속 유지되고 있습니다.

 

결론.

즉 세가지질문에 답을 하면,

1.PK에 위배되는 데이터(중복 데이터)를 삽입 시에 DIRECT PATH와 CONVENTIONAL PATH LOAD 둘다 INDEX가 unusable로 빠지는지?

답 : Conventional path load는 중복데이터가 있을 시에 정상작동되지 않습니다(0건 succeed)

      Direct path load는 중복데이터가 있을 시에 데이터 삽입은 되며, Index unusable상태로 변합니다.

2.중복데이터가 없는 경우에도 Index가 unusable로 빠지는지?

답 : 중복데이터가 없는 경우에는 VALID상태를 유지합니다.

3.direct=true 옵션없이도 direct path load 방식을 사용할 수 있는지?

답 : direct=true 옵션이 없다면 무조건 conventional path load 방식으로 작동합니다.

 

 

끝.