인덱스원리와 활용한 쿼리 튜닝 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

좋은 정보라고 생각되시면 옆의 배너를 클릭하는 센스를~ㅋ