SQLP 19회차 실습문제 2번

문제 참고 한 곳 

   http://simverse.tistory.com/86

   http://cafe.daum.net/oracleoracle

--- 튜닝 전 

select b.대리점번호, b.계약상태코드, count(*) as 계약건수 , max(b.계약일자) as 최근계약일 

 from 대리점 a, 계약 b 

 where a.대리점번호 = b.대리점번호 

  and to_char (b.계약일자, 'yyyymm') = :v_date_contract    

  and b.계약상태코드 in (select 코드 from 코드테이블  where 그룹코드 = 'A01')   --- 이 부분이 예측이 어려움  

  and a.업체 =: v_업체코드 

  group by b.대리점번호, b.계약상태코드

  order by b.대리점번호 

  ;

 

 

--- 튜닝 후 

     

1)

 select /*+ ordered use_nl(b) index (a 대리점_x01) index(b 계약_x01)  */ b.대리점번호, b.계약상태코드, count(*) as 계약건수 

         , max(b.계약일자) as 최근계약일 

 from 대리점 a, ( select /*+ ordered use_nl(d) index(d 계약_x02)  */ d.대리점번호 , d.계약일자, d.계약상태코드 

                 from 코드테이블 c , 계약 d    

                 where c.코드 = d.코드 

                 and c.그룹코드 = 'A01'     

                ) b 

 where a.대리점번호 = b.대리점번호 

  and b.계약일자 = to_date(:v_date_contract,'')    

  and a.업체 =: v_업체코드 

  group by b.대리점번호, b.계약상태코드

  order by b.대리점번호 

  ;

  

or   

2)

 select /*+ ordered use_nl(b) index (a 대리점_x01) index(b 계약_x01)  */ b.대리점번호, b.계약상태코드, count(*) as 계약건수 

         , max(b.계약일자) as 최근계약일 

 from 대리점 a, ( select /*+ ordered use_nl(d) index(d 계약_x02)  */ d.대리점번호 , d.계약일자, d.계약상태코드 

                 from 코드테이블 c , 계약 d    

                 where c.코드 = d.코드 

                 and c.그룹코드 = 'A01'     

                ) b 

 where a.대리점번호 = b.대리점번호 

  and to_char (b.계약일자, 'yyyymm') = :v_date_contract    

  and a.업체 =: v_업체코드 

  group by b.대리점번호, b.계약상태코드

  order by b.대리점번호 

  ;

  

 조건 1 : 600개 업체, 3000개 대래점 (하나의 업체에 4 ~ 6개의 대리점이 있다고 가정) , 계약 (10만건) 

 조건 2 : A01 에 해당하는 코드는 3개 

 조건 3 : A01 에 해당하는 코드는 향후 더 추가 될 수 있음

 

 

 1) 개선 부분 

 

 1. 계약일자 컬럼 인덱스 사용 못함

    ( 여기서는 상황에 따라서 해결 방법이 달라짐 

         1) 계약일자가 'yyyymm' 형식의 날짜 데이터라면 컬럼이 아닌 비교 값에 to_date를 사용하면 됨 

         2) 만약 계약일자가 'yyyymm'형식이 아니라면 함수 기반 인덱스 생성 ) 

 2. 적절한 인덱스 생성 

 3. 조인 순서는 대리점 테이블 부터 하도록 힌트 사용

 4. in 의 경우 in-line view를 통해서 조인 순서 조절할 수 있도록 함 

 

  대리점_pk = 대리점번호 

  대리점_x01 = 업체코드 + 대리점번호  --- 인덱스만 읽고 처리 가능  

 

 -- <인덱스>

 

  계약_pk = 계약번호 

  계약_x01 = 대리점번호 + (  1) 계약일자  or  2)  to_char(계약일자,'yyyymm')  )+ 계약상태코드  

  계약_x02 = 계약상태코드 

          

 2)  해당 쿼리를 통해 추가로 살펴 본 부분 

 

 1. 대리점 번호 인덱스 생성 후 group by를 하면 order by가 필요 없는가 

    - 위의 쿼리로 테스트 해봤는데 sort order by는 실행계획에 없고 

        sort group by nosort가 실행계획으로 출력 되었다. 

    

    테스트) 

    

    create index emp1_empno_ename on emp1(empno,ename) ;

    select /*+ index(emp1 emp1_empno_ename) */ empno, ename 

     from emp1 

    where empno = 7788

    group by empno, ename

    order by empno

    ;

 

        

 2. 위와 같은 쿼리에서 in-list 에 새로운 테이블을 사용하게 되면 처리가 어떻게 되는가 

     - unnest , view merging 이 일어날 것이라고 생각했는데 

       일단 테스트를 통해서 먼제 확인해보았다. 

       먼저 서브쿼리 테이블을 읽고 서브쿼리와 메인쿼리 테이블과 조인 한 후 

       메인 쿼리에 다른 테이블과 조인을 하였다. 

       

       테스트)

       create table emp1 

         as select *

             from emp 

         ;

       

      select a.empno, a.ename, b.dname, b.loc 

       from emp a, dept b 

      where a.deptno = b.deptno 

       and a.empno in ( select empno 

                       from emp1 

                     where deptno = 10 ) 

      ; 

       

 3. 인덱스 컬럼이 변형되어서 인데스 사용 못한다고 하였는데 복합 인덱스에  두번째 컬럼일 때도 그 영향이 있는가 

    - 복합 인덱스를 온전히 사용하는가 하지 못하는 가에 대해 이야기 했는데 영향을 주긴 한다. 

      이를 위해 테스트를 해보았는데 복합 인덱스를 사용 시 두번째 컬럼이 변형되면 제대로 복합 컬럼의 인덱스를 사용하지는 못한다. 

      제대로 사용은 못한다고 하더라도 복합 컬럼 인덱스를 통해 index를 사용할 수 있었으며 

      복합 컬럼만 출력 시 테이블 access도 일어나지 않았다. 

      다만 인덱스 사용시 두번째 조건이 access로 사용 되는가 filter 조건으로 달라졌다. 

      

      일반적으로 predicate에서 access만 나왔을 경우와 access와 filter가 함께 나왔을 경우에 

      access만 나온 경우가 더 효율적이라고 생각한다. 

      이유는 access는 필요한 block만 읽으면 되지만 access와 filter가 함께 나온 경우는 

      access를 통해 필요한 block을 읽고난 후 filter를 하는 작업을 또 해야 하는 것이다. 

      물론 추가적인 작업이 있기 때문에도 효율성을 이야기 하지만 

      생각해보면 인덱스에 a, b 컬럼이 속해 있는 복합 인덱스의 경우

      인덱스를 온전히 사용하여 인덱스에서 a, b 컬럼 둘 다 값을 가지고 있는 block에 access 해서 

      그 값을 가져 오기만 하면 된다. 

      그러나 b 컬럼을 사용하지 못한다면 결국 

      a 컬럼에 해당 하는 블록들을 모두 읽고 난 후 b 컬럼 값의 조건을 filtering 해야 한다. 

      이는 즉 읽어 들이는 block 수에서도 비효율이 발생할 수 있는 부분이다. 

            

      그런데 아래 쿼리로 해당 작업을 테스트 할 때는 두번째 조건이 filter 조건으로 사용 되었을 때 더 성능이 좋게 나왔다는 것이다. 

      이 부분은 현재 확실히 이해가 되지 않는다. 

      온전히 인데스를 이용하는 것보다 두번째 컬럼을 이용하지 않을 때 더 좋은 성능이 나왔기 때문이다. 

      이는 어떻게 설명할 수 있는지 추가적인 좀 더 봐야 할 부분이다. 

      

    테스트) 

    

     create table emp1 

      as select * 

         from emp 

         ;

    

     create index emp1_deptno_hiredate on emp1(deptno,hiredate) ;

     select /*+ index(emp1 emp1_deptno_hiredate) */ deptno , hiredate , count(*)

      from emp1

     where deptno = 10 

       and  to_char(hiredate,'yyyymm') = '198105' 

     group by deptno, hiredate

     order by deptno

     ;

     

     select /*+ index(emp1 emp1_deptno_hiredate)  */  deptno , hiredate , count(*)

      from emp1

     where deptno = 10 

      and  hiredate = to_date('19820123', 'yyyymmdd') 

     group by deptno, hiredate

     order by deptno

     ;