OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 신규인덱스 생성시 기존인덱스 이용
다른 테스트를 하던 중 10046 트레이스를 보다보니 인덱스 생성시에 테이블 FULL SCAN을 하는것을 보고
인덱스 생성시에도 인덱스를 이용할수 있지 않을까 생각이 들어 테스트를 진행해봄
테스트1. 아무 인덱스가 없는 상태에서 id1, id2로 인덱스 생성
테스트2. 기존 인덱스(id1, id2)가 존재하는 상태에서 id1만으로 인덱스 생성(완전중복 인덱스)
테스트3. 기존 인덱스(id1, id2)가 존재하는 상태에서 id1, id2, name으로 인덱스 생성
샘플 대용량 테이블 생성 및 데이터 삽입
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
SQL>
conn imsi/imsi
drop table sample_t purge;
create table sample_t
(id1 number, id2 number, name varchar2(10),
date1 varchar2(8), date2 varchar2(8), date3 date,
phone varchar2(13), price number, qty number,
test1 number, test2 varchar2(5), test3 varchar2(4)
)
nologging;
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..10 LOOP
FOR i IN 1..1000000 LOOP
w_ins(i).id1 := i;
w_ins(i).id2 := i||ceil(dbms_random.value(1, 10000000));
w_ins(i).name := dbms_random.string('x',5);
w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
w_ins(i).qty := ceil(dbms_random.value(1, 10));
w_ins(i).test1 := 1234;
w_ins(i).test2 := 'SQLP';
w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..1000000 INSERT INTO sample_t VALUES w_ins(i);
COMMIT;
END LOOP;
END;
/
|
용량 확인
|
1
2
3
4
5
6
7
8
|
SQL>
col segment_name for a20
select segment_name, bytes/1024/1024 mb from dba_segments
where segment_name = 'SAMPLE_T';
SEGMENT_NAME MB
-------------------- ----------
SAMPLE_T 912
|
test1 테이블 생성
|
1
2
3
|
SQL>
drop table test1 purge;
create table test1 as select * from sample_t;
|
테스트1. 아무 인덱스가 없는 상태에서 id1, id2로 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> create index test1_ix01 on test1(id1, id2);
Index created.
SQL> alter session set events '10046 trace name context off';
Session altered.
|
생성된 trace 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col name for a20
col value for a80
select name, value from v$diag_info
where name = 'Default Trace File';
NAME VALUE
-------------------- --------------------------------------------------------------------------------
Default Trace File /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35647.trc
|
tkprof 실행
|
1
2
3
4
5
|
$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35647.trc oracle19_ora_35647_t1_tkprof.txt sys=no
TKPROF: Release 19.0.0.0.0 - Development on Sat Feb 17 16:11:46 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
|
트레이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35647_t1_tkprof.txt
SQL ID: 8v4nxz0687c3a Plan Hash: 3511723362
create index test1_ix01 on test1(id1, id2)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 6 10 0
Execute 1 6.43 7.60 156870 117815 38673 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 6.44 7.61 156870 117821 38683 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 78
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD NON UNIQUE test1_IX01 (cr=117826 pr=156870 pw=74014 time=7608606 us starts=1)(object id 0)
10000000 10000000 10000000 SORT CREATE INDEX (cr=117517 pr=156869 pw=39372 time=4525331 us starts=1)
10000000 10000000 10000000 TABLE ACCESS FULL TEST1 (cr=117517 pr=117497 pw=0 time=1101942 us starts=1 cost=32144 size=140000000 card=10000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
index (re)build lock or pin object 4 0.00 0.00
db file scattered read 938 0.00 0.37
PGA memory operation 279 0.00 0.00
Disk file operations I/O 1 0.00 0.00
direct path write temp 1405 0.00 0.61
direct path read temp 1539 0.00 0.03
db file sequential read 1 0.00 0.00
direct path write 8863 0.00 1.36
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
당연하게도 인덱스 생성을 위해 테이블을 FULL SCAN 함
트레이스 설정을 위해 sqlplus 세션 재접속
테스트2. 기존 인덱스(id1, id2)가 존재하는 상태에서 id1만으로 인덱스 생성(완전중복 인덱스)
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> create index test1_ix02 on test1(id1);
Index created.
SQL> alter session set events '10046 trace name context off';
Session altered.
|
생성된 trace 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col name for a20
col value for a80
select name, value from v$diag_info
where name = 'Default Trace File';
NAME VALUE
-------------------- --------------------------------------------------------------------------------
Default Trace File /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35857.trc
|
tkprof 실행
|
1
2
3
4
5
|
$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35857.trc oracle19_ora_35857_t2_tkprof.txt sys=no
TKPROF: Release 19.0.0.0.0 - Development on Sat Feb 17 16:14:13 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
|
트레이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35857_t2_tkprof.txt
SQL ID: c90udqyf4uuq4 Plan Hash: 3711363751
create index test1_ix02 on test1(id1)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 7.57 8.47 64141 10000365 25791 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 7.57 8.47 64141 10000367 25791 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 78
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD NON UNIQUE test1_IX02 (cr=10000375 pr=64141 pw=51825 time=8475986 us starts=1)(object id 0)
10000000 10000000 10000000 SORT CREATE INDEX (cr=10000101 pr=64141 pw=29499 time=6195909 us starts=1)
10000000 10000000 10000000 INDEX FAST FULL SCAN TEST1_IX01 (cr=10000101 pr=34642 pw=0 time=3547557 us starts=1)(object id 26906)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 314 0.00 0.00
index (re)build lock or pin object 4 0.00 0.00
Disk file operations I/O 3 0.00 0.00
db file scattered read 288 0.00 0.02
direct path write temp 1087 0.00 0.28
direct path read temp 1152 0.00 0.02
direct path write 5704 0.00 0.84
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
인덱스 생성시 테이블 FULL SCAN이 아닌 TEST1_IX01 인덱스를 사용해 INDEX FAST FULL SCAN을 함
인덱스 생성시 필요한 컬럼(id1)이 TEST1_IX01에 모두 포함되어 있기 때문에 테이블을 굳이 엑세스하지 않고 기존 인덱스만 읽어 신규 인덱스를 생성한것으로 보임
트레이스 설정을 위해 sqlplus 세션 재접속
테스트3. 기존 인덱스(id1, id2)가 존재하는 상태에서 id1, id2, name으로 인덱스 생성
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> create index test1_ix03 on test1(id1, id2, name);
Index created.
SQL> alter session set events '10046 trace name context off';
Session altered.
|
생성된 trace 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col name for a20
col value for a80
select name, value from v$diag_info
where name = 'Default Trace File';
NAME VALUE
-------------------- --------------------------------------------------------------------------------
Default Trace File /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35883.trc
|
tkprof 실행
|
1
2
3
4
5
|
$ tkprof /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35883.trc oracle19_ora_35883_t3_tkprof.txt sys=no
TKPROF: Release 19.0.0.0.0 - Development on Sat Feb 17 16:17:00 2024
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
|
트레이스 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_35883_t3_tkprof.txt
SQL ID: 4xrwwbk8mp9tz Plan Hash: 3536447883
create index test1_ix03 on test1(id1, id2, name)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 1 0
Execute 1 5.95 7.43 160658 117830 41333 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.95 7.44 160658 117831 41334 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 78
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 INDEX BUILD NON UNIQUE test1_IX03 (cr=0 pr=0 pw=0 time=8 us starts=1)(object id 0)
8746979 8746979 8746979 SORT CREATE INDEX (cr=117517 pr=160650 pw=49252 time=4157609 us starts=1)
10000000 10000000 10000000 TABLE ACCESS FULL TEST1 (cr=117517 pr=117497 pw=0 time=654757 us starts=1 cost=32157 size=200000000 card=10000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PGA memory operation 327 0.00 0.00
index (re)build lock or pin object 2 0.00 0.00
asynch descriptor resize 1 0.00 0.00
Disk file operations I/O 2 0.00 0.00
db file scattered read 938 0.00 0.08
direct path write temp 1660 0.00 0.52
direct path read temp 1684 0.00 0.04
direct path write 9625 0.00 1.47
db file sequential read 8 0.00 0.00
reliable message 6 0.00 0.00
enq: RO - fast object reuse 3 0.00 0.00
enq: CR - block range reuse ckpt 3 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net break/reset to client 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
********************************************************************************
|
인덱스 생성시 테스트2 처럼 TEST1_IX01 인덱스를 사용하지 않고 테이블 FULL SCAN을 함
인덱스 생성시 name 이라는 신규 컬럼이 추가 되었기 때문에 index ffs 로는 처리할수 없어서 FULL SCAN이 동작한것으로 보임
결론 :
위 경우와 같이 기존에 존재하는 인덱스의 선두 컬럼들을 이용해 신규인덱스 생성 하는 경우
테이블 스캔을 하지 않고 기존 인덱스를 index fast full scan 방식으로 읽어 신규 인덱스를 생성함
하지만 이 방법은 완전중복 인덱스를 생성하는 방법이기 때문에 실무에 큰 도움이 되지는 않을듯 함
참조 : 친절한 SQL 튜닝