[Tuning] Scalar 서브쿼리 활용

[개요]

Sub-query 는 조인과 더불어 자주 사용되고 있습니다.
특히, 아우터 조인과 똑같은 결과를 만들어 내기 위한 방안으로
1) PL/SQL을 이용한 사용자 정의 함수(User defined function)의 사용
2) Scalar Sub-query의 사용 등을 고려할 수 있습니다. 이번 예제에서
는 이러한 두 가지 방안의 내부적 수행원리를 비교해 보도록 하겠습니다.

기본정보

<옵티마이저>
Rule Based Optimizer
<인덱스정보>
EC_COURSE    : EC_COURSE_PK         : COURSE_CODE
EC_COURSE_SQ : EC_COURSE_SQ_PK      : COURSE_CODE + YEAR + COURSE_SQ_NO
               EC_COURSE_SQ_IDX_01  : YEAR (Non Unique)

0. Before Tuning

SELECT A.COURSE_CODE, A.COURSE_NAME,
       MAX(B.COURSE_SQ_NO) MAXSQ
  FROM EC_COURSE_SQ B, EC_COURSE A
 WHERE A.COURSE_CODE = B.COURSE_CODE(+)
   AND B.YEAR(+)     = '2000'
 GROUP BY A.COURSE_CODE, A.COURSE_NAME

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      2.38       2.34        102      69579          0        1695
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      116      2.38       2.34        102      69579          0        1695

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1695   SORT (GROUP BY)
   3126    NESTED LOOPS (OUTER)
   1695     TABLE ACCESS (FULL) OF 'EC_COURSE'                         (A)
   1852     TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ'            (B)
3149310      INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE)

1. Suppressing을 통한 Tuning 실행

SELECT A.COURSE_CODE, A.COURSE_NAME,
       MAX(B.COURSE_SQ_NO) MAXSQ
  FROM EC_COURSE_SQ B, EC_COURSE A
 WHERE A.COURSE_CODE = B.COURSE_CODE(+)
   AND B.YEAR(+)||''     = '2000'
 GROUP BY A.COURSE_CODE, A.COURSE_NAME

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.04       0.04        101       1843          0        1695
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      116      0.04       0.04        101       1843          0        1695

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1695   SORT (GROUP BY)
   3126    NESTED LOOPS (OUTER)
   1695     TABLE ACCESS (FULL) OF 'EC_COURSE'
   1852     INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_PK' (UNIQUE)

2. User Defined Function을 이용한 튜닝

CREATE OR REPLACE FUNCTION F_MAXSQ(A_COURSE_CODE IN NUMBER)
RETURN NUMBER
IS
  H_MAXSQ EC_COURSE_SQ.COURSE_SQ_NO%TYPE := NULL;
BEGIN
  SELECT /*+ INDEX_DESC(EC_COURSE_SQ EC_COURSE_SQ_PK) */
         COURSE_SQ_NO
    INTO H_MAXSQ
    FROM EC_COURSE_SQ
   WHERE COURSE_CODE = A_COURSE_CODE
     AND YEAR   = '2000'
     AND ROWNUM = 1;
 
  RETURN H_MAXSQ;
END;
/

SELECT A.COURSE_CODE, A.COURSE_NAME,
       F_MAXSQ(A.COURSE_CODE) MAXSQ
  FROM EC_COURSE A
 ORDER BY A.COURSE_CODE, A.COURSE_NAME

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.12       0.13         70         97          0        1695
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      116      0.12       0.13         70         97          0        1695

 

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1695   SORT (ORDER BY)
   1695    TABLE ACCESS (FULL) OF 'EC_COURSE'

3. Scalar Sub-Query를 활용한 튜닝

SELECT A.COURSE_CODE, A.COURSE_NAME,
      (SELECT /*+ INDEX_DESC(B EC_COURSE_SQ_PK) */
             B.COURSE_SQ_NO
         FROM EC_COURSE_SQ B
        WHERE B.COURSE_CODE = A.COURSE_CODE
          AND B.YEAR = '2000'
          AND ROWNUM = 1) MAXSQ
  FROM EC_COURSE A
 ORDER BY A.COURSE_CODE, A.COURSE_NAME

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.03       0.03         69       2204          0        1695
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      116      0.03       0.03         69       2204          0        1695

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
   1695   SORT (ORDER BY)
   1695    TABLE ACCESS (FULL) OF 'EC_COURSE'

-- 실행계획에 서브쿼리에 대한 정보는 보여주지 않는다.
-- select 절에 사용된 것은 실행계획에서 제공하지 않는다.