인덱스 기본 원리
- 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
'정보과학 IT' 카테고리의 다른 글
[Oracle] PARTITION TABLE (0) | 2012.07.19 |
---|---|
[Oracle] Subqueries (0) | 2012.07.18 |
[Oracle] OVER () 함수 (0) | 2012.07.17 |
[Oracle] PARTITION BY 구문 (0) | 2012.07.17 |
TABLE FUNCTION 사용하기 (0) | 2012.07.13 |