[TIBERO] 인덱스 추가 시, Plan변화 TEST

 

1. Table 생성

create tablespace USER_TEST01 datafile 'TS_USER_TEST_01.dtf' size 2G;

create user test_user01 identified by "test01#$" default tablespace USER_TEST01;

grant connect, resource to test_user01;

--drop table test_user01.test01;
create table test_user01.test01 
(c1 number, 
c2 number, 
c3 varchar2(10)
)
nologging;

 

2. 50만건 insert

DECLARE
TYPE tbl_ins IS TABLE OF test_user01.test01%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..5 LOOP
FOR i IN 1..100000 LOOP 
   w_ins(i).c1   := i;
   w_ins(i).c2   := i||ceil(dbms_random.value(1, 10000000));
   w_ins(i).c3  := dbms_random.string('x',5);   
END LOOP;
FORALL i in 1..100000 INSERT INTO test_user01.test01 VALUES w_ins(i);
   COMMIT;
END LOOP;
END;

 

3. 인덱스 생성

create index test_user01.idx_test01 on test_user01.test01(c1, c2);

 

4. 플랜확인

-. RS이후 , table access

set autot on
 
SQL> select c3 from test_user01.test01
   2 where c1=1100
   3 and c2=11003844574;

C3        
----------
SIDIQ

1 row selected.

SQL ID: bpcvw7b3353kr
Child number: 162192
Plan hash value: 2148334212

Execution Plan
--------------------------------------------------------------------------------
   1  TABLE ACCESS (ROWID): TEST01 (Cost:4, %%CPU:0, Rows:1) 
   2    INDEX (RANGE SCAN): IDX_TEST01 (Cost:3, %%CPU:0, Rows:1)

 

※ 위 상태에서 index에 c3를 추가하게되면, 캐싱된 플랜이 있지만 새로운 인덱스를 사용하여 플랜을 만드는지 TEST

 

5. 인덱스 생성 이후, 플랜확인

-. RS이후 table access X

select c3 from test_user01.test01
where c1=1100
and c2=11003844574;

C3        
----------
SIDIQ

1 row selected.

SQL ID: bpcvw7b3353kr
Child number: 162194
Plan hash value: 280239802

Execution Plan
--------------------------------------------------------------------------------
   1  COLUMN PROJECTION (Cost:3, %%CPU:0, Rows:1) 
   2    INDEX (RANGE SCAN): IDX_TEST02 (Cost:3, %%CPU:0, Rows:1)

 

* IDX_TEST02 라는 더 효율 좋은 인덱스로 플랜이 플린다.