by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

2. 인덱스 기본 원리




2. 인덱스 기본 원리

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 not null 컬럼이면 nvl제거, 아니면 함수기반 인덱스(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 변경 없이 정상적으로 인덱스 사용
    

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.