[Tuning] 서브쿼리를 통한 튜닝

기본조건

<Optimizer>
Rule Based Optimizer

<인덱스정보>
EC_COURSE    : EC_COURSE_PK         : COURSE_CODE
EC_APPLY     : EC_APPLY_PK          : COURSE_CODE + YEAR + COURSE_SQ_NO +
                                      MEMBER_TYPE + MEMBER_ID

0. Before Tuning

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_APPLY B, EC_COURSE A
 WHERE A.COURSE_CODE = B.COURSE_CODE(+)
   AND B.YEAR(+)     = '2000'
 GROUP BY A.COURSE_CODE, A.COURSE_NAME
HAVING COUNT(B.COURSE_CODE) > 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       29      0.15       0.14        171       3771          0         408
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.15       0.14        171       3771          0         408

 

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    408   FILTER                           ------ HAVING 절에 의한 FILTER이다.
   1695    SORT (GROUP BY)
  60805     NESTED LOOPS (OUTER)
   1695      TABLE ACCESS (FULL) OF 'EC_COURSE'
  59518      INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) 

1. 서브쿼리를 사용한 튜닝 (Correlated Sub-query) : 서브쿼리가 나중에 수행됨

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE EXISTS (SELECT 'X' FROM EC_APPLY B
                WHERE B.COURSE_CODE = A.COURSE_CODE
                  AND B.YEAR = '2000')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       29      0.02       0.03        149       5196          0         408
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.02       0.03        149       5196          0         408

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    408   FILTER                         --- FILTER가 있다는 것은 Sub-Query가 나중에 돌았다는 뜻이다.
   1695    TABLE ACCESS (FULL) OF 'EC_COURSE'
    408    INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

2. 서브쿼리를 사용한 튜닝(잘못된 사례 : 서브쿼리 내 function 사용) - 서브쿼리가 나중에 수행됨

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE EXISTS (SELECT COUNT(1)  FROM EC_APPLY B    --- 서브쿼리에서 함수사용을 하면 효력이 없다.
                WHERE B.COURSE_CODE = A.COURSE_CODE
                  AND B.YEAR = '2000')

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      114      0.13       0.12       1009       5572          0        1695
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      116      0.13       0.12       1009       5572          0        1695

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1695   FILTER
   1695    TABLE ACCESS (FULL) OF 'EC_COURSE'
   1695    SORT (AGGREGATE)
  59518     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

3. 서브쿼리를 통한 튜닝 - 서브쿼리가 먼저 수행

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE A.COURSE_CODE IN (SELECT B.COURSE_CODE
                           FROM EC_APPLY B
                          WHERE B.YEAR = '2000')

-- 이때 중요한 점은 A.COURSE_CODE에는 인덱스 설정이 되어야 한다는 것이다.
--  속도가 늦어진 이유는 EC_APPLY 테이블의 YEAR에는 INDEX설정이 되어 있지 않기때문에 FULL TABLE SCAN을 한다.

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch       29      0.64       0.81      13126      14352          0         408
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       31      0.65       0.81      13126      14352          0         408

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    408   NESTED LOOPS
    428    VIEW OF 'VW_NSO_1'
    428     SORT (UNIQUE)               ---- 같은 값이 많으므로 내부에서 DISTINCT 와 같은 역하를 한다.
  59607      TABLE ACCESS (FULL) OF 'EC_APPLY'
    408    TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE'
    408     INDEX (UNIQUE SCAN) OF 'EC_COURSE_PK' (UNIQUE)