[Tuning] Nested와 Correlated Sub-query의 동시 사용

[사례연구] Nested와 Correlated Sub-query의 동시 사용

本 사례에서는 요구사항의 특성으로 인해 SQL 의 WHERE 절에 IN과
EXISTS를 동시에 사용할 수 밖에 없는 경우, 이를 효과적으로 처리
할 수 있는 방안을 찾고자 한다.

- 요구사항 

  전체 과정(COURSE_CODE)에 대하여 2000년에 신청자가 전혀 없거나
  500 명 미만인 과정에 대해 과정코드와 과정명을 조회하고자 합니다.

전제조건

<옵티마이저>
Rule Based Optimzer

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

Before Tuning

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE A.COURSE_CODE IN (SELECT COURSE_CODE
                           FROM EC_APPLY
                          WHERE YEAR = '2000'
                          GROUP BY COURSE_CODE
                          HAVING COUNT(COURSE_CODE) < 500)
    OR NOT EXISTS (SELECT 'X'
                     FROM EC_APPLY C
                    WHERE C.COURSE_CODE = A.COURSE_CODE
                      AND C.YEAR = '2000');

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      113    727.99     728.91   20171550   22759487       6784        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115    728.00     728.91   20171550   22759487       6784        1674

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1674   FILTER                  <------------------ 서브 쿼리를 나중에 실행!
   1696    TABLE ACCESS (FULL) OF 'EC_COURSE'
   1695    FILTER                 <------------------ 'HAVING절'에 대한 작업
 646141     SORT (GROUP BY)
101033865      TABLE ACCESS (FULL) OF 'EC_APPLY'
   1308    INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

UNION ALL을 활용한 튜닝

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE A.COURSE_CODE IN (SELECT COURSE_CODE
                           FROM EC_APPLY
                          WHERE YEAR = '2000'
                          GROUP BY COURSE_CODE
                          HAVING COUNT(COURSE_CODE) < 500)
UNION ALL
SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE NOT EXISTS (SELECT 'X'
                     FROM EC_APPLY C
                    WHERE C.COURSE_CODE = A.COURSE_CODE
                      AND C.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      113      0.52       0.52      13013      19560          0        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115      0.52       0.52      13013      19560          0        1674

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1674   UNION-ALL
    387    NESTED LOOPS
    407     VIEW OF 'VW_NSO_1'
    407      FILTER
    428       SORT (GROUP BY)
  59607        TABLE ACCESS (FULL) OF 'EC_APPLY'
    387     TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE'
    387      INDEX (UNIQUE SCAN) OF 'EC_COURSE_PK' (UNIQUE)
   1287    FILTER
   1695     TABLE ACCESS (FULL) OF 'EC_COURSE'
    408     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) 

하나의 서브쿼리를 활용한 튜닝

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A
 WHERE 500 > (SELECT COUNT(*) 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.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      113      0.05       0.06         15       3989          0        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115      0.06       0.06         15       3989          0        1674

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

조인을 활용한 튜닝

SELECT A.COURSE_CODE, A.COURSE_NAME
  FROM EC_COURSE A, EC_APPLY B
 WHERE A.COURSE_CODE = B.COURSE_CODE(+)  --- EC_APPLY에는 데이터가 없을 수도 있기 때문에 없다고 배제하면 문제가 발생
   AND B.YEAR(+) = '2000'
 GROUP BY A.COURSE_CODE, A.COURSE_NAME
 HAVING COUNT(B.COURSE_CODE) < 500

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      113      0.13       0.13         30       3771          0        1674
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      115      0.13       0.14         30       3771          0        1674

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1674   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)