{"id":475,"date":"2015-09-08T07:46:56","date_gmt":"2015-09-08T07:46:56","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=475"},"modified":"2015-09-08T07:46:56","modified_gmt":"2015-09-08T07:46:56","slug":"sql-%ed%8a%9c%eb%8b%9d-%ec%82%ac%eb%a1%80","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=475","title":{"rendered":"SQL \ud29c\ub2dd \uc0ac\ub840"},"content":{"rendered":"

\n <\/p>\n

SQL Tuning CASE<\/strong><\/span>
\n\t\t<\/span><\/p>\n

\n <\/p>\n

[ CASE1: \uc801\uc808\ud55c \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9 ]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

UPDATE TB_CCPIDSCNT
\n<\/span><\/p>\n

SET END_DATE = <\/span>DECODE (SIGN (( NVL (END_DATE<\/span>,TO_CHAR (( ADD_MONTHS (TO_DATE (START_DATE<\/span>,’YYYYMMDD’)<\/span>,36)<\/span>– 1)<\/span>,’YYYYMMDD’))<\/span>– :b0))<\/span>,1, :b0<\/span>,END_DATE)<\/span><\/p>\n

,END_ORD_NO = :b2
\n<\/span><\/p>\n

WHERE NODE_TYPE = ‘S’
\n<\/span><\/p>\n

AND NODE_ID = :b3
\n<\/span><\/p>\n

AND DC_PLAN_ID IN (‘0680’, ‘0964’)
\n<\/span><\/p>\n

AND END_ORD_NO IS NULL
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

UPDATE STATEMENT Optimizer Mode=RULE                             UPDATE    TB_CCPIDSCNT
\n<\/span><\/p>\n


\n\t\t\t\tCONCATENATION <\/strong><\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CCPIDSCNT
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPIDSCNT_01 <\/strong><\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CCPIDSCNT
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPIDSCNT_01<\/strong><\/span>
\n\t\t\t<\/span><\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

TB_CCPIDSCNT \ud14c\uc774\ube14 \uc778\ub371\uc2a4 \uc815\ubcf4
\n<\/span><\/p>\n

\n\n\n\n<\/colgroup>\n\n\n\n\n
\n

PK_CCPIDSCNT<\/span><\/p>\n<\/td>\n

\n

NODE_TYPE, NODE_ID, DC_PLAN_ID, START_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPIDSCNT_01<\/span><\/p>\n<\/td>\n

\n

DC_PLAN_ID<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPIDSCNT_02<\/span><\/p>\n<\/td>\n

\n

CUST_NO<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

1. \ud604\uc7ac TX_CCPIDSCNT_01 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uace0 \uc788\uc73c\ubbc0\ub85c DC_PLAN_ID \uceec\ub7fc \ud558\ub098\ub9cc \ucc98\ub9ac\ubc94\uc704\ub97c \uc904\uc774\ub294\ub370 \uc0ac\uc6a9\ub418\uace0 \uc788\uc74c. \uadf8\ub7ec\ub098, WHERE \uc870\uac74\uc5d0 \uc0ac\uc6a9\ub41c NODE_TYPE, NODE_ID, DC_PLAN_ID \uceec\ub7fc\uc740 \uc55e\uc758 \ub450\uac1c\uac00 EQUAL \uc870\uac74\uc774\uace0 \uc138\ubc88\uc9f8 \uceec\ub7fc\uc774 IN \uc870\uac74\uc774\ubbc0\ub85c PK_CCPIDSCNT \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \ud6e8\uc52c \ud6a8\uc728\uc801<\/span>\uc784.
\n<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

UPDATE \/*+ INDEX(TB_CCPIDSCNT PK_CCPIDSCNT) *\/
\n<\/strong><\/span><\/span><\/p>\n

TB_CCPIDSCNT
\n<\/span><\/p>\n

SET END_DATE =
\n<\/span><\/p>\n

DECODE (SIGN (( NVL (END_DATE
\n<\/span><\/p>\n

,TO_CHAR (( ADD_MONTHS (TO_DATE (START_DATE
\n<\/span><\/p>\n

,’YYYYMMDD’)
\n<\/span><\/p>\n

,36)
\n<\/span><\/p>\n

– 1)
\n<\/span><\/p>\n

,’YYYYMMDD’))
\n<\/span><\/p>\n

– :b0))
\n<\/span><\/p>\n

,1, :b0
\n<\/span><\/p>\n

,END_DATE)
\n<\/span><\/p>\n

,END_ORD_NO = :b2
\n<\/span><\/p>\n

WHERE NODE_TYPE = ‘S’
\n<\/span><\/p>\n

AND NODE_ID = :b3
\n<\/span><\/p>\n

AND DC_PLAN_ID IN (‘0680’, ‘0964’)
\n<\/span><\/p>\n

AND END_ORD_NO IS NULL
\n<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815 Execution Plan>
\n<\/span><\/p>\n

UPDATE STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

UPDATE    TB_CCPIDSCNT
\n<\/span><\/p>\n


\n\t\t\tINLIST ITERATOR CONCATENATED
\n<\/strong><\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CCPIDSCNT
\n<\/span><\/p>\n


\n\t\t\tINDEX RANGE SCAN    PK_CCPIDSCNT
\n<\/strong><\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778>
\n<\/em><\/strong><\/span><\/p>\n

\uc870\uac74\uc5d0 \uc0ac\uc6a9\ub418\ub294 \uceec\ub7fc\uc744 \ucd5c\ub300\ud55c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub294 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uace0 \uc788\ub294\uc9c0 \ud655\uc778\ud558\uace0, \uadf8\ub807\uc9c0 \uc54a\uc744 \uacbd\uc6b0 \uc6d0\ud558\ub294 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub3c4\ub85d HINT \ub97c \uc0ac\uc6a9\ud55c\ub2e4
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

[ CASE2: INDEX SCAN \uc744 \ud1b5\ud55c TABLE ACCESS \uac00 \ubd88\ub9ac\ud55c \uacbd\uc6b0]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

SELECT TRAN_NO
\n<\/span><\/p>\n

…<\/font><\/p>\n

,TO_CHAR (REF_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,TO_CHAR (CHG_SCHE_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,ACC_TELCODE
\n<\/span><\/p>\n

,REMK
\n<\/span><\/p>\n

,TO_CHAR (REG_DATE, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

FROM TB_CWKONCALL
\n<\/span><\/p>\n

WHERE ((TRAN_TYPE > ‘0’
\n<\/span><\/p>\n

AND PROCESS_FLAG IS NULL)
\n<\/span><\/p>\n

AND TRAN_NO LIKE ‘K%’)
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n


\n\t\t\tTABLE ACCESS BY INDEX ROWID    TB_CWKONCALL
\n<\/strong><\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    PK_CWKONCALL<\/strong><\/span>
\n\t\t\t<\/span><\/span><\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

0 recursive calls
\n<\/span><\/p>\n

0 db block gets
\n<\/span><\/p>\n

40731 consistent gets
\n<\/strong><\/span><\/p>\n

6312 physical reads
\n<\/span><\/p>\n

0 redo size
\n<\/span><\/p>\n

3016 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

1373 bytes received via SQL*Net from client
\n<\/span><\/p>\n

2 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

0 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

0 rows processed
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

TB_CWKONCALL \ud14c\uc774\ube14 \uc778\ub371\uc2a4\uc815\ubcf4
\n<\/span><\/p>\n

\n\n\n\n<\/colgroup>\n\n\n
\n

PK_CWKONCALL<\/span><\/p>\n<\/td>\n

\n

TRAN_TYPE, NAT_ID, LINE_CHG_SEQ, TRAN_NO<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

1. [TRAN_TYPE > ‘0’ ] \uc870\uac74\uc5d0 \uc758\ud574 PK_CWKONCALL \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uace0 \uc788\ub294\ub370, ‘0’ \ubcf4\ub2e4 \ud070 \uac12\uc740 \uc804\uccb4\ub97c \uc758\ubbf8\ud558\ubbc0\ub85c \uacb0\uad6d \uc804\uccb4 \ud14c\uc774\ube14\uc744 INDEX SCAN -> \ud14c\uc774\ube14 ACCESS \ud558\uace0 \ub098\uba38\uc9c0 \uc870\uac74\uc744 \uccb4\ud06c\ud558\uac8c \ub418\ubbc0\ub85c TABLE FULL SCAN \ubcf4\ub2e4 \ud6e8\uc52c \ube44\ud6a8\uc728\uc801\uc784. \ub530\ub77c\uc11c FULL SCAN \uc744 \ud0c0\ub3c4\ub85d HINT \ub97c \uc0ac\uc6a9<\/span>\ud55c\ub2e4.
\n<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

SELECT \/*+ FULL(TB_CWKONCALL) *\/<\/strong><\/span>
\n\t\t<\/span><\/p>\n

TRAN_NO
\n<\/span><\/p>\n

…<\/font><\/p>\n

,TO_CHAR (REQ_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,TO_CHAR (ACCEPT_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,TO_CHAR (HOPE_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,TO_CHAR (CHG_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,PAY_OFF_CODE
\n<\/span><\/p>\n

,PAY_OFF_NAME
\n<\/span><\/p>\n

,PAYER
\n<\/span><\/p>\n

,PAY_ADDR
\n<\/span><\/p>\n

,PAY_ZIP
\n<\/span><\/p>\n

,RENT_1
\n<\/span><\/p>\n

,RENT_2
\n<\/span><\/p>\n

,RENT_3
\n<\/span><\/p>\n

,RENT_4
\n<\/span><\/p>\n

,VERI_ST
\n<\/span><\/p>\n

,ERROR_CD
\n<\/span><\/p>\n

,REF_YN
\n<\/span><\/p>\n

,TO_CHAR (REF_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,TO_CHAR (CHG_SCHE_DT, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

,ACC_TELCODE
\n<\/span><\/p>\n

,REMK
\n<\/span><\/p>\n

,TO_CHAR (REG_DATE, ‘YYYY\/MM\/DDHH24:MI:SS’)
\n<\/span><\/p>\n

FROM TB_CWKONCALL
\n<\/span><\/p>\n

WHERE ((TRAN_TYPE > ‘0’
\n<\/span><\/p>\n

AND PROCESS_FLAG IS NULL)
\n<\/span><\/p>\n

AND TRAN_NO LIKE ‘K%’)
\n\t\t\t<\/strong><\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815 Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

TABLE ACCESS FULL    TB_CWKONCALL
\n<\/strong><\/span><\/p>\n

\n <\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

38 recursive calls
\n<\/span><\/p>\n

3 db block gets
\n<\/span><\/p>\n

7681 consistent gets<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

2 physical reads
\n<\/span><\/p>\n

0 redo size
\n<\/span><\/p>\n

3016 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

1413 bytes received via SQL*Net from client
\n<\/span><\/p>\n

2 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

0 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

0 rows processed
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778>
\n<\/em><\/strong><\/span><\/p>\n

\uc778\ub371\uc2a4 SCAN \uc744 \ud1b5\ud55c TABLE ACCESS \ub294 INDEX ACCESS + RANDOM TABLE ACCESS \ub97c \uc218\ud589\ud558\uac8c \ub418\ubbc0\ub85c \ub9e4\uc6b0 \ube44\uc6a9\uc774 \ub9ce\uc774\ub4dc\ub294 ACCESS PATH \uc784. \ub530\ub77c\uc11c, \uc870\uac74\uc5d0 \uc758\ud574\uc11c INDEX SCAN \uc758 \ubc94\uc704\ub97c \ub9ce\uc774 \uc904\uc77c \uc218 \uc788\uc744 \uacbd\uc6b0\uc5d0 \uc0ac\uc6a9\ud588\uc744 \ub54c\ub9cc \uc18d\ub3c4\ud5a5\uc0c1\uc744 \uac00\uc838\uc62c \uc218 \uc788\uace0, \uadf8\ub807\uc9c0 \uc54a\uc744 \uacbd\uc6b0\uc5d0\ub294 TABLE FULL SCAN \uc774 \ubcf4\ub2e4 \ud6a8\uc728\uc801\uc77c \uc218\ub3c4 \uc788\uc74c.
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

[ CASE3: \ubd88\ud544\uc694\ud55c ACCESS \uac00 \uc77c\uc5b4\ub098\uc9c0 \uc54a\ub3c4\ub85d \uc8fc\uc758 ]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

SELECT 1
\n<\/strong><\/span><\/p>\n

FROM DUAL
\n<\/strong><\/span><\/p>\n

WHERE EXISTS (
\n<\/strong><\/span><\/p>\n

SELECT ‘X’
\n<\/span><\/p>\n

FROM (SELECT TABLE_ID
\n<\/span><\/p>\n

,WO_NO
\n<\/span><\/p>\n

,TO_CHAR (MAX (REG_DATE), ‘YYYYMMDDHH24MISS’)
\n<\/span><\/p>\n

REG_DATE
\n<\/span><\/p>\n

,ORD_NO
\n<\/span><\/p>\n

FROM VI_CWKOWO
\n<\/span><\/p>\n

WHERE (((WO_RSLT_FLAG = ‘Y’
\n<\/span><\/p>\n

AND WO_RSLT_CHK_MODE = ‘A’)
\n<\/span><\/p>\n

AND WO_RSLT_APPLY_DATE IS NULL)
\n<\/span><\/p>\n

AND TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’))
\n<\/span><\/p>\n

GROUP BY TABLE_ID
\n<\/span><\/p>\n

,WO_NO
\n<\/span><\/p>\n

,REG_DATE
\n<\/span><\/p>\n

,ORD_NO) A
\n<\/span><\/p>\n

,TB_CORDORDINFO B
\n<\/span><\/p>\n

,TB_CSYSORDTYPE C
\n<\/span><\/p>\n

WHERE ((B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)
\n<\/span><\/p>\n

AND C.SA_CD(+) = B.SA_CD)
\n<\/span><\/p>\n

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD))
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS FULL    DUAL
\n<\/strong><\/span><\/p>\n

NESTED LOOPS OUTER
\n<\/span><\/p>\n

NESTED LOOPS
\n<\/span><\/p>\n

VIEW
\n<\/span><\/p>\n

SORT GROUP BY
\n<\/span><\/p>\n

VIEW    VI_CWKOWO
\n<\/span><\/p>\n

UNION-ALL
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODELMONS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODELMONS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTSSA
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTSSA_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080FP
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080FP_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080DN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080DN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO700
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO700_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODVPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODVPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDSKE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDSKE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDC
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDC_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTHIRD
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTHIRD_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPV
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPV_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOVOT
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOVOT_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPSDNS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPSDNS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOLANS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOLANS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO060TS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO060TS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOATMS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOATMS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS FULL    TB_CWKOSNPLUS
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOHOMEPAGE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOHOMEPAGE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDAIN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDAIN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS FULL    TB_CWKOSNMENU
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOENGCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOENGCALL_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOPCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOPCALL_01
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CSYSORDTYPE
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

\ud14c\uc774\ube14 \uc778\ub371\uc2a4 \uc815\ubcf4
\n<\/span><\/p>\n

\n <\/p>\n

1. INLINE VIEW \ub97c \uc0ac\uc6a9\ud558\uace0 \ub9e8 \ubc14\uae65\ucabd\uc5d0\uc11c FROM DUAL WHERE EXISTS \ubb38\uc7a5\uc744 \uc0ac\uc6a9\ud558\uace0 \uc788\ub294\ub370 ROWNUM = 1 \uc870\uac74\uc744 \uc774\uc6a9\ud558\uba74 DUAL \ud14c\uc774\ube14\uc740 \uc0ac\uc6a9\ud560 \ud544\uc694 \uc5c6\uc74c
\n<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

SELECT 1
\n<\/span><\/p>\n

FROM (SELECT TABLE_ID
\n<\/span><\/p>\n

,WO_NO
\n<\/span><\/p>\n

,TO_CHAR (MAX (REG_DATE), ‘YYYYMMDDHH24MISS’) REG_DATE
\n<\/span><\/p>\n

,ORD_NO
\n<\/span><\/p>\n

FROM VI_CWKOWO
\n<\/span><\/p>\n

WHERE (((WO_RSLT_FLAG = ‘Y’
\n<\/span><\/p>\n

AND WO_RSLT_CHK_MODE = ‘A’)
\n<\/span><\/p>\n

AND WO_RSLT_APPLY_DATE IS NULL)
\n<\/span><\/p>\n

AND TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’))
\n<\/span><\/p>\n

GROUP BY TABLE_ID
\n<\/span><\/p>\n

,WO_NO
\n<\/span><\/p>\n

,REG_DATE
\n<\/span><\/p>\n

,ORD_NO) A
\n<\/span><\/p>\n

,TB_CORDORDINFO B
\n<\/span><\/p>\n

,TB_CSYSORDTYPE C
\n<\/span><\/p>\n

WHERE ((B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)
\n<\/span><\/p>\n

AND C.SA_CD(+) = B.SA_CD)
\n<\/span><\/p>\n

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD)
\n<\/span><\/p>\n

AND ROWNUM = 1<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815 Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

COUNT STOPKEY
\n<\/strong><\/span><\/p>\n

NESTED LOOPS OUTER
\n<\/span><\/p>\n

NESTED LOOPS
\n<\/span><\/p>\n

VIEW
\n<\/span><\/p>\n

SORT GROUP BY
\n<\/span><\/p>\n

VIEW    VI_CWKOWO
\n<\/span><\/p>\n

UNION-ALL
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODELMONS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODELMONS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTSSA
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTSSA_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080FP
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080FP_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080DN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080DN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO700
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO700_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODVPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODVPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDSKE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDSKE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDC
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDC_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTHIRD
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTHIRD_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPV
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPV_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOVOT
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOVOT_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPSDNS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPSDNS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOLANS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOLANS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO060TS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO060TS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOATMS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOATMS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSNPLUS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSNPLUS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOHOMEPAGE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOHOMEPAGE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDAIN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDAIN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSNMENU
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSNMENU_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOENGCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOENGCALL_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOPCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOPCALL_01
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CSYSORDTYPE
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778>
\n<\/em><\/strong><\/span><\/p>\n

\ubd88\ud544\uc694\ud55c TABLE ACCESS \ub294 \uac00\ub2a5\ud558\uba74 \uc77c\uc5b4\ub098\uc9c0 \uc54a\ub3c4\ub85d \uc8fc\uc758\ud574\uc57c \ud55c\ub2e4
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

\n <\/p>\n

[ CASE4: \ubd88\ud544\uc694\ud55c \uc791\uc5c5\uc774 \uc77c\uc5b4\ub098\uc9c0 \uc54a\ub3c4\ub85d \uc8fc\uc758 ]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

SELECT 1
\n<\/span><\/p>\n

FROM (SELECT TABLE_ID
\n<\/span><\/p>\n

,WO_NO
\n<\/span><\/p>\n

,TO_CHAR (MAX<\/strong><\/span> (REG_DATE), ‘YYYYMMDDHH24MISS’) REG_DATE
\n<\/span><\/p>\n

,ORD_NO
\n<\/span><\/p>\n

FROM VI_CWKOWO
\n<\/span><\/p>\n

WHERE (((WO_RSLT_FLAG = ‘Y’
\n<\/span><\/p>\n

AND WO_RSLT_CHK_MODE = ‘A’)
\n<\/span><\/p>\n

AND WO_RSLT_APPLY_DATE IS NULL)
\n<\/span><\/p>\n

AND TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’))
\n<\/span><\/p>\n

GROUP BY TABLE_ID
\n<\/strong><\/span><\/p>\n

,WO_NO
\n<\/strong><\/span><\/p>\n

,REG_DATE
\n<\/strong><\/span><\/p>\n

,ORD_NO<\/strong><\/span>) A
\n<\/span><\/p>\n

,TB_CORDORDINFO B
\n<\/span><\/p>\n

,TB_CSYSORDTYPE C
\n<\/span><\/p>\n

WHERE ((B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)
\n<\/span><\/p>\n

AND C.SA_CD(+) = B.SA_CD)
\n<\/span><\/p>\n

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD)
\n<\/span><\/p>\n

AND ROWNUM = 1
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

COUNT STOPKEY
\n<\/span><\/p>\n

NESTED LOOPS OUTER
\n<\/span><\/p>\n

NESTED LOOPS
\n<\/span><\/p>\n

VIEW
\n<\/span><\/p>\n

SORT GROUP BY
\n<\/strong><\/span><\/p>\n

VIEW    VI_CWKOWO
\n<\/span><\/p>\n

UNION-ALL
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODELMONS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODELMONS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTSSA
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTSSA_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080FP
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080FP_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080DN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080DN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO700
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO700_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODVPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODVPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDSKE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDSKE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDC
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDC_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTHIRD
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTHIRD_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPV
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPV_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOVOT
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOVOT_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPSDNS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPSDNS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOLANS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOLANS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO060TS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO060TS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOATMS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOATMS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSNPLUS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSNPLUS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOHOMEPAGE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOHOMEPAGE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDAIN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDAIN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSNMENU
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSNMENU_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOENGCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOENGCALL_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOPCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOPCALL_01
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CSYSORDTYPE
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

\n <\/p>\n

1. \uc774 SQL \ubb38\uc7a5\uc740 \ud2b9\uc815 \uc870\uac74\uc5d0 \ud574\ub2f9\ud558\ub294 \ub370\uc774\ud0c0\uac00 \uc874\uc7ac\ud558\ub294\uc9c0 \uccb4\ud06c\ud558\uae30 \uc704\ud55c \uac83\uc774\ubbc0\ub85c GROUP BY \uc808\uc774 \ud544\uc694\uc5c6\uc74c
\n<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

SELECT 1
\n<\/span><\/p>\n

FROM VI_CWKOWO A
\n<\/span><\/p>\n

,TB_CORDORDINFO B
\n<\/span><\/p>\n

,TB_CSYSORDTYPE C
\n<\/span><\/p>\n

WHERE A.WO_RSLT_FLAG = ‘Y’
\n<\/span><\/p>\n

AND A.WO_RSLT_CHK_MODE = ‘A’
\n<\/span><\/p>\n

AND A.WO_RSLT_APPLY_DATE IS NULL
\n<\/span><\/p>\n

AND A.TABLE_ID IN (‘TB_CWKO060TS’, ‘TB_CWKO700’)
\n<\/span><\/p>\n

AND B.ORD_NO = NVL (A.ORD_NO, A.WO_NO)
\n<\/span><\/p>\n

AND C.SA_CD(+) = B.SA_CD
\n<\/span><\/p>\n

AND C.ORD_TYPE_CD(+) = B.ORD_TYPE_CD
\n<\/span><\/p>\n

AND ROWNUM = 1
\n\t\t\t<\/strong><\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815 Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

COUNT STOPKEY
\n<\/span><\/p>\n

NESTED LOOPS OUTER
\n<\/span><\/p>\n

NESTED LOOPS
\n<\/span><\/p>\n

VIEW    VI_CWKOWO
\n<\/span><\/p>\n

UNION-ALL
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODELMONS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODELMONS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTSSA
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTSSA_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080FP
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080FP_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO080DN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO080DN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO700
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO700_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKODVPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKODVPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDSKE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDSKE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDC
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDC_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOTHIRD
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOTHIRD_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPV
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPV_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOVOT
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOVOT_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOPSDNS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOPSDNS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOLANS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOLANS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKO060TS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKO060TS_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOATMS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOATMS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSNPLUS
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSNPLUS_02
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOHOMEPAGE
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOHOMEPAGE_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCARDAIN
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCARDAIN_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOSNMENU
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOSNMENU_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOENGCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOENGCALL_01
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CWKOCOPCALL
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CWKOCOPCALL_01
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CORDORDINFO
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_CSYSORDTYPE
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778>
\n<\/em><\/strong><\/span><\/p>\n

\uc774 \uacbd\uc6b0\uc5d0\ub294 \ubd88\ud544\uc694\ud55c GROUP BY \uc808\uc5d0 \uc758\ud574 \ub0b4\ubd80\uc801\uc73c\ub85c SORT \uc791\uc5c5\uc774 \ubc1c\uc0dd\ud558\uac8c \ub418\uc5b4 \ubd80\ubd84\ubc94\uc704\ucc98\ub9ac\uac00 \uac00\ub2a5\ud568\uc5d0\ub3c4 \ubd88\uad6c\ud558\uace0 \ubd80\ubd84\ubc94\uc704\ucc98\ub9ac\uac00 \ubd88\uac00\ub2a5\ud558\uac8c \ub418\uace0, \ub370\uc774\ud130 \ucc98\ub9ac\ub7c9\ub3c4 \uc99d\uac00\ud558\uac8c \ub418\uc5c8\uc74c. \uac19\uc740 \uacb0\uacfc\ub97c \uc5bb\uc744 \uc218 \uc788\ub2e4\uba74 \ubd88\ud544\uc694\ud55c SORT \uc791\uc5c5\ub4f1\uc774 \uc77c\uc5b4\ub098\uc9c0 \uc54a\ub3c4\ub85d \uc8fc\uc758\ud558\uace0 \uceec\ub7fc \ub370\uc774\ud0c0\uc5d0 \ub530\ub77c\uc11c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uc544\ub3c4 \ub418\ub294 \ud568\uc218(\uc608: NVL) \ub4f1\uc740 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub3c4\ub85d \ud55c\ub2e4
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

\n <\/p>\n

[ CASE5: \ub4dc\ub77c\uc774\ube59 \ud14c\uc774\ube14 \uc120\uc815 \/ \ub3d9\uc77c\ub370\uc774\ud0c0\uc758 \ubc18\ubcf5 ACCESS \ud68c\ud53c ]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

SELECT LIST_FLAG
\n<\/span><\/p>\n

,LIST_CNT
\n<\/span><\/p>\n

FROM (SELECT \/*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*\/
\n<\/span><\/p>\n

‘1’ LIST_FLAG
\n<\/span><\/p>\n

,COUNT (DISTINCT B.SA_ID) LIST_CNT
\n<\/span><\/p>\n

FROM TB_CCPI115A A
\n<\/span><\/p>\n

,TB_CCPI115 B
\n<\/span><\/p>\n

WHERE ((((A.BUSI_CD = :b2
\n<\/span><\/p>\n

AND A.TOP_SA_ID = B.SA_ID)
\n<\/span><\/p>\n

AND A.START_ORD_NO = B.START_ORD_NO)
\n<\/span><\/p>\n

AND B.STATUS_CD IN
\n<\/span><\/p>\n

(’02’, ’03’, ’04’, ’06’, ’07’, ’08’, ’09’, ’13’, ’15’))
\n<\/span><\/p>\n

AND B.DELIVER_HOPE_DATE < TO_CHAR (SYSDATE, ‘YYYYMMDD’))
\n<\/span><\/p>\n

UNION ALL
\n<\/span><\/p>\n

SELECT \/*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*\/
\n<\/span><\/p>\n

‘2’ LIST_FLAG
\n<\/span><\/p>\n

,COUNT (DISTINCT B.SA_ID) LIST_CNT
\n<\/span><\/p>\n

FROM TB_CCPI115A A
\n<\/span><\/p>\n

,TB_CCPI115 B
\n<\/span><\/p>\n

WHERE ((((A.BUSI_CD = :b2
\n<\/span><\/p>\n

AND A.TOP_SA_ID = B.SA_ID)
\n<\/span><\/p>\n

AND A.START_ORD_NO = B.START_ORD_NO)
\n<\/span><\/p>\n

AND B.STATUS_CD NOT IN (’01’, ’12’))
\n<\/span><\/p>\n

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’))
\n<\/span><\/p>\n

UNION ALL
\n<\/span><\/p>\n

SELECT \/*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*\/
\n<\/span><\/p>\n

‘3’ LIST_FLAG
\n<\/span><\/p>\n

,COUNT (DISTINCT B.SA_ID) LIST_CNT
\n<\/span><\/p>\n

FROM TB_CCPI115A A
\n<\/span><\/p>\n

,TB_CCPI115 B
\n<\/span><\/p>\n

WHERE ((((A.BUSI_CD = :b2
\n<\/span><\/p>\n

AND A.TOP_SA_ID = B.SA_ID)
\n<\/span><\/p>\n

AND A.START_ORD_NO = B.START_ORD_NO)
\n<\/span><\/p>\n

AND B.STATUS_CD IN (’10’, ’11’, ’16’))
\n<\/span><\/p>\n

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’))
\n<\/span><\/p>\n

UNION ALL
\n<\/span><\/p>\n

SELECT \/*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*\/
\n<\/span><\/p>\n

‘4’ LIST_FLAG
\n<\/span><\/p>\n

,COUNT (DISTINCT B.SA_ID) LIST_CNT
\n<\/span><\/p>\n

FROM TB_CCPI115A A
\n<\/span><\/p>\n

,TB_CCPI115 B
\n<\/span><\/p>\n

WHERE ((((A.BUSI_CD = :b2
\n<\/span><\/p>\n

AND A.TOP_SA_ID = B.SA_ID)
\n<\/span><\/p>\n

AND A.START_ORD_NO = B.START_ORD_NO)
\n<\/span><\/p>\n

AND B.STATUS_CD IN
\n<\/span><\/p>\n

(’02’, ’03’, ’04’, ’06’, ’07’, ’08’, ’09’, ’13’, ’15’))
\n<\/span><\/p>\n

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’)))
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE        4          15020                    
\n<\/span><\/p>\n

VIEW        4     60     15020                    
\n<\/span><\/p>\n


\n\t\t\t\tUNION-ALL<\/strong><\/span>                                      
\n<\/strong><\/span><\/p>\n

SORT GROUP BY        1     28 K                        
\n<\/span><\/p>\n

NESTED LOOPS        556     28 K    3755                    
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115A    750     14 K    5                1    3
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPI115A_02<\/strong><\/span>    750          4                1    3
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115    7 K    231 K    5                1    3
\n<\/span><\/p>\n

INDEX RANGE SCAN    PK_CCPI115    7 K         4                1    3
\n<\/span><\/p>\n

SORT GROUP BY        1     1 K                        
\n<\/span><\/p>\n

NESTED LOOPS        28     1 K    3755                    
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115A    750     14 K    5                1    3
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPI115A_02<\/strong><\/span>    750          4                1    3
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115    371     11 K    5                1    3
\n<\/span><\/p>\n

INDEX RANGE SCAN    PK_CCPI115    371          4                1    3
\n<\/span><\/p>\n

SORT GROUP BY        1     5 K                        
\n<\/span><\/p>\n

NESTED LOOPS        111     5 K    3755                    
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115A    750     14 K    5                1    3
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPI115A_02<\/strong><\/span>    750          4                1    3
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115    1 K    46 K    5                1    3
\n<\/span><\/p>\n

INDEX RANGE SCAN    PK_CCPI115    1 K         4                1    3
\n<\/span><\/p>\n

SORT GROUP BY        1     5 K                        
\n<\/span><\/p>\n

NESTED LOOPS        111     5 K    3755                    
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115A    750     14 K    5                1    3
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPI115A_02<\/strong><\/span>    750          4                1    3
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115    1 K    46 K    5                1    3
\n<\/span><\/p>\n

INDEX RANGE SCAN    PK_CCPI115    1 K         4                1    3
\n<\/span><\/p>\n

\n <\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

80 recursive calls
\n<\/span><\/p>\n

0 db block gets
\n<\/span><\/p>\n

253749 consistent gets
\n<\/strong><\/span><\/p>\n

2407 physical reads
\n<\/span><\/p>\n

0 redo size
\n<\/span><\/p>\n

336 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

2308 bytes received via SQL*Net from client
\n<\/span><\/p>\n

3 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

4 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

4 rows processed
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

TB_CCPI115 \ud14c\uc774\ube14 \uc778\ub371\uc2a4\uc815\ubcf4
\n<\/span><\/p>\n

\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

PK_CCPI115<\/span><\/p>\n<\/td>\n

\n

SA_ID, START_ORD_NO, START_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_04<\/span><\/p>\n<\/td>\n

\n

CHAKSIN_OFC_CD, SA_ID<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_05<\/span><\/p>\n<\/td>\n

\n

RECEIPT_CD, STATUS_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_06<\/span><\/p>\n<\/td>\n

\n

PRINT_RESV_DATE, START_ORD_NO, STATUS_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_07<\/span><\/p>\n<\/td>\n

\n

CHAKSIN_OFC_CD, EQUIP_ID, PRINT_RESV_DATE, PRINT_SEQ<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_08<\/span><\/p>\n<\/td>\n

\n

CHAKSIN_OFC_CD, DELIVER_HOPE_DATE, START_ORD_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_09<\/span><\/p>\n<\/td>\n

\n

START_DATE, RECEIPT_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_10<\/span><\/p>\n<\/td>\n

\n

END_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_11<\/span><\/p>\n<\/td>\n

\n

DELIVER_HOPE_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_12<\/span><\/p>\n<\/td>\n

\n

FIRST_ORD_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_13<\/span><\/p>\n<\/td>\n

\n

STATUS_CD, CHAKSIN_OFC_CD, PRINT_FLAG, PRINT_RESV_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_14<\/span><\/p>\n<\/td>\n

\n

START_ORD_NO, START_DATE, STATUS_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_16<\/span><\/p>\n<\/td>\n

\n

REQER_WEB_ID, START_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

TB_CCPI115A \ud14c\uc774\ube14 \uc778\ub371\uc2a4\uc815\ubcf4
\n<\/span><\/p>\n

\n\n\n\n<\/colgroup>\n\n\n\n\n
\n

PK_CCPI115A<\/span><\/p>\n<\/td>\n

\n

SA_ID, START_ORD_NO, START_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115A_01<\/span><\/p>\n<\/td>\n

\n

TOP_SA_ID<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115A_02<\/span><\/p>\n<\/td>\n

\n

BUSI_CD<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

1. LIST_FLAG \uac12\uc774 ‘2’, ‘3’, ‘4’ \uc778 \uacbd\uc6b0 TB_CCPI115A \ud14c\uc774\ube14\uc758 TX_CCPI115A_02 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\uace0 \uc788\uc73c\ub098, \uc774 \uacbd\uc6b0\uc5d0\ub294 \uc778\ub371\uc2a4 \ud6a8\uc728\uc774 \ub5a8\uc5b4\uc9d0
\n<\/span><\/p>\n

DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’) \uc870\uac74\uc5d0\uc11c \uc2e4\uc81c \ucc98\ub9ac\ud558\ub294 \ub0a0\uc9dc\uac00 \ud558\ub8e8\uce58 \uc774\ubbc0\ub85c TB_CCPI115 \ud14c\uc774\ube14\uc744 \ub4dc\ub77c\uc774\ube59 \ud14c\uc774\ube14\ub85c \ud558\uace0
\n<\/span><\/p>\n

TX_CCPI115_11(TX_CCPI115_11) \uc778\ub371\uc2a4\ub97c \uc774\uc6a9\ud558\ub3c4\ub85d \ud55c\ub2e4
\n<\/span><\/p>\n

2. LIST_FLAG \uac12\uc774 ‘2’, ‘3’, ‘4’ \uc778 \uacbd\uc6b0 \uac19\uc740\ub370\uc774\ud0c0\ub97c \uc138\ubc88 \uc77d\uc5b4\uc11c UNION ALL \ucc98\ub9ac\ub97c \ud558\uace0 \uc788\uc74c
\n<\/span><\/p>\n

CARTESIAN PRODUCT \ub97c \uc774\uc6a9\ud558\uc5ec \ub370\uc774\ud130\ub97c \ud55c\ubc88 \uc77d\uace0 \ub09c\ud6c4 LIST_FLAG \uac12\uc774 ‘2’, ‘3’, ‘4’ \uc778 \uacbd\uc6b0\uc5d0 \ub530\ub77c \uacb0\uacfc\ub97c \ub2e4\ub974\uac8c \ucc98\ub9ac\ud558\uc5ec BLOCK ACCESS \ud69f\uc218\ub97c
\n<\/span><\/p>\n

\ub9ce\uc774 \uc904\uc77c \uc218 \uc788\uc74c
\n<\/span><\/p>\n

\uc2e4\uc81c \ub370\uc774\ud130 \uc870\ud68c\uc2dc \ub370\uc774\ud130 \ucc98\ub9ac\ub7c9\uc5d0\uc11c 3\ubc30 \uc774\uc0c1\uc758 \uac1c\uc120\uc774 \ub098\ud0c0\ub098\uace0 \uc788\uc74c<\/span>
\n\t\t<\/span><\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

SELECT \/*+ ORDERED USE_NL(A B) INDEX(A TX_CCPI115A_02) +*\/
\n<\/span><\/p>\n

‘1’ LIST_FLAG
\n<\/span><\/p>\n

,COUNT (DISTINCT B.SA_ID) LIST_CNT
\n<\/span><\/p>\n

FROM TB_CCPI115A A
\n<\/span><\/p>\n

,TB_CCPI115 B
\n<\/span><\/p>\n

WHERE ((((A.BUSI_CD = ‘C0078’
\n<\/span><\/p>\n

AND A.TOP_SA_ID = B.SA_ID)
\n<\/span><\/p>\n

AND A.START_ORD_NO = B.START_ORD_NO)
\n<\/span><\/p>\n

AND B.STATUS_CD IN
\n<\/span><\/p>\n

(’02’, ’03’, ’04’, ’06’, ’07’, ’08’, ’09’, ’13’, ’15’))
\n<\/span><\/p>\n

AND B.DELIVER_HOPE_DATE < TO_CHAR (SYSDATE, ‘YYYYMMDD’))
\n<\/span><\/p>\n

UNION ALL
\n<\/strong><\/span><\/p>\n

SELECT \/*+ LEADING(B) USE_HASH(B A) INDEX(A TX_CCPI115A_02)*\/
\n<\/strong><\/span><\/p>\n

C.LIST_FLAG
\n<\/strong><\/span><\/p>\n

,COUNT(DISTINCT DECODE(C.LIST_FLAG,
\n<\/strong><\/span><\/p>\n

‘2’, DECODE(SIGN(INSTR(’01’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’12’,B.STATUS_CD)), 0, B.SA_ID),
\n<\/strong><\/span><\/p>\n

‘3’, DECODE(SIGN(INSTR(’10’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’11’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’16’,B.STATUS_CD)), 1, B.SA_ID),
\n<\/strong><\/span><\/p>\n

‘4’, DECODE(SIGN(INSTR(’02’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’03’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’04’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’06’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’07’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’08’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’09’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’13’,B.STATUS_CD)) +
\n<\/strong><\/span><\/p>\n

SIGN(INSTR(’15’,B.STATUS_CD)), 1, B.SA_ID)
\n<\/strong><\/span><\/p>\n

,0) ) LIST_CNT
\n<\/strong><\/span><\/p>\n

FROM TB_CCPI115A A
\n<\/strong><\/span><\/p>\n

,TB_CCPI115 B
\n<\/strong><\/span><\/p>\n

,(SELECT ‘2’ LIST_FLAG FROM DUAL
\n<\/strong><\/span><\/p>\n

UNION ALL
\n<\/strong><\/span><\/p>\n

SELECT ‘3’ LIST_FLAG FROM DUAL
\n<\/strong><\/span><\/p>\n

UNION ALL
\n<\/strong><\/span><\/p>\n

SELECT ‘4’ LIST_FLAG FROM DUAL
\n<\/strong><\/span><\/p>\n

) C
\n<\/strong><\/span><\/p>\n

WHERE A.BUSI_CD = :b2
\n<\/strong><\/span><\/p>\n

AND A.TOP_SA_ID = B.SA_ID
\n<\/strong><\/span><\/p>\n

AND A.START_ORD_NO = B.START_ORD_NO
\n<\/strong><\/span><\/p>\n

AND B.DELIVER_HOPE_DATE = TO_CHAR (SYSDATE, ‘YYYYMMDD’)
\n<\/strong><\/span><\/p>\n

GROUP BY C.LIST_FLAG
\n<\/strong><\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815 Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE        67 M         9509331                    
\n<\/span><\/p>\n

UNION-ALL                                      
\n<\/span><\/p>\n

SORT GROUP BY        1     28 K                        
\n<\/span><\/p>\n

NESTED LOOPS        559     28 K    3775                    
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115A    754     14 K    5                1    3
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CCPI115A_02    754          4                1    3
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115    7 K    231 K    5                1    3
\n<\/span><\/p>\n

INDEX RANGE SCAN    PK_CCPI115    7 K         4                1    3
\n<\/span><\/p>\n

SORT GROUP BY        67 M    6G    9505556                    
\n<\/span><\/p>\n


\n\t\t\t\tHASH JOIN<\/strong><\/span>        67 M    6G    5120                    
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115    148 K    12 M    5                1    3
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPI115_11<\/strong><\/span>    148 K         4                1    3
\n<\/span><\/p>\n


\n\t\t\t\tMERGE JOIN CARTESIAN<\/strong><\/span>        4 M    112 M    2267                    
\n<\/span><\/p>\n

PARTITION CONCATENATED                                  1    3
\n<\/span><\/p>\n

TABLE ACCESS BY LOCAL INDEX ROWID    TB_CCPI115A    754     14 K    5                1    3
\n<\/span><\/p>\n


\n\t\t\t\tINDEX RANGE SCAN    TX_CCPI115A_02<\/strong><\/span>    754          4                1    3
\n<\/span><\/p>\n

SORT JOIN        6 K    35 K    2262                    
\n<\/span><\/p>\n

VIEW        6 K    35 K                        
\n<\/span><\/p>\n

UNION-ALL                                      
\n<\/span><\/p>\n

TABLE ACCESS FULL    DUAL    2 K         1                    
\n<\/span><\/p>\n

TABLE ACCESS FULL    DUAL    2 K         1                    
\n<\/span><\/p>\n

TABLE ACCESS FULL    DUAL    2 K         1                    
\n<\/span><\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

24 recursive calls
\n<\/span><\/p>\n

9 db block gets
\n<\/span><\/p>\n

77334 consistent gets
\n<\/strong><\/span><\/p>\n

1668 physical reads
\n<\/span><\/p>\n

0 redo size
\n<\/span><\/p>\n

336 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

2299 bytes received via SQL*Net from client
\n<\/span><\/p>\n

3 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

3 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

4 rows processed
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778 1>
\n<\/em><\/strong><\/span><\/p>\n

FROM \uc808\uc5d0 \ub450\uac1c \uc774\uc0c1\uc758 \ud14c\uc774\ube14\uc774 \uc0ac\uc6a9\ub420 \uacbd\uc6b0, HASH JOIN \uc774\ub098 SORT MERGE JOIN \uc774 \uc544\ub2cc NESTED LOOP \uc870\uc778\uc778 \uacbd\uc6b0\uc5d0\ub294 \uc0ac\uc6a9\ub41c \uc870\uac74\uc73c\ub85c \ubcf4\uc544 \ucc98\ub9ac\uac74\uc218\uac00 \uac00\uc7a5 \uc801\uc740 \ud14c\uc774\ube14\uc774 \uac00\uc7a5 DRIVING \ud14c\uc774\ube14\ub85c \uc0ac\uc6a9\ub418\uc5b4\uc57c \uc774\ud6c4\uc758 JOIN \uc791\uc5c5\uc758 \ud69f\uc218\ub97c \uc904\uc77c \uc218 \uc788\uc73c\ubbc0\ub85c \uac00\uc7a5 \ud6a8\uc728\uc801\uc784
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778 2>
\n<\/em><\/strong><\/span><\/p>\n

\uac19\uc740 \ub370\uc774\ud130\ub97c \ub450\ubc88\uc774\uc0c1 ACCESS \ud558\ub294 \uac83\uc740 DISK I\/O \ub610\ub294 Memory I\/O \ub97c \ubc1c\uc0dd\ud558\uac8c \ub418\ubbc0\ub85c \ubd88\uac00\ud53c\ud55c \uacbd\uc6b0\uac00 \uc544\ub2c8\uba74 \ucd5c\ub300\ud55c \ud53c\ud574\uc57c \ud568, \ud55c\ubc88\uc758 \ub370\uc774\ud130 ACCESS \ub97c \ud1b5\ud558\uc5ec \ud544\uc694\ud55c \ub370\uc774\ud130\ub97c \ucd94\ucd9c\ud560 \uc218 \uc788\ub3c4\ub85d SQL \uc791\uc131\uc5d0 \uc8fc\uc758\ub97c \uae30\uc6b8\uc5ec\uc57c \ud568
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

[ CASE6: CONCATENATION \uc758 \uc801\uc808\ud55c \uc0ac\uc6a9 ]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

SELECT COUNT (*)
\n<\/span><\/p>\n

FROM TB_CCPI115
\n<\/span><\/p>\n

WHERE (RECEIPT_CD IN (’05’, ’06’)
\n<\/span><\/p>\n

AND STATUS_CD = ’01’)
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

SORT AGGREGATE
\n<\/span><\/p>\n

PARTITION CONCATENATED
\n<\/span><\/p>\n


\n\t\t\tINDEX FAST FULL SCAN    TX_CCPI115_05<\/strong>
\n\t\t<\/span><\/p>\n

\n <\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

0 recursive calls
\n<\/span><\/p>\n

4 db block gets
\n<\/span><\/p>\n

25940 consistent gets
\n<\/strong><\/span><\/p>\n

25871 physical reads
\n<\/span><\/p>\n

200 redo size
\n<\/span><\/p>\n

229 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

120 bytes received via SQL*Net from client
\n<\/span><\/p>\n

2 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

0 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

1 rows processed<\/span>
\n\t\t<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

TB_CCPI115 \ud14c\uc774\ube14 \uc778\ub371\uc2a4\uc815\ubcf4
\n<\/span><\/p>\n

\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

PK_CCPI115<\/span><\/p>\n<\/td>\n

\n

SA_ID, START_ORD_NO, START_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_04<\/span><\/p>\n<\/td>\n

\n

CHAKSIN_OFC_CD, SA_ID<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_05<\/span><\/p>\n<\/td>\n

\n

RECEIPT_CD, STATUS_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_06<\/span><\/p>\n<\/td>\n

\n

PRINT_RESV_DATE, START_ORD_NO, STATUS_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_07<\/span><\/p>\n<\/td>\n

\n

CHAKSIN_OFC_CD, EQUIP_ID, PRINT_RESV_DATE, PRINT_SEQ<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_08<\/span><\/p>\n<\/td>\n

\n

CHAKSIN_OFC_CD, DELIVER_HOPE_DATE, START_ORD_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_09<\/span><\/p>\n<\/td>\n

\n

START_DATE, RECEIPT_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_10<\/span><\/p>\n<\/td>\n

\n

END_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_11<\/span><\/p>\n<\/td>\n

\n

DELIVER_HOPE_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_12<\/span><\/p>\n<\/td>\n

\n

FIRST_ORD_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_13<\/span><\/p>\n<\/td>\n

\n

STATUS_CD, CHAKSIN_OFC_CD, PRINT_FLAG, PRINT_RESV_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_14<\/span><\/p>\n<\/td>\n

\n

START_ORD_NO, START_DATE, STATUS_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CCPI115_16<\/span><\/p>\n<\/td>\n

\n

REQER_WEB_ID, START_DATE<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

1. TX_CCPI115_05 \uc778\ub371\uc2a4\ub97c FAST FULL SCAN \uc73c\ub85c \uc77d\uc5b4\uc11c \uc870\uac74\uc5d0 \ub9de\ub294 \uac74\uc218\ub9cc \uad6c\ud558\uace0 \uc788\ub294\ub370, \uc2e4\uc81c\ub85c \ud14c\uc774\ube14 \ucd1d\uac74\uc218\ub294 2\ubc31\ub9cc\uac74 \uc774\uc0c1\uc774\uace0, \uc870\uac74\uc5d0 \ub9de\ub294 \uac74\uc218\ub294 94 \uac74\uc5d0 \ubd88\uacfc\ud558\ubbc0\ub85c INDEX FULL SCAN \uc740 \ub9e4\uc6b0 \ube44\ud6a8\uc728\uc801\uc784
\n<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

SELECT \/*+ INDEX(TB_CCPI115 TX_CCPI115_05) USE_CONCAT *\/<\/strong><\/span>
\n\t\t<\/span><\/p>\n

COUNT (*)
\n<\/span><\/p>\n

FROM TB_CCPI115
\n<\/span><\/p>\n

WHERE (RECEIPT_CD IN (’05’, ’06’)
\n<\/span><\/p>\n

AND STATUS_CD = ’01’)
\n\t\t\t<\/strong><\/span><\/p>\n

\n <\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

SORT AGGREGATE
\n<\/span><\/p>\n


\n\t\t\tCONCATENATION
\n<\/strong><\/span><\/p>\n

PARTITION CONCATENATED
\n<\/span><\/p>\n


\n\t\t\tINDEX RANGE SCAN    TX_CCPI115_05<\/strong>
\n\t\t<\/span><\/p>\n

PARTITION CONCATENATED
\n<\/span><\/p>\n


\n\t\t\tINDEX RANGE SCAN    TX_CCPI115_05<\/strong>
\n\t\t<\/span><\/p>\n

\n <\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

56 recursive calls
\n<\/span><\/p>\n

0 db block gets
\n<\/span><\/p>\n

144 consistent gets
\n<\/strong><\/span><\/p>\n

1 physical reads
\n<\/span><\/p>\n

0 redo size
\n<\/span><\/p>\n

230 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

382 bytes received via SQL*Net from client
\n<\/span><\/p>\n

3 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

0 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

1 rows processed
\n\t\t\t<\/strong><\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778>
\n<\/em><\/strong><\/span><\/p>\n

\uc778\ub371\uc2a4 \uceec\ub7fc\uc911 \ud558\ub098\ub97c IN \uc5f0\uc0b0\uc790\ub098 OR \uc5f0\uc0b0\uc790\ub97c \uc0ac\uc6a9\ud558\ub294 \uc870\uac74\uc774 \uc788\uc744 \uacbd\uc6b0\uc5d0\ub294 CONCATENATION \uc774 \uc0ac\uc6a9\ub418\uace0 \uc788\ub294\uc9c0\ub97c \uccb4\ud06c\ud558\uace0, \uc0ac\uc6a9\ub418\uc9c0 \uc54a\uace0 \uc788\uc744 \uacbd\uc6b0\uc5d0\ub294 USE_CONCAT \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\uc5ec CONCATENATION \uc774 \uc0ac\uc6a9\ub420 \uc218 \uc788\ub3c4\ub85d \uc720\ub3c4\ud55c\ub2e4
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

\n <\/p>\n

[ CASE7: \uc870\ud68c\uc870\uac74\ubcc4 UNION ALL \uc758 \uc801\uc808\ud55c \uc0ac\uc6a9 ]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

SELECT \/*+ INDEX (A PK_MSYSOFC) (B TX_CFACPDA_01) +*\/
\n<\/span><\/p>\n

A.TEL_OFC_NAME
\n<\/span><\/p>\n

,B.MNG_OFC_CD
\n<\/span><\/p>\n

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0))
\n<\/span><\/p>\n

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0))
\n<\/span><\/p>\n

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0))
\n<\/span><\/p>\n

,SUM (DECODE ((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0))
\n<\/span><\/p>\n

,COUNT (B.MNG_OFC_CD)
\n<\/span><\/p>\n

FROM TB_MSYSOFC A
\n<\/span><\/p>\n

,TB_CFACPDA B
\n<\/span><\/p>\n

WHERE ((:b0 = ‘1’
\n<\/span><\/p>\n

AND A.TEL_OFC_CD = :b1)
\n<\/span><\/p>\n

OR (:b0 = ‘0’
\n<\/span><\/p>\n

AND DECODE (:b1, ‘200375’, A.TEL_OFC_CD, A.HQ_OFC_CD) = :b1
\n<\/span><\/p>\n

AND A.TEL_OFC_TYPE_CD IN
\n<\/span><\/p>\n

(’02’, ’03’, ’07’, ’11’, ’12’, ‘M2’, ‘M3’)))
\n<\/span><\/p>\n

AND B.MNG_OFC_CD = A.TEL_OFC_CD(+)
\n<\/span><\/p>\n

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD) IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)
\n<\/span><\/p>\n

GROUP BY A.TEL_OFC_NAME
\n<\/span><\/p>\n

,B.MNG_OFC_CD
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE        1 K         133
\n<\/span><\/p>\n

SORT GROUP BY        1 K    157 K    133
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

NESTED LOOPS OUTER
\n<\/span><\/p>\n

INDEX FAST FULL SCAN    TX_CFACPDA_01<\/strong><\/span>    79     2 K    2
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_MSYSOFC    2 K    130 K    1
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_MSYSOFC    2 K
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

TB_CFACPDA \ud14c\uc774\ube14 \uc778\ub371\uc2a4\uc815\ubcf4
\n<\/span><\/p>\n

\n\n\n\n<\/colgroup>\n\n\n\n\n\n\n\n\n
\n

PK_CFACPDA<\/span><\/p>\n<\/td>\n

\n

RCV_DATE, SERIAL_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CFACPDA_01<\/span><\/p>\n<\/td>\n

\n

MNG_OFC_CD, PDA_STATUS_CD, PDA_QUALITY_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CFACPDA_02<\/span><\/p>\n<\/td>\n

\n

PDA_STATUS_CD, PDA_QUALITY_CD<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CFACPDA_03<\/span><\/p>\n<\/td>\n

\n

SERIAL_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CFACPDA_04<\/span><\/p>\n<\/td>\n

\n

MNG_OFC_CD, RCV_DATE, SERIAL_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CFACPDA_05<\/span><\/p>\n<\/td>\n

\n

MNG_OFC_CD, MNG_CHNG_DATE, SERIAL_NO<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

TX_CFACPDA_06<\/span><\/p>\n<\/td>\n

\n

MNG_OFC_CD, MODEL_CD, PDA_STATUS_CD, PDA_QUALITY_CD, MNG_CHNG_FLAG, RCV_DATE, SERIAL_NO<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n <\/p>\n

1. TX_CFACPDA_01 \uc778\ub371\uc2a4\ub97c FAST FULL SCAN \ud558\uace0 \uc788\uc73c\ub098 \uc870\uac74\uc73c\ub85c \ubcf4\uc544 :b0 \uac12\uacfc :b1 \uac12\uc5d0 \ub530\ub77c \uac01\uac01 \ub2e4\ub978 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 SQL \uc744 \uc791\uc131\ud558\uc5ec
\n<\/span><\/p>\n

UNION ALL \ucc98\ub9ac\ud558\uc5ec \uacbd\uc6b0\uc5d0 \ub530\ub77c \uc720\ub9ac\ud55c \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud558\ub294 \uac83\uc774 \uc720\ub9ac\ud568
\n<\/span><\/p>\n

– :b0 = ‘1’ \uc778 \uacbd\uc6b0\uc5d0\ub294 B.MNG_OFC_CD =
\n\t\t\t\t\t\t\t\t:b1 \uc870\uac74\uc744 \uc0ac\uc6a9\ud558\uc5ec TX_CFACPDA_01 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \uc88b\uc74c
\n<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

– :b0 = ‘0’ \uc774\uba74\uc11c :b1 = ‘200375’<\/span>
\n\t\t\t\t\t<\/span>\uc778<\/span>
\n\t\t\t<\/span>\uacbd\uc6b0\uc5d0\ub294 TB_MSYSOFC \ub97c \uba3c\uc800 UNIQUE SCAN \ud558\uc5ec \uac00\uc838\uc628 \uac12\uc73c\ub85c TX_CFACPDA_01 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud558\ub294 \uac83\uc774 \uc88b\uc74c<\/span>
\n\t\t<\/span><\/p>\n

– :b0 = ‘0’ \uc774\uba74\uc11c :b1 = ‘200375’<\/span>
\n\t\t\t\t\t<\/span>\uc774<\/span>
\n\t\t\t<\/span>\uc544\ub2cc \uacbd\uc6b0\uc5d0\ub294 TB_MSYSOFC \ub97c \uba3c\uc800 FULL SCAN \ud558\uc5ec <\/span>TB_CFACPDA \ud14c\uc774\ube14\uacfc \uc870\uc778\ud558\ub294 \uac83\uc774 \uc88b\uc74c
\n<\/span><\/p>\n

\uac01\uac01\uc758 \uacbd\uc6b0\uc5d0 \ud574\ub2f9\ud558\ub294 SQL \ub85c \ubd84\ub9ac\ud558\uc5ec UNION ALL \ub85c \ucc98\ub9ac\ud55c\ub2e4<\/span>
\n\t\t<\/span><\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

SELECT \/*+ ORDERED USE_NL(X Y) *\/<\/strong>
\n\t\t\t<\/span><\/span><\/p>\n

X.MNG_OFC_CD
\n<\/span><\/p>\n

,Y.TEL_OFC_NAME
\n<\/span><\/p>\n

,X.PG_SUM
\n<\/span><\/p>\n

,X.PB_SUM
\n<\/span><\/p>\n

,X.CG_SUM
\n<\/span><\/p>\n

,X.SG_SUM
\n<\/span><\/p>\n

,X.CNT
\n<\/span><\/p>\n

FROM (SELECT B.MNG_OFC_CD
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0)) PG_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0)) PB_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0)) CG_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0)) SG_SUM
\n<\/span><\/p>\n

,COUNT(B.MNG_OFC_CD) CNT
\n<\/span><\/p>\n

FROM TB_CFACPDA B
\n<\/span><\/p>\n

WHERE :b0 = ‘1’
\n<\/strong><\/span><\/p>\n

         AND B.MNG_OFC_CD = :b1
\n<\/strong><\/span><\/p>\n

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD)IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)
\n<\/span><\/p>\n

GROUP BY B.MNG_OFC_CD
\n<\/span><\/p>\n

     UNION ALL
\n<\/strong><\/span><\/p>\n

         SELECT B.MNG_OFC_CD
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0)) PG_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0)) PB_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0)) CG_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0)) SG_SUM
\n<\/span><\/p>\n

,COUNT(B.MNG_OFC_CD) CNT
\n<\/span><\/p>\n

FROM TB_CFACPDA B
\n<\/span><\/p>\n

WHERE :b0 = ‘0’
\n<\/strong><\/span><\/p>\n

             AND :b1 = ‘200375’
\n<\/strong><\/span><\/p>\n

             AND B.MNG_OFC_CD = (SELECT A.TEL_OFC_CD FROM TB_MSYSOFC A WHERE A.TEL_OFC_CD = :b1)
\n<\/strong><\/span><\/p>\n

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD)IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)
\n<\/span><\/p>\n

GROUP BY B.MNG_OFC_CD
\n<\/span><\/p>\n


\n\t\t\tUNION ALL
\n<\/strong><\/span><\/p>\n

         SELECT \/*+ ORDERED INDEX(B TB_CFACPDA_01) *\/<\/strong>
\n\t\t<\/span><\/p>\n

         B.MNG_OFC_CD
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PG’, 1, 0)) PG_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘PB’, 1, 0)) PB_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘CG’, 1, 0)) CG_SUM
\n<\/span><\/p>\n

,SUM(DECODE((B.PDA_STATUS_CD || B.PDA_QUALITY_CD), ‘SB’, 1, 0)) SG_SUM
\n<\/span><\/p>\n

,COUNT(B.MNG_OFC_CD) CNT
\n<\/span><\/p>\n

FROM (SELECT A.TEL_OFC_CD FROM TB_MSYSOFC A WHERE A.HQ_OFC_CD = :b1) A,
\n<\/span><\/p>\n

             TB_CFACPDA B
\n<\/span><\/p>\n

WHERE :b0 = ‘0’
\n<\/strong><\/span><\/p>\n

     AND :b1 <> ‘200375’
\n<\/strong><\/span><\/p>\n

     AND B.MNG_OFC_CD(+) = A.TEL_OFC_CD
\n<\/span><\/p>\n

AND (B.PDA_STATUS_CD || B.PDA_QUALITY_CD)IN (‘PG’, ‘PB’, ‘CG’, ‘SB’)
\n<\/span><\/p>\n

GROUP BY B.MNG_OFC_CD
\n<\/span><\/p>\n

         ) X
\n<\/span><\/p>\n

,TB_MSYSOFC Y
\n<\/span><\/p>\n

WHERE Y.TEL_OFC_CD = X.MNG_OFC_CD
\n\t\t\t<\/strong><\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815 Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE        8 K         457
\n<\/span><\/p>\n

NESTED LOOPS        8 K    800 K    457
\n<\/span><\/p>\n

VIEW        402     29 K    55
\n<\/span><\/p>\n

UNION-ALL<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

SORT GROUP BY        1     35     2
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CFACPDA_01<\/strong><\/span>    1     35     2
\n<\/span><\/p>\n

SORT GROUP BY        1     35     2
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CFACPDA_01<\/strong><\/span>    1     35     2
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_MSYSOFC    1     10     2
\n<\/span><\/p>\n

SORT GROUP BY        400     19 K    51
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

FILTER
\n<\/span><\/p>\n

NESTED LOOPS OUTER
\n<\/span><\/p>\n

TABLE ACCESS FULL    TB_MSYSOFC    20     200     3
\n<\/span><\/p>\n

INDEX RANGE SCAN    TX_CFACPDA_01<\/strong><\/span>    2 K    78 K    2
\n<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_MSYSOFC    2 K    52 K    1
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_MSYSOFC    2 K         2
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778>
\n<\/em><\/strong><\/span><\/p>\n

\uc870\ud68c\uc870\uac74\uc774 \uacbd\uc6b0\uc5d0 \ub530\ub77c \ub2ec\ub77c\uc9c8 \uacbd\uc6b0\uc5d0\ub294 \uac01\uac01\uc758 \uacbd\uc6b0\uc5d0 \ucd5c\uc801\uc758 \uc778\ub371\uc2a4\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub3c4\ub85d SQL \uc744 \ubd84\ub9ac\ud558\uc5ec \uac01\uac01\uc744 \ucd5c\uc801\ud654\ud55c \ub2e4\uc74c UNION ALL \ub85c \ubb36\uc5b4\uc11c \ucc98\ub9ac\ud55c\ub2e4
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

\n <\/p>\n

[ CASE8: HASH JOIN \uc758 \uc0ac\uc6a9 ]<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

<SQL>
\n<\/span><\/p>\n

SELECT DISTINCT A.NODE_OFC_CD
\n<\/span><\/p>\n

,LPAD (B.A_NO, 4, ‘0’)
\n<\/span><\/p>\n

FROM TB_CFACNODEOFC A
\n<\/span><\/p>\n

,TB_MSYSOFC B
\n<\/span><\/p>\n

WHERE (A.NODE_OFC_CD = B.TEL_OFC_CD
\n<\/span><\/p>\n

AND A.SA_CD = :b0)
\n<\/span><\/p>\n

\n <\/p>\n

<Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

SORT UNIQUE
\n<\/span><\/p>\n


\n\t\t\t\tNESTED LOOPS <\/strong><\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CFACNODEOFC
\n<\/span><\/p>\n

INDEX RANGE SCAN    PK_CFACNODEOFC
\n<\/strong><\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_MSYSOFC
\n<\/span><\/p>\n

INDEX UNIQUE SCAN    PK_MSYSOFC
\n<\/strong><\/span><\/p>\n

\n <\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

0 recursive calls
\n<\/span><\/p>\n

0 db block gets
\n<\/span><\/p>\n

810 consistent gets<\/span>
\n\t\t\t<\/strong><\/span><\/p>\n

6 physical reads
\n<\/span><\/p>\n

0 redo size
\n<\/span><\/p>\n

2825 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

985 bytes received via SQL*Net from client
\n<\/span><\/p>\n

9 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

1 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

98 rows processed
\n<\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd\ud3ec\uc778\ud2b8>
\n<\/span><\/p>\n

1. \ud604\uc7ac TB_CFACNODEOFC \ud14c\uc774\ube14\uc744 INDEX \ub97c \ud1b5\ud574 \uc77d\uc740 \ub2e4\uc74c TB_MSYSOFC \ud14c\uc774\ube14\uacfc NESTED LOOP \uc870\uc778\uc774 \uc77c\uc5b4\ub098\uace0 \uc788\ub294\ub370 TB_CFACNODEOFC \ud14c\uc774\ube14\uc5d0\uc11c A.SA_CD = ‘0577’ \ub9cc\uc871\ud558\uc5ec NESTED LOOP \uc870\uc778\uc774 \uc218\ud589\ub418\ub294 \ud69f\uc218\ub294 \uc544\ub798\uc758 \ub370\uc774\ud130 \uc790\ub8cc\ub97c \ubcfc \ub54c \ud3c9\uade0\uc801\uc73c\ub85c 400 \ud68c \uc815\ub3c4\uc774\ubbc0\ub85c NESTED LOOP \uc870\uc778\uc758 \ubd80\ub2f4\uc774 \ub9ce\uc740 \uc0c1\ud0dc\uc784
\n<\/span><\/p>\n

\n <\/p>\n

select SA_CD, count(*)
\n<\/span><\/p>\n

from TB_CFACNODEOFC
\n<\/span><\/p>\n

group by SA_CD
\n<\/span><\/p>\n

\n <\/p>\n

SA_CD    COUNT(*)
\n<\/span><\/p>\n

——————
\n<\/span><\/p>\n

0511    434
\n<\/span><\/p>\n

0513    394
\n<\/span><\/p>\n

0577    269
\n<\/span><\/p>\n

0821    423
\n<\/span><\/p>\n

0825    11
\n<\/span><\/p>\n

2186    410
\n<\/span><\/p>\n

2189    410
\n<\/span><\/p>\n

C600    425
\n<\/span><\/p>\n

CB00    112<\/span>
\n\t\t<\/span><\/p>\n

\n <\/p>\n

\ub530\ub77c\uc11c TB_MSYSOFC \ud14c\uc774\ube14\uc740 \uac74\uc218\uac00 \ub9e4\uc6b0 \uc791\uc740 \ud14c\uc774\ube14\uc774\ubbc0\ub85c \uc774 \ud14c\uc774\ube14\uc744 \uba3c\uc800 FULL SCAN \ud55c \ub2e4\uc74c HASH JOIN \uc73c\ub85c \ud480\ub9ac\ub294 \uac83\uc774 NESTED LOOP \uc5d0 \uc758\ud55c \ub9ce\uc740 TABLE ACCESS \ub97c \uc904\uc77c \uc218 \uc788\uc73c\ubbc0\ub85c FROM \uc808 \uc21c\uc11c\ub97c \ubc14\uafb8\uace0 HINT \ub97c \uc0ac\uc6a9\ud558\uc5ec SQL \uc744 \uc218\uc815\ud55c\ub2e4<\/span>
\n\t\t<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815SQL>
\n<\/span><\/p>\n

SELECT \/*+ ORDERED USE_HASH(B A) *\/<\/strong><\/span>
\n\t\t<\/span><\/p>\n

DISTINCT A.NODE_OFC_CD
\n<\/span><\/p>\n

,LPAD (B.A_NO, 4, ‘0’)
\n<\/span><\/p>\n


\n\t\t\t FROM TB_MSYSOFC B
\n<\/strong><\/span><\/p>\n

,TB_CFACNODEOFC A
\n<\/strong><\/span><\/p>\n

WHERE (A.NODE_OFC_CD = B.TEL_OFC_CD
\n<\/span><\/p>\n

AND A.SA_CD = :b0)
\n<\/span><\/p>\n

\n <\/p>\n

<\uc218\uc815\ud6c4 Execution Plan>
\n<\/span><\/p>\n

SELECT STATEMENT Optimizer Mode=RULE
\n<\/span><\/p>\n

SORT UNIQUE
\n<\/span><\/p>\n


\n\t\t\tHASH JOIN<\/strong>
\n\t\t<\/span><\/p>\n


\n\t\t\tTABLE ACCESS FULL<\/strong>
\n\t\t<\/span><\/p>\n

TABLE ACCESS BY INDEX ROWID    TB_CFACNODEOFC
\n<\/span><\/p>\n

INDEX RANGE SCAN    PK_CFACNODEOFC
\n<\/strong><\/span><\/p>\n

\n <\/p>\n

Statistics
\n<\/span><\/p>\n

———————————————————-
\n<\/span><\/p>\n

0 recursive calls
\n<\/span><\/p>\n

3 db block gets
\n<\/span><\/p>\n

317 consistent gets
\n<\/strong><\/span><\/p>\n

6 physical reads
\n<\/span><\/p>\n

0 redo size
\n<\/span><\/p>\n

2825 bytes sent via SQL*Net to client
\n<\/span><\/p>\n

1021 bytes received via SQL*Net from client
\n<\/span><\/p>\n

9 SQL*Net roundtrips to\/from client
\n<\/span><\/p>\n

1 sorts (memory)
\n<\/span><\/p>\n

0 sorts (disk)
\n<\/span><\/p>\n

98 rows processed
\n\t\t\t<\/strong><\/span><\/p>\n

\n <\/p>\n

<\ud29c\ub2dd \uac00\uc774\ub4dc\ub77c\uc778>
\n<\/em><\/strong><\/span><\/p>\n

NESTED LOOP \uc870\uc778\uc5d0\uc11c \ub4dc\ub77c\uc774\ube59 \ud14c\uc774\ube14\ub85c \uc801\ud569\ud55c \ucc98\ub9ac\ubc94\uc704\ub97c \ub9ce\uc774 \uc904\uc5ec\uc904 \uc218 \uc788\ub294 \uc870\uac74\uc774 \uc5c6\uc744 \uacbd\uc6b0\uc5d0\ub294 HASH JOIN \uc744 \uc0ac\uc6a9\ud560 \uac83\uc744 \uace0\ub824\ud574\ubcf4\ub294\ub370, HASH JOIN \uc740 \ud56d\uc0c1 \uc801\uc740 \uac74\uc218\uc758 \ud14c\uc774\ube14\uc774 \uba3c\uc800 \ud480\ub9ac\ub3c4\ub85d \ud558\uc5ec\uc57c PGA \uc601\uc5ed\uc758 HASH \uba54\ubaa8\ub9ac\ub97c \uc801\uac8c \uc0ac\uc6a9\ud558\uace0 HASH JOIN \uc2dc \ucc3e\uc744 \ub370\uc774\ud130 \uac74\uc218\uac00 \uc801\uc5b4\uc9c0\ubbc0\ub85c \uc791\uc740 \ud14c\uc774\ube14\uc744 \uba3c\uc800 \uc77d\uc744 \uc218 \uc788\ub3c4\ub85d \uc8fc\uc758\ud55c\ub2e4.
\n<\/em><\/strong><\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n

\n <\/p>\n

\n <\/p>\n

<\ucc38\uace0\uc790\ub8cc>
\n<\/span><\/p>\n

– An Introduction to Database Systems, Addison Wesley
\n<\/span><\/p>\n

– Oracle Performance Tuning, O’reilly
\n<\/span><\/p>\n

– Effective Oracle By Design, McGraw Hill
\n<\/span><\/p>\n

– Oracle Expert One-On-One, Apress
\n<\/span><\/p>\n

– Oracle Concepts Manual, Oracle
\n<\/span><\/p>\n

– Oracle Administrator’s Guide Manual, Oracle
\n<\/span><\/p>\n

– Oracle SQL Reference Manual, Oracle
\n<\/span><\/p>\n

– Oracle Tuning Manual, Oracle
\n<\/span><\/p>\n

– Oracle SQL*Plus User’s Guide and Reference, Oracle
\n<\/span><\/p>\n

– Oracle Internal, Oracle Education Center<\/span>
\n\t\t<\/span><\/p>\n

\n <\/p>\n

\n <\/p>\n


\n\t\t<\/span> <\/p>\n","protected":false},"excerpt":{"rendered":"

  SQL Tuning CASE   [ CASE1: \uc801\uc808\ud55c \uc778\ub371\uc2a4\uc758 \uc0ac\uc6a9 ] <SQL> UPDATE TB_CCPIDSCNT SET END_DATE = DECODE (SIGN (( NVL (END_DATE,TO_CHAR (( ADD_MONTHS (TO_DATE (START_DATE,’YYYYMMDD’),36)– 1),’YYYYMMDD’))– :b0)),1, :b0,END_DATE) ,END_ORD_NO = :b2 WHERE NODE_TYPE = ‘S’ AND NODE_ID = :b3 AND DC_PLAN_ID IN (‘0680’, ‘0964’) AND END_ORD_NO IS NULL   <Execution Plan> UPDATE STATEMENT Optimizer Mode=RULE         […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[11],"tags":[230,233],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/475"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=475"}],"version-history":[{"count":0,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/475\/revisions"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=475"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}