2. 인덱스 기본 원리

조회 수 16620 추천 수 0 2011.02.23 23:22:49
balto *.205.181.6
  •  B*Tree 인덱스를 정상적으로 사용하려면 범위 스캔 시작지점을 찾기 위해 루트 블록부터 리프블록까지의
    수직적 탐색 과정을 거쳐야 한다.

 

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

case

조건

예제

1

인덱스 컬럼을 조건절에서 가공하는 경우

select * from 업체

where substr(업체명, 1, 2) = '대한';

2

부정형 비교

select * from 고객

where 직업 <> '학생';

3

is not null 조건

select * from 사원

where 부서코드 is not null;

 

- 오라클에서 null 값은 index에 저장하지 않는다.

- 3의 경우 index에 없는 데이터를 답으로 찾을 수 있다.

- NOT NULL 제약이 명시된 empno 속성을 아래와 같이 검색하면 index를 스캔하기도 한다.
   select * from emp where empno is null; => 답이 없음 확인 하는 과정이다.

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

  emp_idx : job + deptno

  select * from emp where job is null and deptno = 20


(2) 인덱스 컬럼의 가공

인덱스 컬럼 가공 사례

튜닝 방안

select * from 업체

where substr(업체명, 1, 2) = '대한';

select * from 업체

where 업체명 like '대한%';

select * from 사원

where 월급여 * 12 = 36000000;

select * from 사원

where 월급여 = 36000000 / 12;

select * from 주문

where to_char(일시, 'yyyymmdd') = :dt;

select * from 주문

where

  일시 >= to_date(:dt, 'yyyymmdd') and 일시   <to_date(:dt, 'yyyymmdd') + 1;

select * from 고객

where 연령 || 직업 = '30공무원';

select * from 고객

where 연령 = 30 and 직업 = '공무원';

select * from 회원사지점

where 회원번호 || 지점번호 = :str;

select * from 회원사지점

  where 회원번호 = substr(:str, 1, 2) and 지점번호 = substr(:str, 3, 4);

select * from 주문

where nvl(주문수량, 0) >= 100;

* nvl 때문에 index를 활용하지 않음

select * from 주문

where 주문수량 >= 100;

select * from 주문

where nvl(주문수량, 0) <100;

 

* 주문수량이 NOT NULL이 아니면 수정

함수기반 인덱스(FBI) 생성 고려 => create index 주문_x01 on 주문(nvl(주문수량, 0) );

튜닝 사례1

조건

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

일별지수업종별거래및시세_X01 : 거래일자

튜닝전

select ... from

where 거래일자 between :startDd and :endDd

          and 지수구분코드 || 지수업종코드 in ('1001', '2003');

문제

일별지수업종별거래및시세_PK인덱스 사용 불능

튜닝후

select ... from

where 거래일자 between :startDd and :endDd

          and (지수구분코드, 지수업종코드) in (('1', '001'), ('2', '003'));

설명

IN-List 사용으로 인덱스 일별지수업종별거래및시세_PK 사용

튜닝 사례2

조건

접수정보파일_PK : 수신번호

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

튜닝전

select ... from

where decode(정정대상접수번호, lpad(' ', 14),

                     금감원접수번호, 정정대상접수번호) = :접수번호;

문제

인덱스를 가공했기 때문에 Full Table Scan

튜닝후

select ... from

where 정정대상접수번호 in (:접수번호, lpad(' ', 14))

           and 금감원접수번호 = decode(정정대상접수번호, lpad(' ', 14), :접수번호,

                                                               금감원접수번호);

설명

접수번호=정정대상접수번호가 공백이면 금감원접수번호, 아니면 정정대상접수번호와 같다.

=> (로직변경)

(3) 묵시적 형변환

튜닝 사례

조건

인덱스 구성

월별품목실적_PK : 대상연월 + 영업조직ID + 물품지원품목코드

월별품목실적_N1 : 대상연월 + 물품지원품목코드 + 영업조직ID

모두 varchar2 컬럼

튜닝전

select count(*)

from (

      select ......

      from .....

      where .....

) x, 월별품목실적 y

where y.물품지원품목코드(+) = x.물품지원품목코드

         and y.영업조직id(+) = x. 영업조직id

         and y.대상연월(+) = substr(x.파트너지원요청일자, 1, 6) - 1;

문제

Index Range Scan 대신 Index Full Table Scan 사용

INDEX FULL SCAN 월별품목실적_PK

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

숫자형과 문자형이 비교될 때는 숫자형이 우선시되기 때문

 

and y.대상연월(+) = substr(x.파트너지원요청일자, 1, 6) - 1

=> and to_number(y.대상연월)(+) = to_number(substr(x.파트너지원요청일자, 1, 6)) - 1

튜닝후

select count(*)

from (

      select ......

      from .....

     where .....

) x, 월별품목실적 y

where y.물품지원품목코드(+) = x.물품지원품목코드

           and y.영업조직id(+) = x. 영업조직id

           and y.대상연월(+) = to_char(add_months

(to_date(x.파트너지원요청일자, 'yyyymmdd'), -1), 'yyyymm')

설명

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

INDEX UNIQUE SCAN 월별품목실적_PK

묵시적 형변환 사용시 주의사항 : 쿼리 수행 도중 에러가 발생하거나 결과가 틀릴 수 있다

사례 1

문법 에러 : 문자형 컬럼에 숫자로 변환할 수 없는 문자열이 들어 있는 경우

where n_col = v_col

*

2행에 오류:

ORA-01722: 수치가 부적합합니다.

설명

- like로 비교할 때만큼은 숫자형이 문자형으로 변환된다.

해결

where n_col like v_col || '%'

      => where to_char(n_col) like v_col || '%'

 

사례 2

결과 오류

max(decode(job, 'PRESIDENT', NULL, sal)

설명

- 숫자형이 문자형으로 변환되어 사장 급여가 950(낮은값)이 출력

=> 950이 나온 이유... 사원의 급여(숫자)를 모두 문자로 바꾸어보니 그중 '950'(문자열)이 가장 max(큰값)이 출력된 것이다.....

decode(a, b, c, d)를 처리할 때 출력되는 데이터 타입은 세번째 인자 c에 의해 결정, c가 문자형이고 d가 숫자형이면 내부적으로 d가 문자형으로 바뀜.

- c 인자가 null 값이면 varchar2로 취급

해결

max(decode(job, 'PRESIDENT', to_number(NULL), sal)

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

조건

create table emp as select * from scott.emp;

alter table emp add v_deptno varchar2(2);

update emp set v_deptno = deptno;

create index emp_x01 on emp(v_deptno);

튜닝전

select * from emp where v_deptno = 20;

문제

Full Table Scan

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

튜닝후

drop index emp_x01;

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

select * from emp where v_deptno = 20;

설명

SQL 변경 없이 정상적으로 인덱스 사용(INDEX RANGE SCAN)

 

  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 박우창 (balto)
  • 최초작성일: 2011년 2월 20일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^