나의 향기에 젖다...

1. Before SQL & Plan

               TITEMPACK B,
               TPURCHASEORDERHI C
  WHERE  C.OWNERID = '1101'
      AND  C.CUSTID IN (SELECT  OWNERCUSTID
                                      FROM   TOWNERCUSTOMER
                                    WHERE  OWNERID = '1101'
                                        AND   CUSTID = '137490')
      And  a.OwnerID = c.OwnerID

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.030        0.014          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2   86.410     1154.691     727343    7614453          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4   86.440     1154.705     727343    7614453          0          1

1.1 문제점 및 해결책

  - c.custid 조건절에 사용 되어지는 SUBQUERY에서 TPURCHASEORDERHI 테이블 액세스 시 성능저하가 발생하여 SubQuery를 인라인뷰로 변경하여 조인하는 방식으로 튜닝하여 성능개선
 

2. After SQL

               TITEMPACK B,
               TPURCHASEORDERHI C,
               (SELECT OWNERCUSTID
                FROM   TOWNERCUSTOMER
                WHERE  OWNERID = '1101'
                AND    CUSTID = '137490') D
  WHERE  C.OWNERID = '1101'
       AND  C.CUSTID = D.OWNERCUSTID
       AND  A.OWNERID = C.OWNERID

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.010        0.011          0          0          0          0
Execute      1    0.000        0.000          0          0          0          0
Fetch        2    0.020        0.019          0       4245          0          1
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.030        0.030          0       4245          0          1