[Tuning] 인덱스에 의한 Sort 대체

인덱스에 의한 Sort 대체

▶ Index 정보

- 인덱스정보
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)
EC_APPLY     : EC_APPLY_PK          : COURSE_CODE + YEAR + COURSE_SQ_NO +
                                      MEMBER_TYPE + MEMBER_ID
               EC_APPLY_COMPANY_IDX : COMPANY_NO (Non Unique)


++ 테이블 중 EC_COURSE_SQ 테이블만 통계정보 존재

▶ Before Tuning

SELECT B.COURSE_CODE, C.COURSE_NAME, B.COURSE_SQ_NO,
       A.MEMBER_TYPE, A.MEMBER_ID, B.COURSE_S_DATE,
       B.COURSE_E_DATE
  FROM EC_APPLY A, EC_COURSE_SQ B, EC_COURSE C
 WHERE A.COMPANY_NO     = '1248100998'
   AND B.COURSE_CODE    = 712
   AND C.COURSE_CODE    = B.COURSE_CODE
   AND B.YEAR           = '2000'
   AND A.COURSE_CODE    = B.COURSE_CODE
   AND A.YEAR           = B.YEAR
   AND A.COURSE_SQ_NO   = B.COURSE_SQ_NO
ORDER BY B.COURSE_CODE, B.COURSE_SQ_NO, A.MEMBER_TYPE, A.MEMBER_ID

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    475   SORT (ORDER BY)
    475    NESTED LOOPS
    475     NESTED LOOPS
      1      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE'
      1       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'EC_COURSE_PK'(UNIQUE)
    475      TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY'
   5341       INDEX (RANGE SCAN) OF 'EC_APPLY_COMPANY_IDX' (NON-UNIQUE)
    475     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE_SQ'
    475      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'EC_COURSE_SQ_PK'(UNIQUE)

▶ hint 사용  /*+ ORDERED USE_NL(A B C) INDEX(A EC_APPLY_PK) */

SELECT /*+ ORDERED USE_NL(A B C) INDEX(A EC_APPLY_PK) */
       B.COURSE_CODE, C.COURSE_NAME, B.COURSE_SQ_NO,
       A.MEMBER_TYPE, A.MEMBER_ID, B.COURSE_S_DATE,
       B.COURSE_E_DATE
  FROM EC_COURSE C, EC_COURSE_SQ B, EC_APPLY A
 WHERE A.COMPANY_NO     = '1248100998'
   AND C.COURSE_CODE    = 712
   AND C.COURSE_CODE    = B.COURSE_CODE
   AND B.YEAR           = '2000'
   AND A.COURSE_CODE    = B.COURSE_CODE
   AND A.YEAR           = B.YEAR
   AND A.COURSE_SQ_NO   = B.COURSE_SQ_NO
  
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    475   TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY'
    791    NESTED LOOPS
      4     NESTED LOOPS
      1      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE'
      1       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'EC_COURSE_PK'(UNIQUE)
      4      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE_SQ'
      4       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'EC_COURSE_SQ_PK'(UNIQUE)
    786     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)    

▶ hint 사용  /*+ RULE */

SELECT /*+ RULE */
       B.COURSE_CODE, C.COURSE_NAME, B.COURSE_SQ_NO,
       A.MEMBER_TYPE, A.MEMBER_ID, B.COURSE_S_DATE,
       B.COURSE_E_DATE
  FROM EC_COURSE C, EC_COURSE_SQ B, EC_APPLY A
 WHERE A.COMPANY_NO||'' = '1248100998'
   AND C.COURSE_CODE    = 712
   AND C.COURSE_CODE    = B.COURSE_CODE
   AND B.YEAR||''       = '2000'
   AND A.COURSE_CODE    = B.COURSE_CODE
   AND A.YEAR           = B.YEAR
   AND A.COURSE_SQ_NO   = B.COURSE_SQ_NO
  
   Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: HINT: RULE
    475   TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY'
    791    NESTED LOOPS
      4     NESTED LOOPS
      1      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE'
      1       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'EC_COURSE_PK'(UNIQUE)
      4      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE_SQ'
      4       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'EC_COURSE_SQ_PK'(UNIQUE)
    786     INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) 

▶ hint 사용 /*+ USE_NL(A B C) INDEX(A EC_APPLY_PK) */

SELECT /*+ USE_NL(A B C) INDEX(A EC_APPLY_PK) */
       B.COURSE_CODE, C.COURSE_NAME, B.COURSE_SQ_NO,
       A.MEMBER_TYPE, A.MEMBER_ID, B.COURSE_S_DATE,
       B.COURSE_E_DATE
  FROM EC_COURSE C, EC_COURSE_SQ B, EC_APPLY A
 WHERE A.COMPANY_NO     = '1248100998'
   AND C.COURSE_CODE    = 712
   AND C.COURSE_CODE    = B.COURSE_CODE
   AND B.YEAR           = '2000'
   AND A.COURSE_CODE    = B.COURSE_CODE
   AND A.YEAR           = B.YEAR
   AND A.COURSE_SQ_NO   = B.COURSE_SQ_NO

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    475   NESTED LOOPS
    475    NESTED LOOPS
      1     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE'
      1      INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'EC_COURSE_PK'(UNIQUE)
    475     TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY'
    786      INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)
    475    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF'EC_COURSE_SQ'
    475     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'EC_COURSE_SQ_PK'(UNIQUE)

 ▶ hint 사용 /*+ ORDERED USE_NL(A B C) */

SELECT /*+ ORDERED USE_NL(A B C) */
       B.COURSE_CODE, C.COURSE_NAME, B.COURSE_SQ_NO,
       A.MEMBER_TYPE, A.MEMBER_ID, B.COURSE_S_DATE,
       B.COURSE_E_DATE
  FROM EC_COURSE C, EC_COURSE_SQ B, EC_APPLY A
 WHERE A.COMPANY_NO     = '1248100998'
   AND C.COURSE_CODE    = 712
   AND C.COURSE_CODE    = B.COURSE_CODE
   AND B.YEAR           = '2000'
   AND A.COURSE_CODE    = B.COURSE_CODE
   AND A.YEAR           = B.YEAR
   AND A.COURSE_SQ_NO   = B.COURSE_SQ_NO

  Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
    475   TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY'
  21369    NESTED LOOPS
      4     NESTED LOOPS
      1      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE'
      1       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'EC_COURSE_PK'(UNIQUE)
      4      TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 'EC_COURSE_SQ'
      4       INDEX   GOAL: ANALYZED (RANGE SCAN) OF 'EC_COURSE_SQ_PK'(UNIQUE)
  21364     INDEX (RANGE SCAN) OF 'EC_APPLY_COMPANY_IDX' (NON-UNIQUE)