잡동사니 보물섬

--- 튜닝 전 

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.대리점번호 

  ;

select b.deri_no, b.con_code, count(*) as con_count , max(b.con_date) as last_date 

 from deri a, contract2 b 

 where a.deri_no = b.deri_no 

  and to_char (b.con_date, 'yyyymm') = '199511'  

  and b.con_code in (select con_code from code  where group_code = 'A01')     

  and a.co_code ='A590' 

  group by b.deri_no, b.con_code

  order by b.deri_no

  ; 

--- 튜닝 후 

1) 계약일자가 'yyyymm' 형식으로 입력 될 경우 

 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.대리점번호 

  ;

2) 계약일자가 'yyyymmdd' 형식으로 입력 될 경우 

   select /*+ ordered use_nl(b) index (a deri_x01) index(b contract2_x01)  */ 

          b.deri_no, b.con_code, count(*) as con_count , max(b.con_date) as last_date 

     from deri a, ( select /*+ ordered use_nl(d) index(d contract2_x02)  */ d.deri_no , d.con_date, d.con_code

                        from code c , contract2 d    

                        where c.con_code = d.con_code 

                         and c.group_code = 'A01'     

                     ) b 

     where a.deri_no = b.deri_no 

      and to_char (b.con_date, 'yyyymm') = '199511'    

      and a.co_code = 'A590' 

    group by b.deri_no, b.con_code

    order by b.deri_no

     ;

  --- 인덱스 

  대리점_pk = 대리점번호 

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

  계약_pk = 계약번호 

  계약_x01 = 대리점번호 + 계약일자 + 계약상태코드  

  계약_x02 = 계약상태코드     

  create index deri_x01 on deri(co_code, deri_no) ;

  create index contract2_x01 on contract2(deri_no, to_char(con_date,'yyyymm') , con_code ) ;

  create index contract2_x02 on contract2(con_code) ;

--- 대리점 table 

create table 대리점 

 as 

   select round( dbms_random.value(1, 3000) , 0) as 대리점번호, 'a' as 대리점명 , 'b' as 업체코드 , 'c' as 업체명 

    from dual 

    connect by level <= 3000 

;

update deri 

 set co_code = 'A'|| lpad ( round(dbms_random.value(1, 600),0) ,3 ,0) 

 where deri_no > 0

; 

 select count ( distinct(co_code) ) 

  from deri 

;

select avg(count(*)) 

 from deri 

 group by co_code 

 ;

  --- dbms_random 패키지를 사용해서 딱 600개의 코드는 나오지 않지만 그와 근사치로 나온다 

      (저는 distinct count가 596 나왔습니다.) 

      또 그룹 코드당 평균 5개 정도 대리점을 가지고 있다. 

commit ;

---- 코드 테이블 

create table code  

 as 

   select 'a'||to_char ( round( dbms_random.value(1,10) , 0 ) ) as con_code , 'D01' as group_code 

     from dual 

     connect by level <= 10 

    ;

 update code 

 set group_code = 'A01' 

 where rownum <= 3 

 ;

 select *

  from code 

  where group_code = 'A01'

  ;

 commit ;

 --- 계약 테이블 

 create table 계약1 

  as 

    select /*+ 날짜형식 yyyymm */ rownum as 계약번호, dbms_random.value (1, 3000) as 대리점번호

          , 'a'||to_char ( round( dbms_random.value(1,10) , 0 ) ) as 계약상태코드

          , to_date(round(dbms_random.value(1980, 1999),0) || round(dbms_random.value(1,12),0) , 'yyyymm' ) as 계약일자

     from dual 

     connect by level <= 1000000

;

or 

 create table 계약2 

  as 

    select /*+ 날짜형식 yyyymmdd */ rownum as 계약번호, dbms_random.value (1, 3000) as 대리점번호, 'a'||to_char ( round( dbms_random.value(1,10) , 0 ) ) as 계약상태코드

          , to_date(round(dbms_random.value(1980, 1999),0) || lpad(round(dbms_random.value(1,12),0) ,2 ,0) || round(dbms_random.value(1,28),0) , 'yyyymmdd' ) as 계약일자

     from dual 

     connect by level <= 1000000

;

   -- 날짜는 2월달 때문에 28일까지로 맞춤 

문제 참고 한 곳 

   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

     ;