[친절한SQL튜닝] 인덱스 및 제약 해제를 통한 대량 DML 튜닝

인덱스와 무결성 제약 조건은 DML 성능에 큰 영향을 끼친다. OLTP에서는 어렵지만,

인덱스,무결성 제약조건을 해제함으로써 OLAP 대용량 dml시 성능개선 효과를 볼 수 있다.

 

1. 테스트 데이터 생성

create table source
as select b.no, a.*
from (select * from emp where rownum<=10) a ,
( select rownum  no from dual connect by level <= 1000000) b;

--test테이블 생성 : PK, 인덱스, 인덱스 확인
create table target as select * from source where 1=2;
alter table target add constraint target_pk primary key (no, empno);
create index target_x1 on target(ename);

SELECT * FROM dba_indexes WHERE owner = 'ADMIN' AND table_name = 'TARGET';
/*
TARGET_PK	NORMAL	ADMIN	TARGET	UNIQUE	VALID
TARGET_X1	NORMAL	ADMIN	TARGET	NONUNIQUE	VALID
 */

 

2. 테스트

/* TEST1 : PK(unique인덱스포함), 인덱스 존재할때 1000만건 insert : 2분 7초 */
insert /*+ append */ into target
select * from source;
commit; --2분 7초


/* test2 : PK제약과 인덱스 해제 : PK(unique인덱스) 인 경우 : 53초
 데이터 inset시간 ( 21초 ) + pk활성화시간(19초) + 인덱스 rebuild시간(13초) */ 
truncate table target;

--pk제약과 인덱스해제, pk비활성화하면서 TARGET_X1 unique 인덱스 drop
alter table target modify constraint target_pk disable drop index;

--일반인덱스 unusable 
alter index target_x1 unusable;

--인덱스 확인
SELECT table_name, index_name, uniqueness, status  FROM dba_indexes WHERE owner = 'ADMIN' AND table_name = 'TARGET';
--TARGET	TARGET_X1	NONUNIQUE	UNUSABLE

--데이터 insert
insert /*+ append */ into target select * from source; --21초

--pk활성화 : TARGET_PK unique인덱스 다시생성됨
alter table target modify constraint target_pk enable novalidate; --19초

--일반인덱스 재생성
alter index target_x1 rebuild; --13초


--test2-1 참고 : unique pK인덱스를 unusable 변경한 상태에서는 데이터를 입력할 수 없다.
truncate table target;

alter index target_pk unusable;

insert /*+ append */ into target
select * from source; --Error : ORA-26026: 유일 ADMIN.TARGET_PK 인덱스는 처음과 같은 사용불가능한 상태입니다

insert  into target
select * from source; --errpr : ORA-01502: 인덱스 'ADMIN.TARGET_PK'또는 인덱스 분할영역은 사용할 수 없은 상태입니다
 
--pk인덱스를 drop하지않고 unusable상태에서 사용하려면 PK제약에 non-unique 인덱스를 사용
truncate table target;
alter table target drop primary key drop index;
-- non-unique인덱스생성
create index target_pk on target(no, empno); 

--pk추가 non-unique인덱스 활용
alter table target add constraint target_pk primary key (no, empno) using index target_pk;
 

/* test3 : PK제약과 인덱스 해제 : PK(non-unique인덱스) 인 경우 : 60초
 데이터 inset시간 ( 21초 ) + PK인덱스 rebuild(13초 ) + 인덱스 rebuild시간(16초) + pk활성화시간(0초)  */
alter table target modify constraint target_pk disable keep index; --인덱스보존
alter index target_pk unusable; --non-unique로 재생성했으므로 비활성화 따로 진행
alter index target_x1 unusable;

insert /*+ append */ into target select * from source; --21초 

alter index target_pk rebuild; 13초
alter index target_x1 rebuild; 16초

alter table target modify constraint target_pk enable novalidate;

PK와 인덱스가 존재할 때 2분 7초 걸렸던 작업이 PK,인덱스 제약 해제 후 작업 후 1분정도로 수행

서버 사양에 따라 차이가 있지만 성능이 많이 개선됨을 확인

 

ㅇ PK제약과 인덱스 해제 : PK(unique인덱스) 인 경우

1. PK제약해제 및 PK인덱스 DROP, 인덱스 unusable

2. DATA INSERT

3. PK활성화, 일반인덱스 재생성 (rebuild)

 

ㅇ PK제약과 인덱스 해제 : PK(non-unique인덱스) 인 경우 -> PK비활성화, PK인덱스 unusable, 인덱스 unusable

1. PK비활성화, PK인덱스 unusable, 인덱스 unusable

2. DATA INSERT

3. PK 활성화, 일반인덱스 재생성, pk인덱스 재생성

 

 

출처 : 친절한SQL튜닝