인덱스원리와 활용한 쿼리 튜닝 1
* 인덱스 사용이 불가능하거나 범위 스캔이 불가능한 경우
-------------------------------------------------------------------------------------
1. 인덱스 컬럼을 조건절에서 가공하는경우
select *
from 업체
where substr(업체명,1,2) = '나비' ;
2. 부정형 비교를 하는경우
select *
from 업체
where 업체명 <> 'S그룹' ;
3. is not null 을 사용하는 경우
select *
from 업체
where 업체코드 is not null ;
-------------------------------------------------------------------------------------
* 인덱스 컬럼의 가공 예
---------------------------------------------------------------------------------------------------------------
select *
from 업체
where substr(업체명,1,2) = '나비' ; => where 업체명 like '%나비%' ;
select *
from 업체
where 매출 * 12 = 120000 ; => where 매출 = 120000 /12;
select *
from 업체
where to_char(납기일시,'YYYYMMDD' = :dt ; => where 납기일시 >= to_date( :dt ,'YYYYMMDD')
and 납기일시 < to_date( :dt ,'YYYYMMDD') + 1 ;
select *
from 업체
where 업체코드||지점코드 = :str ; => where 업체코드 = substr(:str,1,2)
and 지점코드 = substr(:str,3,3) ;
---------------------------------------------------------------------------------------------------------------
* 함수기반의 index를 생성한다.
ex) 컬럼값이 null 이 있는경우 nvl을 사용한 함수기반의 index를 생성하여 사용하는 방안 있다.
* IN list 사용한다.
ex) select *
from 부서
where (이름, 나이) in ( (김,30), (Kim,29) ) ;
* 묵시적 형변환에 유의할것!
ex) .....
where a.지급연월 = substr(b.요청일자,1,6) -1 ;
-> 지급연월과 요청일자가 varchar2형이라고 할때 인덱스를 정상적으로 처리못하고 INDEX full scan처리한 결과가 나타난다.
이유는 컬럼에 숫자값을 더하거나 빼는 연산을 가하면 내부적으로 숫자형으로 형변환이 일어나기 때문이다.
substr(b.요청일자,1,6) -1 이 먼저 숫자형으로 묵시적 형변환이 일어나고,
a.지급연월 = substr(b.요청일자,1,6) -1 이 연산을 위해 a.지급연월 부분도 묵시적 형변환이 일어난다.
= where to_number(a.지급연월) = to_number(substr(b.요청일자,1,6)) -1
이쿼리는 아래와 같은 튜닝을 통해 효율성을 높일 수 있다.
=> where a.지급연월 = to_char(add_months(to_date(b.요청일자,'yyyymmdd') -1),'yyyymm') ;
ps.1 묵시적 형변환 사용시 주의사항
where n_col = c_col;
-> 숫자형 컬럼과 문자형 컬럼을 비교하면 문자형 컬럼이 숫자형으로 변환되는데, 만약 문자형 컬럼에 숫자로 변환할 수 없는
문자열이 들어있으면 쿼리 수행도중 에러가 발생한다.
ps.2 like 로 비교할때는 숫자형이 문자형으로 변환된다.
where n_col like c_col; -> where to_char(n_col) like v_col||'%' 의 묵시적 형변환이 일어난다.
ps.3 decode (a,b,c,d) 처리시 주의사항
decode(a,b,c,d)를 처리할때 a=b이면 c를 반환하고, 아니면 d를 반환한다. 이때 출력되는 값의 데이타 타입은 세번째 인자 c에 의해
결정된다. c가 문자형이고, d가 숫자형이면 내부적으로 d가 문자형으로 변환된다.
또 c인자가 null값이면 varchar2로 취급한다.
ex) select max(decode(a,'ceo',NULL,sal)) maxsal, -> select max(decode(a,'ceo',to_number(NULL),sal)) maxsal,
... ....
참고. 오라클 성능 고도화 원리와 해법2
좋은 정보라고 생각되시면 옆의 배너를 클릭하는 센스를~ㅋ