인덱스 기본 원리,. (by hj4867) [2014.06.20]
Contents
(1) 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
- 정상적인 인덱스 범위 스캔이 불가능한 경우(Index Full Scan은 가능)
- 인덱스 컬럼 가공
- {code:sql}
select *
from 업체
where substr(업체명, 1, 2) = '대한'
** 부정형 비교
** {code:sql}
select *
from 고객
where 직업 <> '학생'
- is not null 조건(부정형 비교에 해당됨)
- {code:sql}
select *
from 사원
where 부서코드 is not null
* 인덱스 사용이 불가능한 경우
** is null 조건만으로 검색할 때
** {code:sql}
select *
from 사원
where 연락처 is null
- 예외: 해당 컬럼이 not null 제약이 있을 경우 Table Full Scan을 피하기 위해 사용됨.(하지만 10g에서는 Index Full Scan으로 나타남)
- is null 조건을 사용하더라도 다른 인덱스 구성 컬럼에 is null 이외의 조건식이 하나라도 있으면 Range Scan 이 가능하다.
- 예외: 해당 컬럼이 not null 제약이 있을 경우 Table Full Scan을 피하기 위해 사용됨.(하지만 10g에서는 Index Full Scan으로 나타남)
(2) 인덱스 컬럼의 가공
인덱스 컬럼 가공사례 및 튜닝 방안
| NO | 인덱스 컬럼 가공 사례 | 튜닝 방안 | |
|---|---|---|---|
| 1 | select * from 업체 where substr(업체명,1,2) = '대한' | select * from 업체 where 업체명 like '대한%' | |
| 2 | select * from 사원 where 월급여 * 12 = 36000000 | select * from 사원 where 월급여 = 36000000/12 | |
| 3 | select * from 주문 where to_char(일시,'yyyymmdd') = :dt | select * from 주문 where 일시 >= to_date(:dt,'yyyymmdd') and 일시 < to_date(:dt,'yyyymmdd') + 1 | |
| 4 | select * from 고객 where 연령||직업 ='30공무원' | select * from 고객 where 연령=30 and 직업 ='공무원' | |
| 5 | select * from 회원사지점 where 회원번호||지점번호 = :str | select * from 회원사지점 where 회원번호 = substr(:str,1,2) and 지점번호 = substr(:str,1,2) | |
| 6 | select * from 주문 where nvl(주문수량,0) >= 100 | 주문수량이 100보다 크거나 같을 때 값을 가져오는 경우이므로 주문수량이 null인 경우는 해당 되지 않으므로 "0"으로 치환 할 필요 없음 select * from 주문 where 주문수량 >= 100 | |
| 7 | select * from 주문 where nvl(주문수량,0) < 100 | ① 1단계 : 주문수량 컬럼이 not null 여부 확인(not null일 경우 : null 치환 함수 제거) ② 2단계 : 해당건수가 많지 않다면 FBI 사용 고려(Table Full Scan이 불가피하므로) | |
| 8 | 조건절의 컬럼이 가공되어 full table scan을 탐 {code} where 거래일자 between :startDd and :endDd and 지수구분코드 | 지수업종코드 in (('1001'),('2003')) group by 거래일자 {code} | In-List로 처리해서 PK 인덱스를 사용 {code} where 거래일자 between :startDd and :endDd and (지수구분코드, 지수업종코드) in (('1','001'),('2','003')) group by 거래일자{code} |
튜닝사례1
- 인덱스 컬럼을 가공하지 않고 IN-List 사용하여 튜닝
튜닝사례2
- 인덱스 컬럼을 가공하지 않고 조건절을 재구성하여 튜닝
(3) 묵시적 형변환
- 쿼리문
- 튜닝 전 실행계획
- 위 예제에서 모든 조인 컬럼의 데이터 타입은 varchar2 이다.
- NL Outer 조인은 조인 순서가 고정돼 항상 Outer 테이블이 먼저 드라이빙된다.
- 따라서 인라인 뷰로 처리한 x쪽 집합이 먼저 읽히고, y쪽 월별품목실적 조인 컬럼에 값을 제공하게 된다.
- varchar2 컬럼인 x쪽 파트너지원요청일자 컬럼에서 앞6자리 연월을 취하고 1을 차감하는 과정에서 숫자형으로 묵시적 형변환이 일어난다.
- 그 이후, y쪽 대상연월 컬럼이 숫자형으로 형변환된다. (숫자형과 문자형이 비교될 때는 숫자형이 우선시되기 떄문)
- 결과적으로 인덱스 컬럼을 가공한 셈이 된다.
- 조건문 튜닝
- 튜닝 후 실행계획
- 쿼리 옵티마이저에 의해 일어나는 내부적인 형변환 원리를 이해하고 조인 컬럼의 데이터 타입을 일일이 따져보지 않으면 문제점을 쉽게 발견하기 어려운 튜닝 사례.
묵시적 형변환 사용시 주의사항