DML Tuning By Removing/Rebuilding Index and Constraints

Inserting Data: Index가 있는 경우(특히 많은 경우) 항상 정렬된 상태를 유지해야 하는 특성때문에 인덱스를 사용하지 않도록 설정을 바꾼 뒤 -> data insert -> 다시 index rebuild가 더 빠를수 있음. * novalidate : pk 검증 안하고 index 생성하도록 하는 옵션

 

 

create table source

as

select b.no, a.*

from (select * from emp where rownum <=10 ) a

        , (select rownum as no from dual connect by level <=100000) b;

 

create table target as

select * from source where 1=2;

 

alter table target add cosnstraint target_pk primary key(no,empno);

create index target_x1 on target(ename);

set timing on;

insert /*+append*/ into taget 

select * from source

 

commit;

 

-> 1min 19 sec

 

truncate table target;

--set target table: without indexes

alter table target modify constraint target_pk idsable keep index;

alter index target_pk unusable;

alter index target_x1 unusable;

 

insert into target 

select * from source;

 

commit;

 

-> 5 sec

 

--then rebuilding indexes

alter table target add constraint target_pk primary key(no,empno);

using index target_pk;

 

--or rebuilding indexes by using below queries:

alter index target_x1 rebuild;

alter index target_pk rebuild;

alter table target modify constraint target_pk enable novalidate;