append 힌트 재대로 사용하시나요?

흔히 대량 데이터를 insert 할 때 많은 DBA들이 append 힌트를 사용 하라고 한다.

근데 무작정 append 힌트 쓴다고 과연 빨라질까?

append 힌트는 데이터베이스가 noarchive mode 혹은 table을 nologging 으로 설정 시에만 사용 가능하다.

다음과 같은 상황에서 빠르게 insert 가 가능하다.

1. 대량의 redo를 발생시켜 archive mode에서 log switch를 방해. -> nologging 설정

2. append 힌트 사용시 direct load를 지원하여 버퍼캐쉬를 거치지 않아 rollback 정보 생성 안함.

대부분 위 사실을 다들 알고 있다.

근데 간과한 것이 하나 있으니 그것은 "INDEX" 다.

append 힌트 사용해서 열심히 insert 해도 인덱스가 있어서 다량의 redo와 index spilt으로 인한 성능 저하는 피할 수 없다.

인덱스를 다 날리고 작업을 하던가 아니면 인덱스를 비활성화 하고 진행하자.

1. alter index table1_idx unusable;

2. alter session set skip_unusable_indexes=true;

3. insert /*+ append */ into table1 select * from table2;

4. alter index big_table1_idx rebuild nologging;

여기서 또 중요한 사실이 하나 있으니 테이블 만들때 PK를 어떻게 만들었냐에 따라 에러를 만나게 될 수도 있다.

SQL> create table test1 (col1 number,

  2  col2 number);

Table created.

SQL> create unique index test1_pk on test1(col1);

Index created.

SQL> alter table test1 add constraints test1_pk primary key(col1);

Table altered.

SQL> alter index test1_pk unusable;

Index altered.

17:38:38 SQL> insert /*+ append */ into test1 select * from test2;

2 rows created.

SQL> create table test1

 2  (col1 number, col2 number, constraint test1_pk primary key(col1));

Table created.

SQL> alter index test1_pk unusable;

Index altered.

SQL> insert /*+ append */ into test1 select * from test2;

insert /*+ append */ into test1 select * from test2

                          *

ERROR at line 1:

ORA-26026: unique index SYS.TEST1_PK initially in unusable state

난 첫번째 방법으로 PK만드는 것을 선호한다.

좀 번거롭긴 하지만 tablespace도 지정할 수 있고, 삭제시 안전 장치도 된다.