꿈꾸는 개발자, DBA 커뮤니티 구루비

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년 03월 04일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.*

2. 인덱스 기본 원리 (by jongmali) [2010.03.04]

02. 인덱스 기본 원리

문서에 대하여