SQL_Tuning (Index 생성)

원인 : 바인드변수(b1)을 입력받아 쓰는데 해당 바인드변수와 비교되는 컬럼의 인덱스가 없음.
        분포도도 좋고 인덱스를 추가하기로 결정함.(컬럼명 GUARANTEENO)

1. 해당 SQL과 인덱스 생성전 트레이스

SELECT SUM( C.TOTALCONTRACTAMT )
FROM ( SELECT A.ORDERNO
    FROM BGA_B215MK A, BGA_CMN_PRE_SENT B
    WHERE A.TRANSACTIONNO = B.TRANSACTIONNO
   AND A.SND_RCV_DT = B.SND_RCV_DT
   AND A.SND_RCV_GB = B.SND_RCV_GB
   AND A.MGR_SEQ = B.MGR_SEQ
   AND B.SND_RCV_YN = 'Y'
   AND A.GUARANTEENO = :b1 ) A, (
    SELECT ORDERNO, MAX( SEND_DATA ) SEND_DATA
    FROM ( SELECT A.ORDERNO, ( B.TRANSACTIONDATE || B.TRANSACTIONTIME
      ) SEND_DATA
     FROM BGA_B211MK A, BGA_CMN_PRE_SENT B
     WHERE A.TRANSACTIONNO = B.TRANSACTIONNO
    AND A.SND_RCV_DT = B.SND_RCV_DT
    AND A.SND_RCV_GB = B.SND_RCV_GB
    AND A.MGR_SEQ = B.MGR_SEQ
    AND B.SND_RCV_YN = 'Y'
    AND A.GUARANTEENO = :b1
     UNION
     SELECT A.ORDERNO, ( B.TRANSACTIONDATE || B.TRANSACTIONTIME
      ) SEND_DATA
     FROM BGA_B213MK A, BGA_CMN_PRE_SENT B
     WHERE A.TRANSACTIONNO = B.TRANSACTIONNO
    AND A.SND_RCV_DT = B.SND_RCV_DT
    AND A.SND_RCV_GB = B.SND_RCV_GB
    AND A.MGR_SEQ = B.MGR_SEQ
    AND B.SND_RCV_YN = 'Y'
    AND A.GUARANTEENO = :b1 )
    GROUP BY ORDERNO ) B, (
    SELECT ORDERNO, SEND_DATA, TOTALCONTRACTAMT
    FROM ( SELECT A.ORDERNO, ( B.TRANSACTIONDATE || B.TRANSACTIONTIME
      ) SEND_DATA, A.TOTALCONTRACTAMT
     FROM BGA_B211MK A, BGA_CMN_PRE_SENT B
     WHERE A.TRANSACTIONNO = B.TRANSACTIONNO
    AND A.SND_RCV_DT = B.SND_RCV_DT
    AND A.SND_RCV_GB = B.SND_RCV_GB
    AND A.MGR_SEQ = B.MGR_SEQ
    AND B.SND_RCV_YN = 'Y'
    AND A.GUARANTEENO = :b1
     UNION
     SELECT A.ORDERNO, ( B.TRANSACTIONDATE || B.TRANSACTIONTIME
      ) SEND_DATA, A.TOTALCONTRACTAMT
     FROM BGA_B213MK A, BGA_CMN_PRE_SENT B
     WHERE A.TRANSACTIONNO = B.TRANSACTIONNO
    AND A.SND_RCV_DT = B.SND_RCV_DT
    AND A.SND_RCV_GB = B.SND_RCV_GB
    AND A.MGR_SEQ = B.MGR_SEQ
    AND B.SND_RCV_YN = 'Y'
    AND A.GUARANTEENO = :b1 ) ) C
WHERE A.ORDERNO = B.ORDERNO
  AND B.ORDERNO = C.ORDERNO
  AND B.SEND_DATA = C.SEND_DATA
===================================

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      2.50       2.55         11     645350         24           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      2.52       2.57         11     645350         24           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 34  (STL1B2B)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      0    NESTED LOOPS
      0     MERGE JOIN
      0      MERGE JOIN
      0       SORT (JOIN)
      0        VIEW
      0         SORT (UNIQUE)
      0          UNION-ALL
      0           NESTED LOOPS
      0            TABLE ACCESS (FULL) OF 'BGA_CMN_PRE_SENT'          <---
      0            TABLE ACCESS (BY INDEX ROWID) OF 'BGA_B211MK'
      0             INDEX (UNIQUE SCAN) OF 'PK_BGA_B211MK'
                        (UNIQUE)
      0           NESTED LOOPS
      0            TABLE ACCESS (FULL) OF 'BGA_CMN_PRE_SENT'           <---
      0            TABLE ACCESS (BY INDEX ROWID) OF 'BGA_B213MK'
      0             INDEX (UNIQUE SCAN) OF 'PK_BGA_B213MK'
                        (UNIQUE)
      0       SORT (JOIN)
      0        VIEW
      0         SORT (GROUP BY)
      0          VIEW
      0           SORT (UNIQUE)
      0            UNION-ALL
      0             NESTED LOOPS
      0              TABLE ACCESS (FULL) OF 'BGA_CMN_PRE_SENT'
      0              TABLE ACCESS (BY INDEX ROWID) OF
                         'BGA_B211MK'
      0               INDEX (UNIQUE SCAN) OF 'PK_BGA_B211MK'
                          (UNIQUE)
      0             NESTED LOOPS
      0              TABLE ACCESS (FULL) OF 'BGA_CMN_PRE_SENT'
      0              TABLE ACCESS (BY INDEX ROWID) OF
                         'BGA_B213MK'
      0               INDEX (UNIQUE SCAN) OF 'PK_BGA_B213MK'
                          (UNIQUE)
      0      SORT (JOIN)
      0       TABLE ACCESS (FULL) OF 'BGA_B215MK'
      0     TABLE ACCESS (BY INDEX ROWID) OF 'BGA_CMN_PRE_SENT'
      0      INDEX (UNIQUE SCAN) OF 'PK_BGA_CMN_PRE_SENT' (UNIQUE)

2. 인덱스 생성

  1  create index BGA_B211MK_idx1 on BGA_B211MK (GUARANTEENO)
  2* tablespace ts_b2b_d
SQL> /

  1  create index BGA_B213MK_idx1 on BGA_B213MK (GUARANTEENO)
  2* tablespace ts_b2b_d
SQL> /

  1  create index BGA_B215MK_idx1 on BGA_B215MK (GUARANTEENO)
  2* tablespace ts_b2b_d
SQL> /

3. 인덱스 생성후 트레이스

===========================================================================
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.05       0.04          0       9130          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.08       0.07          0       9130          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 34  (STL1B2B)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (AGGREGATE)
      0    MERGE JOIN
      0     SORT (JOIN)
      0      NESTED LOOPS
      0       NESTED LOOPS
      0        VIEW
      0         SORT (UNIQUE)
      0          UNION-ALL
      0           NESTED LOOPS
      0            TABLE ACCESS (BY INDEX ROWID) OF 'BGA_B211MK'
      0             INDEX (RANGE SCAN) OF 'BGA_B211MK_IDX1'
                        (NON-UNIQUE)
      0            TABLE ACCESS (BY INDEX ROWID) OF
                       'BGA_CMN_PRE_SENT'
      0             INDEX (UNIQUE SCAN) OF 'PK_BGA_CMN_PRE_SENT'
                        (UNIQUE)
      0           NESTED LOOPS
      0            TABLE ACCESS (BY INDEX ROWID) OF 'BGA_B213MK'
      0             INDEX (RANGE SCAN) OF 'BGA_B213MK_IDX1'
                        (NON-UNIQUE)
      0            TABLE ACCESS (BY INDEX ROWID) OF
                       'BGA_CMN_PRE_SENT'
      0             INDEX (UNIQUE SCAN) OF 'PK_BGA_CMN_PRE_SENT'
                        (UNIQUE)
      0        TABLE ACCESS (BY INDEX ROWID) OF 'BGA_B215MK'
      0         INDEX (RANGE SCAN) OF 'BGA_B215MK_IDX1' (NON-UNIQUE)

      0       TABLE ACCESS (BY INDEX ROWID) OF 'BGA_CMN_PRE_SENT'
      0        INDEX (UNIQUE SCAN) OF 'PK_BGA_CMN_PRE_SENT' (UNIQUE)
      0     SORT (JOIN)
      0      VIEW
      0       SORT (GROUP BY)
      0        VIEW
      0         SORT (UNIQUE)
      0          UNION-ALL
      0           NESTED LOOPS
      0            TABLE ACCESS (BY INDEX ROWID) OF 'BGA_B211MK'
      0             INDEX (RANGE SCAN) OF 'BGA_B211MK_IDX1'
                        (NON-UNIQUE)
      0            TABLE ACCESS (BY INDEX ROWID) OF
                       'BGA_CMN_PRE_SENT'
      0             INDEX (UNIQUE SCAN) OF 'PK_BGA_CMN_PRE_SENT'
                        (UNIQUE)
      0           NESTED LOOPS
      0            TABLE ACCESS (BY INDEX ROWID) OF 'BGA_B213MK'
      0             INDEX (RANGE SCAN) OF 'BGA_B213MK_IDX1'
                        (NON-UNIQUE)
      0            TABLE ACCESS (BY INDEX ROWID) OF
                       'BGA_CMN_PRE_SENT'
      0             INDEX (UNIQUE SCAN) OF 'PK_BGA_CMN_PRE_SENT'
                        (UNIQUE)