오라클 성능 고도화 원리와 해법 II (2010년)
목록
인덱스 기본 원리 0 0 3,709

by 구루비 인덱스변환 FBI [2010.03.04]

02. 인덱스 기본 원리

  • B*Tree 인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프블록까지의 수직적 탐색 과정을 거쳐야 한다.

(1) 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우

  • 정상적인 인덱스 범위 스캔이 불가능한 경우(Index Full Scan은 가능)

- 인덱스 컬럼을 조건절에서 가공
where substr(업체명, 1, 2) = '대한'

- 부정형 비교
where 직업 <> '학생'

- is not null 조건도 부정형 비교에 해당
where 부서코드 is not null
=> '부서코드'에 단일 컬럼 인덱스가 존재한다면 인덱스 전체 스캔을 통해 얻은 레코드는 모두 '부서코드 is not null' 조건을 만족.
  • 인덱스 사용이 불가능한 경우

- is null 조건만으로 검색할 때
where 연락처 is null

=> 예외적으로 해당 컬럼이 not null 제약이 있을 경우 Table Full Scan을 피하기 위해 사용.

- is null 조건을 사용하더라도 다른 인덱스 구성 컬럼에 is null 이외의 조건식이 하나라도 있으면 Index Range Scan 가능
(인덱스 선두 컬럼이 조건걸에 누락되지 않아야 한다)
emp_idx : job + deptno
where job is null and deptno = 20

(2) 인덱스 컬럼의 가공

인덱스 컬럼 가공 사례튜닝 방안
substr(업체명, 1, 2) = '대한'업체명 like '대한%'
월급여 * 12 = 36000000월급여 = 36000000 / 12
to_char(일시, 'yyyymmdd') = :dt일시 >= to_date(:dt, 'yyyymmdd')
and 일시 < to_date(:dt, 'yyyymmdd') + 1
연령 || 직업 = '30공무원'연령 = 30
and 직업 = '공무원'
회원번호 || 지점번호 = :str회원번호 = substr(:str, 1, 2)
and 지점번호 = substr(:str, 3, 4)
nvl(주문수량, 0) >= 100주문수량 >= 100
nvl(주문수량, 0) < 100함수기반 인덱스(FBI) 생성 고려
=> create index 주문_x01 on 주문(nvl(주문수량, 0) );

튜닝 사례1


일별지수업종별거래및시세_PK : 지수구분코드 + 지수업종코드 + 거래일자
일별지수업종별거래및시세_X01 : 거래일자

거래일자 between :startDd and :endDd
and 지수구분코드 \|\| 지수업종코드 in ('1001', '2003');  => 거래일자 인데스 사용 혹은 Full Table Scan
=>
거래일자 between :startDd and :endDd
and (지수구분코드, 지수업종코드) in (('1', '001'), ('2', '003')); => PK 인덱스 사용

튜닝 사례2


접수정보파일_PK : 수신번호
접수정보파일_X01 : 정정대상접수번호 + 금감원접수번호

decode(정정대상접수번호, lpad(' ', 14), 금감원접수번호, 정정대상접수번호) = :접수번호 => Full Table Scan
=>
정정대상접수번호 in (:접수번호, lpad(' ', 14))
and 금감원접수번호 = decode(정정대상접수번호, lpad(' ', 14), :접수번호, 금감원접수번호) 
=> 접수정보파일_X01 Index Range Scan

(3) 묵시적 형변환


select count(*)
from (
   select ......
    from .....
   where .....
) x, 월별품목실적 y
where y.물품지원품목코드(+) = x.물품지원품목코드
and y.영업조직id(+)       = x. 영업조직id
and y.대상연월(+)        = substr(x.파트너지원요청일자, 1, 6) - 1

실행시간 : 5.548, 논리읽기 : 8224

...
INDEX FULL SCAN 월별품목실적_PK (cr=8197 pr=1640 pw=0 time=5508725 us)

인덱스 구성
월별품목실적_PK : 대상연월 + 영업조직ID + 물품지원품목코드
월별품목실적_N1 : 대상연월 + 물품지원품목코드 + 영업조직ID
모두 varchar2 컬럼

원인) 숫자형과 문자형이 비교될 때는 순자형이 우선시되기 때문
and y.대상연월(+)        = substr(x.파트너지원요청일자, 1, 6) - 1
=>
and to_number(y.대상연월)(+)        = to_number(substr(x.파트너지원요청일자, 1, 6)) - 1

해결)
and y.대상연월(+)        = 
to_char(add_months(to_date(x.파트너지원요청일자, 'yyyymmdd'), -1), 'yyyymm')

실행시간 : 0.001, 논리읽기 : 39

...
INDEX UNIQUE SCAN 월별품목실적_PK (cr=12 pr=0 pw=0 time=175 us)

묵시적 형변환 사용시 주의사항

  • 쿼리 수행 도중 에러가 발생하거나 결과가 틀릴 수 있다
  • 오류사례

- 문법에러 : 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 들어 있는 경우 
where n_col = v_col
              *
2행에 오류:
ORA-01722: 수치가 부적합합니다.

- like로 비교할 때만큼은 숫자형이 문자형으로 변환된다.
where n_col like v_col \|\| '%' => where to_char(n_col) like v_col \|\| '%'

- 결과 오류
max(decode(job, 'PRESIDENT', NULL, sal) 
- 숫자형이 문자형으로 변환되어 950이 3000 보다 더 큰 값으로 출력
=> 
max(decode(job, 'PRESIDENT', to_number(NULL), sal)
- decode(a, b, c, d)를 처리할 때 출력되는 데이터 타입은 세번째 인자 c에 의해 결정
- c 인자가 null 값이면 varchar2로 취급
  • 묵시적 형변환에 의존하지 말고 명시적으로 변환함수를 사용하자.

함수기반 인덱스(FBI) 활용

  • 묵시적 형변화에 의해 성능이슈가 있을 경우 임시방편으로 함수기반 인덱스(FBI)를 사용할 수 있다.
    (꼭 추후 일정을 잡아 개선해야 한다)

v_deptno = 20  
=>묵시적 형 변환 : to_number(v_deptno) = 20

create index emp_x01 on emp(to_number(v_deptno));

- SQL 변경 없이 정상적으로 인덱스 사용
  • 오라클은 null 값을 맨 뒤에 저장한다(테스트.... 방법이 틀렸나????)

create table t(a varchar2(10), b varchar2(10), c varchar2(10), d varchar2(10));
 
insert into t values('A', null, null, null); 
insert into t values('A', 'B', null, null); 
insert into t values('A', null, 'B', null); 
insert into t values('A', null, null, 'B'); 
insert into t values('A', null, 'B', null); 
insert into t values(null, null, null, 'B'); 
insert into t values(null, 'A', null, 'B');
commit;

- block dump

create index t_id on t(a, b, c, d);

- index tree dump
- index leaf block dump


--------------------------------------------- block dump

Dump file c:\oracle\product\10.2.0\admin\ora10g\udump\ora10g_ora_1840.trc
Thu Mar 04 17:50:59 2010
ORACLE V10.2.0.3.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 3
CPU                 : 2 - type 586, 2 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:1658M/3580M, Ph+PgF:1181M/3415M, VA:1304M/2047M
Instance name: ora10g

Redo thread mounted by this instance: 1

Oracle process number: 23

Windows thread id: 1840, image: ORACLE.EXE (SHAD)


*** 2010-03-04 17:50:59.578
*** ACTION NAME:() 2010-03-04 17:50:59.578
*** MODULE NAME:(01@ block_dump.sql) 2010-03-04 17:50:59.578
*** SERVICE NAME:(SYS$USERS) 2010-03-04 17:50:59.578
*** SESSION ID:(158.6) 2010-03-04 17:50:59.578
Start dump data blocks tsn: 7 file#: 6 minblk 15 maxblk 15
buffer tsn: 7 rdba: 0x0180000f (6/15)
scn: 0x0000.000f9c1a seq: 0x09 flg: 0x06 tail: 0x9c1a0609
frmt: 0x02 chkval: 0xc87c type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07AB5A00 to 0x07AB7A00
7AB5A00 0000A206 0180000F 000F9C1A 06090000  [................]
7AB5A10 0000C87C 00000001 0000D22F 000F9C18  [|......./.......]
7AB5A20 00000000 00320002 01800009 000E0009  [......2.........]
7AB5A30 000001D8 00806520 001501F3 00002007  [.... e....... ..]
7AB5A40 000F9C1A 00000000 00000000 00000000  [................]
7AB5A50 00000000 00000000 00000000 00000000  [................]
7AB5A60 00000000 00070100 0020FFFF 1F391F62  [.......... .b.9.]
7AB5A70 00001F39 1F930007 1F841F8C 1F731F7B  [9...........{.s.]
7AB5A80 1F621F6B 00000000 00000000 00000000  [k.b.............]
7AB5A90 00000000 00000000 00000000 00000000  [................]
        Repeat 498 times
7AB79C0 00000000 012C0000 4101FF04 2C4201FF  [......,....A..B,]
7AB79D0 FFFF0401 2C4201FF 41010301 2C4201FF  [......B,...A..B,]
7AB79E0 41010401 4201FFFF 0103012C 4201FF41  [...A...B,...A..B]
7AB79F0 0102012C 2C420141 41010101 9C1A0609  [,...A.B,...A....]
Block header dump:  0x0180000f
 Object id on Block? Y
 seg/obj: 0xd22f  csc: 0x00.f9c18  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1800009 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.00e.000001d8  0x00806520.01f3.15  --U-    7  fsc 0x0000.000f9c1a
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
 
data_block_dump,data header at 0x7ab5a64
===============
tsiz: 0x1f98
hsiz: 0x20
pbl: 0x07ab5a64
bdba: 0x0180000f
     76543210
flag=--------
ntab=1
nrow=7
frre=-1
fsbo=0x20
fseo=0x1f62
avsp=0x1f39
tosp=0x1f39
0xe:pti[0]	nrow=7	offs=0
0x12:pri[0]	offs=0x1f93
0x14:pri[1]	offs=0x1f8c
0x16:pri[2]	offs=0x1f84
0x18:pri[3]	offs=0x1f7b
0x1a:pri[4]	offs=0x1f73
0x1c:pri[5]	offs=0x1f6b
0x1e:pri[6]	offs=0x1f62
block_row_dump:
tab 0, row 0, @0x1f93
tl: 5 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 1]  41
tab 0, row 1, @0x1f8c
tl: 7 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 1]  41
col  1: [ 1]  42
tab 0, row 2, @0x1f84
tl: 8 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 1]  41
col  1: *NULL*
col  2: [ 1]  42
tab 0, row 3, @0x1f7b
tl: 9 fb: --H-FL-- lb: 0x1  cc: 4
col  0: [ 1]  41
col  1: *NULL*
col  2: *NULL*
col  3: [ 1]  42
tab 0, row 4, @0x1f73
tl: 8 fb: --H-FL-- lb: 0x1  cc: 3
col  0: [ 1]  41
col  1: *NULL*
col  2: [ 1]  42
tab 0, row 5, @0x1f6b
tl: 8 fb: --H-FL-- lb: 0x1  cc: 4
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
col  3: [ 1]  42
tab 0, row 6, @0x1f62
tl: 9 fb: --H-FL-- lb: 0x1  cc: 4
col  0: *NULL*
col  1: [ 1]  41
col  2: *NULL*
col  3: [ 1]  42
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 15 maxblk 15

--------------------------------------------- - index tree dump
*** 2010-03-04 17:54:23.734
*** ACTION NAME:() 2010-03-04 17:54:23.734
*** MODULE NAME:(SQL*Plus) 2010-03-04 17:54:23.734
----- begin tree dump
leaf: 0x1800014 25165844 (0: nrow: 7 rrow: 7)
----- end tree dump

-------------------------------------------------- index leaf block dump
*** 2010-03-04 17:54:53.156
*** ACTION NAME:() 2010-03-04 17:54:53.156
*** MODULE NAME:(01@ block_dump.sql) 2010-03-04 17:54:53.156
Start dump data blocks tsn: 7 file#: 6 minblk 20 maxblk 20
buffer tsn: 7 rdba: 0x01800014 (6/20)
scn: 0x0000.000f9fe3 seq: 0x01 flg: 0x00 tail: 0x9fe30601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07AB5A00 to 0x07AB7A00
7AB5A00 0000A206 01800014 000F9FE3 00010000  [................]
7AB5A10 00000000 00000002 0000D230 000F9FE2  [........0.......]
7AB5A20 00000000 00320002 01800011 00000000  [......2.........]
7AB5A30 00000000 00000000 00000000 00000000  [................]
7AB5A40 00000000 0000FFFF 00000000 00000000  [................]
7AB5A50 00000000 00008000 000F9FE2 00000000  [................]
7AB5A60 00000000 05800000 00000000 00320007  [..............2.]
7AB5A70 1ECB1EFD 00000000 00000000 00000000  [................]
7AB5A80 00000000 00001F64 1F461F55 1F281F37  [....d...U.F.7.(.]
7AB5A90 1F0B1F1A 00001EFD 00000000 00000000  [................]
7AB5AA0 00000000 00000000 00000000 00000000  [................]
        Repeat 491 times
7AB7960 FF000000 4201FFFF 00800106 0005000F  [.......B........]
7AB7970 4101FF00 064201FF 0F008001 00000600  [...A..B.........]
7AB7980 FFFF4101 800106FF 00000F00 41010000  [.A.............A]
7AB7990 4201FFFF 00800106 0003000F FF410100  [...B..........A.]
7AB79A0 06FF4201 0F008001 00000400 01FF4101  [.B...........A..]
7AB79B0 0106FF42 000F0080 01000002 FF420141  [B...........A.B.]
7AB79C0 800106FF 01000F00 00000000 00000000  [................]
7AB79D0 00000000 00000000 00000000 00000000  [................]
        Repeat 1 times
7AB79F0 00000000 00000000 00000000 9FE30601  [................]
Block header dump:  0x01800014
 Object id on Block? Y
 seg/obj: 0xd230  csc: 0x00.f9fe2  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1800011 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000f9fe2
 
Leaf block dump
===============
header address 128670308=0x7ab5a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 5
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 7933=0x1efd
kdxcoavs 7883
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8021] flag: ------, lock: 0, len=15
col 0; len 1; (1):  41
col 1; len 1; (1):  42
col 2; NULL
col 3; NULL
col 4; len 6; (6):  01 80 00 0f 00 01
row#1[8006] flag: ------, lock: 0, len=15
col 0; len 1; (1):  41
col 1; NULL
col 2; len 1; (1):  42
col 3; NULL
col 4; len 6; (6):  01 80 00 0f 00 02
row#2[7991] flag: ------, lock: 0, len=15
col 0; len 1; (1):  41
col 1; NULL
col 2; len 1; (1):  42
col 3; NULL
col 4; len 6; (6):  01 80 00 0f 00 04
row#3[7976] flag: ------, lock: 0, len=15
col 0; len 1; (1):  41
col 1; NULL
col 2; NULL
col 3; len 1; (1):  42
col 4; len 6; (6):  01 80 00 0f 00 03
row#4[7962] flag: ------, lock: 0, len=14
col 0; len 1; (1):  41
col 1; NULL
col 2; NULL
col 3; NULL
col 4; len 6; (6):  01 80 00 0f 00 00
row#5[7947] flag: ------, lock: 0, len=15
col 0; NULL
col 1; len 1; (1):  41
col 2; NULL
col 3; len 1; (1):  42
col 4; len 6; (6):  01 80 00 0f 00 06
row#6[7933] flag: ------, lock: 0, len=14
col 0; NULL
col 1; NULL
col 2; NULL
col 3; len 1; (1):  42
col 4; len 6; (6):  01 80 00 0f 00 05
----- end of leaf block dump -----
End dump data blocks tsn: 7 file#: 6 minblk 20 maxblk 20

문서에 대하여

"코어 오라클 데이터베이스 스터디모임" 에서 2010년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.

- 강좌 URL : http://www.gurubee.net/lecture/3198

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

목록
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입