2014. 4. 28. 20:59

[Oracle] SUBQUERY -> INLINE VIEW

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

 

 

'DB Tuning' 카테고리의 다른 글

[Oracle] LEADING HINT  (0) 2014.04.28
[Oracle] HINT 정리  (0) 2014.04.28
[Oracle] INTERSECT, MINUS -> JOIN, NOT EXISTS  (0) 2014.04.28