데이터베이스 질문과 답변
SELECT /*+ LEADING(@SEL$3 C@SEL$3 D@SEL$3 B@SEL$3 ) USE_NL(@SEL$3 B@SEL$3) */ INPUT_DD, INPUT_TP, SUM(CASE WHEN BIRTH_YEAR = 99 THEN 1 ELSE 0 END) B_99, SUM(CASE WHEN BIRTH_YEAR = 98 THEN 1 ELSE 0 END) B_98, SUM(CASE WHEN BIRTH_YEAR = 97 THEN 1 ELSE 0 END) B_97, SUM(CASE WHEN BIRTH_YEAR = 96 THEN 1 ELSE 0 END) B_96, SUM(CASE WHEN BIRTH_YEAR = 95 THEN 1 ELSE 0 END) B_95, SUM(CASE WHEN BIRTH_YEAR = 94 THEN 1 ELSE 0 END) B_94, SUM(CASE WHEN BIRTH_YEAR = 93 THEN 1 ELSE 0 END) B_93, SUM(CASE WHEN BIRTH_YEAR = 92 THEN 1 ELSE 0 END) B_92, SUM(CASE WHEN BIRTH_YEAR = 91 THEN 1 ELSE 0 END) B_91, SUM(CASE WHEN BIRTH_YEAR = 90 THEN 1 ELSE 0 END) B_90, SUM(CASE WHEN BIRTH_YEAR = 89 THEN 1 ELSE 0 END) B_89, SUM(CASE WHEN BIRTH_YEAR = 88 THEN 1 ELSE 0 END) B_88, SUM(CASE WHEN BIRTH_YEAR = 87 THEN 1 ELSE 0 END) B_87, SUM(CASE WHEN BIRTH_YEAR = 86 THEN 1 ELSE 0 END) B_86, SUM(CASE WHEN BIRTH_YEAR = 85 THEN 1 ELSE 0 END) B_85, SUM(CASE WHEN BIRTH_YEAR = 84 THEN 1 ELSE 0 END) B_84, SUM(CASE WHEN BIRTH_YEAR = 83 THEN 1 ELSE 0 END) B_83, SUM(CASE WHEN BIRTH_YEAR = 82 THEN 1 ELSE 0 END) B_82, SUM(CASE WHEN BIRTH_YEAR = 81 THEN 1 ELSE 0 END) B_81, SUM(CASE WHEN BIRTH_YEAR = 80 THEN 1 ELSE 0 END) B_80, SUM(CASE WHEN BIRTH_YEAR = 79 THEN 1 ELSE 0 END) B_79, SUM(CASE WHEN BIRTH_YEAR = 78 THEN 1 ELSE 0 END) B_78, SUM(CASE WHEN BIRTH_YEAR = 77 THEN 1 ELSE 0 END) B_77, SUM(CASE WHEN BIRTH_YEAR = 76 THEN 1 ELSE 0 END) B_76, SUM(CASE WHEN BIRTH_YEAR = 75 THEN 1 ELSE 0 END) B_75, SUM(CASE WHEN BIRTH_YEAR = 74 THEN 1 ELSE 0 END) B_74, SUM(CASE WHEN BIRTH_YEAR = 73 THEN 1 ELSE 0 END) B_73, SUM(CASE WHEN BIRTH_YEAR = 72 THEN 1 ELSE 0 END) B_72, SUM(CASE WHEN BIRTH_YEAR = 71 THEN 1 ELSE 0 END) B_71, SUM(CASE WHEN BIRTH_YEAR = 70 THEN 1 ELSE 0 END) B_70, SUM(CASE WHEN BIRTH_YEAR = 69 THEN 1 ELSE 0 END) B_69, SUM(CASE WHEN BIRTH_YEAR = 68 THEN 1 ELSE 0 END) B_68, SUM(CASE WHEN BIRTH_YEAR = 67 THEN 1 ELSE 0 END) B_67, SUM(CASE WHEN BIRTH_YEAR = 66 THEN 1 ELSE 0 END) B_66, SUM(CASE WHEN BIRTH_YEAR = 65 THEN 1 ELSE 0 END) B_65, SUM(CASE WHEN BIRTH_YEAR = 64 THEN 1 ELSE 0 END) B_64, SUM(CASE WHEN BIRTH_YEAR = 63 THEN 1 ELSE 0 END) B_63, SUM(CASE WHEN BIRTH_YEAR = 62 THEN 1 ELSE 0 END) B_62, SUM(CASE WHEN BIRTH_YEAR = 61 THEN 1 ELSE 0 END) B_61, SUM(CASE WHEN BIRTH_YEAR = 60 THEN 1 ELSE 0 END) B_60, SUM(CASE WHEN BIRTH_YEAR = 59 THEN 1 ELSE 0 END) B_59, SUM(CASE WHEN BIRTH_YEAR = 58 THEN 1 ELSE 0 END) B_58, SUM(CASE WHEN BIRTH_YEAR = 57 THEN 1 ELSE 0 END) B_57, SUM(CASE WHEN BIRTH_YEAR = 56 THEN 1 ELSE 0 END) B_56, SUM(CASE WHEN BIRTH_YEAR = 55 THEN 1 ELSE 0 END) B_55, SUM(CASE WHEN BIRTH_YEAR = 54 THEN 1 ELSE 0 END) B_54, SUM(CASE WHEN BIRTH_YEAR = 53 THEN 1 ELSE 0 END) B_53, SUM(CASE WHEN BIRTH_YEAR = 52 THEN 1 ELSE 0 END) B_52, SUM(CASE WHEN BIRTH_YEAR = 51 THEN 1 ELSE 0 END) B_51, SUM(CASE WHEN BIRTH_YEAR = 50 THEN 1 ELSE 0 END) B_50, SUM(CASE WHEN BIRTH_YEAR = 49 THEN 1 ELSE 0 END) B_49, SUM(CASE WHEN BIRTH_YEAR = 48 THEN 1 ELSE 0 END) B_48, SUM(CASE WHEN BIRTH_YEAR = 47 THEN 1 ELSE 0 END) B_47, SUM(CASE WHEN BIRTH_YEAR = 46 THEN 1 ELSE 0 END) B_46, SUM(CASE WHEN BIRTH_YEAR = 45 THEN 1 ELSE 0 END) B_45, SUM(CASE WHEN BIRTH_YEAR = 44 THEN 1 ELSE 0 END) B_44, SUM(CASE WHEN BIRTH_YEAR = 43 THEN 1 ELSE 0 END) B_43, SUM(CASE WHEN BIRTH_YEAR = 42 THEN 1 ELSE 0 END) B_42, SUM(CASE WHEN BIRTH_YEAR = 41 THEN 1 ELSE 0 END) B_41, SUM(CASE WHEN BIRTH_YEAR = 40 THEN 1 ELSE 0 END) B_40, SUM(CASE WHEN BIRTH_YEAR BETWEEN TO_CHAR(SYSDATE,'YY') AND 92 THEN 1 ELSE 0 END) CNT, COUNT(*) TOTALCNT, COUNT(DISTINCT(INPUT_DD)) OVER() ROWCNT FROM ( WITH OFFER as ( SELECT * FROM LC_OFFER ) SELECT B.CRM_MSTR_NO, SUBSTR(B.BIRTH_DAY,0,2) BIRTH_YEAR,B.INPUT_TP, SUBSTR(B.BIRTH_DAY,7,1) GENDER, B.LATELY_AGREE_DT, B.EXPIRED_DD, (SELECT MAX(INPUT_DD) FROM LC_AGREE WHERE AUTHCODE = B.AUTHCODE) INPUT_DD, (SELECT MAX(OFFER_DD) FROM OFFER WHERE CRM_MSTR_NO = B.CRM_MSTR_NO AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'OI1')) OI1_OFF_DT, (SELECT MAX(OFFER_DD) FROM OFFER WHERE CRM_MSTR_NO = B.CRM_MSTR_NO AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'OI3')) OI3_OFF_DT, (SELECT MAX(OFFER_DD) FROM OFFER WHERE CRM_MSTR_NO = B.CRM_MSTR_NO AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'OE3')) OE3_OFF_DT, (SELECT MAX(OFFER_DD) FROM OFFER WHERE CONTENTS_NO = 200002 AND CRM_MSTR_NO = B.CRM_MSTR_NO AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'TI1')) TI1_OFF_DT, D.RE_OFFER_TERM, (SELECT MAX(OFFER_DD) FROM LC_OFFER WHERE CONTENTS_NO = 200002 AND CRM_MSTR_NO = B.CRM_MSTR_NO AND SPON_NO = D.SPON_NO) SELF_OFF_DD, C.* FROM LC_MEM_MSTR B, LC_TPOF_BSNSS C, LC_SPON D WHERE C.BSNSS_CD = D.BSNSS_CD AND B.HP_AUTH_YN = 'Y' AND D.SPON_NO = 123 AND B.LATELY_AGREE_DT >= D.AGREE_START_DT AND B.LATELY_AGREE_DT BETWEEN '20141001'||'000000' AND '20141031'||'235959' AND B.BIRTH_DAY BETWEEN '66'||'0000' AND '89'||'1231' AND B.CRM_MSTR_NO IS NOT NULL ) E WHERE 1=1 AND (SELF_OFF_DD IS NULL OR SELF_OFF_DD < TO_CHAR(SYSDATE-RE_OFFER_TERM,'YYYYMMDD') ) AND (OI1_OFF_DT IS NULL OR OI1_OFF_DT < TO_CHAR(SYSDATE-OFFR_INSU_MI_TF, 'YYYYMMDD')) AND (OI3_OFF_DT IS NULL OR OI3_OFF_DT < TO_CHAR(SYSDATE-OFFR_INSU_SS_TF, 'YYYYMMDD')) AND (OE3_OFF_DT IS NULL OR OE3_OFF_DT < TO_CHAR(SYSDATE-OFFR_NOINSU_SKB_TF, 'YYYYMMDD')) AND (TI1_OFF_DT IS NULL OR TI1_OFF_DT < TO_CHAR(SYSDATE-USE_INSU_TF, 'YYYYMMDD')) GROUP BY ROLLUP(INPUT_DD,INPUT_TP)
------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
SQL_ID dmpp3p761dsth, child number 0
-------------------------------------
SELECT /*+ LEADING(@SEL$3 C@SEL$3 D@SEL$3 B@SEL$3 ) USE_NL(@SEL$3
B@SEL$3) */ INPUT_DD, INPUT_TP, SUM(CASE WHEN
BIRTH_YEAR = 99 THEN 1 ELSE 0 END) B_99, SUM(CASE WHEN
BIRTH_YEAR = 98 THEN 1 ELSE 0 END) B_98, SUM(CASE WHEN
BIRTH_YEAR = 97 THEN 1 ELSE 0 END) B_97, SUM(CASE WHEN
BIRTH_YEAR = 96 THEN 1 ELSE 0 END) B_96, SUM(CASE WHEN
BIRTH_YEAR = 95 THEN 1 ELSE 0 END) B_95, SUM(CASE WHEN
BIRTH_YEAR = 94 THEN 1 ELSE 0 END) B_94, SUM(CASE WHEN
BIRTH_YEAR = 93 THEN 1 ELSE 0 END) B_93, SUM(CASE WHEN
BIRTH_YEAR = 92 THEN 1 ELSE 0 END) B_92, SUM(CASE WHEN
BIRTH_YEAR = 91 THEN 1 ELSE 0 END) B_91, SUM(CASE WHEN
BIRTH_YEAR = 90 THEN 1 ELSE 0 END) B_90, SUM(CASE WHEN
BIRTH_YEAR = 89 THEN 1 ELSE 0 END) B_89, SUM(CASE WHEN
BIRTH_YEAR = 88 THEN 1 ELSE 0 END) B_88, SUM(CASE WHEN
BIRTH_YEAR = 87 THEN
Plan hash value: 3616432643
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30776 (100)| | | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | LC_AGREE | 1 | 17 | 4 (0)| 00:00:01 | | | |
|* 3 | INDEX RANGE SCAN | XPKLC_AGREE | 1 | | 3 (0)| 00:00:01 | | | |
| 4 | WINDOW BUFFER | | 2 | 214 | 30776 (1)| 00:06:10 | 73728 | 73728 | |
| 5 | SORT GROUP BY ROLLUP | | 2 | 214 | 30776 (1)| 00:06:10 | 73728 | 73728 | |
| 6 | SORT AGGREGATE | | 1 | 27 | | | | | |
| 7 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 | | | |
|* 8 | TABLE ACCESS FULL | LC_SPON | 1 | 7 | 2 (0)| 00:00:01 | | | |
|* 9 | INDEX RANGE SCAN | XPKLC_OFFER | 1 | 20 | 2 (0)| 00:00:01 | | | |
| 10 | SORT AGGREGATE | | 1 | 27 | | | | | |
| 11 | NESTED LOOPS | | 1 | 27 | 6 (0)| 00:00:01 | | | |
|* 12 | TABLE ACCESS FULL | LC_SPON | 2 | 14 | 2 (0)| 00:00:01 | | | |
|* 13 | INDEX RANGE SCAN | XPKLC_OFFER | 1 | 20 | 2 (0)| 00:00:01 | | | |
| 14 | SORT AGGREGATE | | 1 | 27 | | | | | |
| 15 | NESTED LOOPS | | 1 | 27 | 4 (0)| 00:00:01 | | | |
|* 16 | TABLE ACCESS FULL | LC_SPON | 1 | 7 | 2 (0)| 00:00:01 | | | |
|* 17 | INDEX RANGE SCAN | XPKLC_OFFER | 1 | 20 | 2 (0)| 00:00:01 | | | |
| 18 | SORT AGGREGATE | | 1 | 32 | | | | | |
| 19 | NESTED LOOPS | | 1 | 32 | 6 (0)| 00:00:01 | | | |
|* 20 | TABLE ACCESS FULL | LC_SPON | 2 | 14 | 2 (0)| 00:00:01 | | | |
|* 21 | INDEX RANGE SCAN | XPKLC_OFFER | 1 | 25 | 2 (0)| 00:00:01 | | | |
| 22 | SORT AGGREGATE | | 1 | 25 | | | | | |
| 23 | FIRST ROW | | 1 | 25 | 3 (0)| 00:00:01 | | | |
|* 24 | INDEX RANGE SCAN (MIN/MAX) | XPKLC_OFFER | 1 | 25 | 3 (0)| 00:00:01 | | | |
|* 25 | VIEW | | 59461 | 6213K| 30774 (1)| 00:06:10 | | | |
| 26 | NESTED LOOPS | | 59461 | 4645K| 30774 (1)| 00:06:10 | | | |
|* 27 | HASH JOIN | | 1 | 38 | 4 (25)| 00:00:01 | 1000K| 1000K| 982K (0)|
| 28 | TABLE ACCESS FULL | LC_TPOF_BSNSS | 8 | 160 | 2 (0)| 00:00:01 | | | |
|* 29 | TABLE ACCESS BY INDEX ROWID| LC_SPON | 1 | 18 | 1 (0)| 00:00:01 | | | |
|* 30 | INDEX UNIQUE SCAN | XPKLC_SPON | 1 | | 0 (0)| | | | |
|* 31 | TABLE ACCESS FULL | LC_MEM_MSTR | 59461 | 2438K| 30770 (1)| 00:06:10 | | | |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4
2 - SEL$4 / LC_AGREE@SEL$4
3 - SEL$4 / LC_AGREE@SEL$4
4 - SEL$1
6 - SEL$5766345A
8 - SEL$5766345A / LC_SPON@SEL$6
9 - SEL$5766345A / LC_OFFER@SEL$4A4D854C
10 - SEL$16F381A3
12 - SEL$16F381A3 / LC_SPON@SEL$8
13 - SEL$16F381A3 / LC_OFFER@SEL$4A4D854C
14 - SEL$C76F7318
16 - SEL$C76F7318 / LC_SPON@SEL$10
17 - SEL$C76F7318 / LC_OFFER@SEL$4A4D854C
18 - SEL$2D645A8A
20 - SEL$2D645A8A / LC_SPON@SEL$12
21 - SEL$2D645A8A / LC_OFFER@SEL$2
22 - SEL$13
24 - SEL$13 / LC_OFFER@SEL$13
25 - SEL$3 / E@SEL$1
26 - SEL$3
28 - SEL$3 / C@SEL$3
29 - SEL$3 / D@SEL$3
30 - SEL$3 / D@SEL$3
31 - SEL$3 / B@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5766345A")
UNNEST(@"SEL$6")
OUTLINE_LEAF(@"SEL$16F381A3")
UNNEST(@"SEL$8")
OUTLINE_LEAF(@"SEL$C76F7318")
UNNEST(@"SEL$10")
OUTLINE_LEAF(@"SEL$2D645A8A")
UNNEST(@"SEL$12")
OUTLINE_LEAF(@"SEL$13")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SEL$1")
OUTLINE(@"SEL$FD6426FF")
MERGE(@"SEL$4A4D854C")
OUTLINE(@"SEL$6")
OUTLINE(@"SEL$57D1E1D8")
MERGE(@"SEL$4A4D854C")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$EB0F7A06")
MERGE(@"SEL$4A4D854C")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$4F71AB07")
MERGE(@"SEL$2")
OUTLINE(@"SEL$12")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$4A4D854C")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$9")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$2")
NO_ACCESS(@"SEL$1" "E"@"SEL$1")
FULL(@"SEL$3" "C"@"SEL$3")
INDEX_RS_ASC(@"SEL$3" "D"@"SEL$3" ("LC_SPON"."SPON_NO"))
FULL(@"SEL$3" "B"@"SEL$3")
LEADING(@"SEL$3" "C"@"SEL$3" "D"@"SEL$3" "B"@"SEL$3")
USE_HASH(@"SEL$3" "D"@"SEL$3")
USE_NL(@"SEL$3" "B"@"SEL$3")
INDEX(@"SEL$13" "LC_OFFER"@"SEL$13" ("LC_OFFER"."SPON_NO" "LC_OFFER"."CRM_MSTR_NO" "LC_OFFER"."CONTENTS_NO"
"LC_OFFER"."OFFER_DD"))
FULL(@"SEL$2D645A8A" "LC_SPON"@"SEL$12")
INDEX(@"SEL$2D645A8A" "LC_OFFER"@"SEL$2" ("LC_OFFER"."SPON_NO" "LC_OFFER"."CRM_MSTR_NO" "LC_OFFER"."CONTENTS_NO"
"LC_OFFER"."OFFER_DD"))
LEADING(@"SEL$2D645A8A" "LC_SPON"@"SEL$12" "LC_OFFER"@"SEL$2")
USE_NL(@"SEL$2D645A8A" "LC_OFFER"@"SEL$2")
FULL(@"SEL$C76F7318" "LC_SPON"@"SEL$10")
INDEX(@"SEL$C76F7318" "LC_OFFER"@"SEL$4A4D854C" ("LC_OFFER"."SPON_NO" "LC_OFFER"."CRM_MSTR_NO"
"LC_OFFER"."CONTENTS_NO" "LC_OFFER"."OFFER_DD"))
LEADING(@"SEL$C76F7318" "LC_SPON"@"SEL$10" "LC_OFFER"@"SEL$4A4D854C")
USE_NL(@"SEL$C76F7318" "LC_OFFER"@"SEL$4A4D854C")
FULL(@"SEL$16F381A3" "LC_SPON"@"SEL$8")
INDEX(@"SEL$16F381A3" "LC_OFFER"@"SEL$4A4D854C" ("LC_OFFER"."SPON_NO" "LC_OFFER"."CRM_MSTR_NO"
"LC_OFFER"."CONTENTS_NO" "LC_OFFER"."OFFER_DD"))
LEADING(@"SEL$16F381A3" "LC_SPON"@"SEL$8" "LC_OFFER"@"SEL$4A4D854C")
USE_NL(@"SEL$16F381A3" "LC_OFFER"@"SEL$4A4D854C")
FULL(@"SEL$5766345A" "LC_SPON"@"SEL$6")
INDEX(@"SEL$5766345A" "LC_OFFER"@"SEL$4A4D854C" ("LC_OFFER"."SPON_NO" "LC_OFFER"."CRM_MSTR_NO"
"LC_OFFER"."CONTENTS_NO" "LC_OFFER"."OFFER_DD"))
LEADING(@"SEL$5766345A" "LC_SPON"@"SEL$6" "LC_OFFER"@"SEL$4A4D854C")
USE_NL(@"SEL$5766345A" "LC_OFFER"@"SEL$4A4D854C")
INDEX_RS_ASC(@"SEL$4" "LC_AGREE"@"SEL$4" ("LC_AGREE"."AUTHCODE" "LC_AGREE"."AGREE_DD"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("AUTHCODE"=:B1)
8 - filter("BSNSS_CD"='OI1')
9 - access("LC_OFFER"."SPON_NO"="SPON_NO" AND "LC_OFFER"."CRM_MSTR_NO"=:B1)
12 - filter("BSNSS_CD"='OI3')
13 - access("LC_OFFER"."SPON_NO"="SPON_NO" AND "LC_OFFER"."CRM_MSTR_NO"=:B1)
16 - filter("BSNSS_CD"='OE3')
17 - access("LC_OFFER"."SPON_NO"="SPON_NO" AND "LC_OFFER"."CRM_MSTR_NO"=:B1)
20 - filter("BSNSS_CD"='TI1')
21 - access("LC_OFFER"."SPON_NO"="SPON_NO" AND "LC_OFFER"."CRM_MSTR_NO"=:B1 AND "LC_OFFER"."CONTENTS_NO"=200002)
24 - access("SPON_NO"=:B1 AND "CRM_MSTR_NO"=:B2 AND "CONTENTS_NO"=200002)
25 - filter((("SELF_OFF_DD" IS NULL OR "SELF_OFF_DD"<TO_CHAR(SYSDATE@!-INTERNAL_FUNCTION("RE_OFFER_TERM"),'YYYYMMDD'
)) AND ("OI1_OFF_DT" IS NULL OR "OI1_OFF_DT"<TO_CHAR(SYSDATE@!-INTERNAL_FUNCTION("OFFR_INSU_MI_TF"),'YYYYMMDD')) AND
("OI3_OFF_DT" IS NULL OR "OI3_OFF_DT"<TO_CHAR(SYSDATE@!-INTERNAL_FUNCTION("OFFR_INSU_SS_TF"),'YYYYMMDD')) AND
("OE3_OFF_DT" IS NULL OR "OE3_OFF_DT"<TO_CHAR(SYSDATE@!-INTERNAL_FUNCTION("OFFR_NOINSU_SKB_TF"),'YYYYMMDD')) AND
("TI1_OFF_DT" IS NULL OR "TI1_OFF_DT"<TO_CHAR(SYSDATE@!-INTERNAL_FUNCTION("USE_INSU_TF"),'YYYYMMDD'))))
27 - access("C"."BSNSS_CD"="D"."BSNSS_CD")
29 - filter(("D"."BSNSS_CD" IS NOT NULL AND "D"."AGREE_START_DT"<='20141031235959'))
30 - access("D"."SPON_NO"=123)
31 - filter(("B"."LATELY_AGREE_DT">='20141001000000' AND "B"."BIRTH_DAY">='660000' AND "B"."BIRTH_DAY"<='891231'
AND "B"."LATELY_AGREE_DT"<='20141031235959' AND "B"."HP_AUTH_YN"='Y' AND "B"."CRM_MSTR_NO" IS NOT NULL AND
"B"."LATELY_AGREE_DT">="D"."AGREE_START_DT"))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) MAX("INPUT_DD")[8]
2 - "LC_AGREE".ROWID[ROWID,10], "AUTHCODE"[NUMBER,22], "INPUT_DD"[VARCHAR2,8]
3 - "LC_AGREE".ROWID[ROWID,10], "AUTHCODE"[NUMBER,22]
4 - (#keys=2) "INPUT_DD"[VARCHAR2,8], "INPUT_TP"[VARCHAR2,2], SYSDEF[4], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 99
THEN 1 ELSE 0 END )[22], COUNT(*)[22], SUM(CASE WHEN ("BIRTH_YEAR">=TO_CHAR(SYSDATE@!,'YY') AND
TO_NUMBER("BIRTH_YEAR")<=92) THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 40 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 41 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 42
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 43 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 44 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 45 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 46 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 47
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 48 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 49 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 50 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 51 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 52
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 53 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 54 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 55 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 56 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 57
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 58 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 59 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 60 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 61 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 62
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 63 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 64 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 65 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 66 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 67
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 68 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 69 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 70 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 71 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 72
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 73 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 74 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 75 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 76 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 77
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 78 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 79 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 80 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 81 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 82
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 83 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 84 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 85 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 86 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 87
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 88 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 89 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 90 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 91 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 92
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 93 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 94 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 95 THEN 1 ELSE 0 END )
5 - (#keys=2) "INPUT_DD"[VARCHAR2,8], "INPUT_TP"[VARCHAR2,2], COUNT(*)[22], SUM(CASE WHEN
("BIRTH_YEAR">=TO_CHAR(SYSDATE@!,'YY') AND TO_NUMBER("BIRTH_YEAR")<=92) THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 40 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 41 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 42 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 43
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 44 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 45 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 46 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 47 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 48
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 49 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 50 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 51 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 52 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 53
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 54 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 55 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 56 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 57 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 58
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 59 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 60 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 61 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 62 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 63
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 64 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 65 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 66 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 67 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 68
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 69 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 70 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 71 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 72 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 73
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 74 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 75 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 76 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 77 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 78
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 79 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 80 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 81 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 82 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 83
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 84 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 85 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 86 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 87 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 88
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 89 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 90 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 91 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 92 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 93
THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 94 THEN 1 ELSE 0 END )[22], SUM(CASE
TO_NUMBER("BIRTH_YEAR") WHEN 95 THEN 1 ELSE 0 END )[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 96 THEN 1 ELSE 0 END
)[22], SUM(CASE TO_NUMBER("BIRTH_YEAR") WHEN 97 THEN 1 ELSE
6 - (#keys=0) MAX("LC_OFFER"."OFFER_DD")[8]
7 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10], "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22],
"LC_OFFER"."CRM_MSTR_NO"[NUMBER,22], "LC_OFFER"."OFFER_DD"[VARCHAR2,8]
8 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10]
9 - "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22], "LC_OFFER"."CRM_MSTR_NO"[NUMBER,22],
"LC_OFFER"."OFFER_DD"[VARCHAR2,8]
10 - (#keys=0) MAX("LC_OFFER"."OFFER_DD")[8]
11 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10], "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22],
"LC_OFFER"."CRM_MSTR_NO"[NUMBER,22], "LC_OFFER"."OFFER_DD"[VARCHAR2,8]
12 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10]
13 - "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22], "LC_OFFER"."CRM_MSTR_NO"[NUMBER,22],
"LC_OFFER"."OFFER_DD"[VARCHAR2,8]
14 - (#keys=0) MAX("LC_OFFER"."OFFER_DD")[8]
15 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10], "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22],
"LC_OFFER"."CRM_MSTR_NO"[NUMBER,22], "LC_OFFER"."OFFER_DD"[VARCHAR2,8]
16 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10]
17 - "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22], "LC_OFFER"."CRM_MSTR_NO"[NUMBER,22],
"LC_OFFER"."OFFER_DD"[VARCHAR2,8]
18 - (#keys=0) MAX("LC_OFFER"."OFFER_DD")[8]
19 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10], "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22],
"LC_OFFER"."CRM_MSTR_NO"[NUMBER,22], "LC_OFFER"."CONTENTS_NO"[NUMBER,22], "LC_OFFER"."OFFER_DD"[VARCHAR2,8]
20 - "SPON_NO"[NUMBER,22], "BSNSS_CD"[VARCHAR2,10]
21 - "LC_OFFER".ROWID[ROWID,10], "LC_OFFER"."SPON_NO"[NUMBER,22], "LC_OFFER"."CRM_MSTR_NO"[NUMBER,22],
"LC_OFFER"."CONTENTS_NO"[NUMBER,22], "LC_OFFER"."OFFER_DD"[VARCHAR2,8]
22 - (#keys=0) MAX("OFFER_DD")[8]
23 - "LC_OFFER".ROWID[ROWID,10], "SPON_NO"[NUMBER,22], "CRM_MSTR_NO"[NUMBER,22], "CONTENTS_NO"[NUMBER,22],
"OFFER_DD"[VARCHAR2,8]
24 - "LC_OFFER".ROWID[ROWID,10], "SPON_NO"[NUMBER,22], "CRM_MSTR_NO"[NUMBER,22], "CONTENTS_NO"[NUMBER,22],
"OFFER_DD"[VARCHAR2,8]
25 - "BIRTH_YEAR"[VARCHAR2,4], "INPUT_TP"[VARCHAR2,2], "INPUT_DD"[VARCHAR2,8], "OI1_OFF_DT"[VARCHAR2,8],
"OI3_OFF_DT"[VARCHAR2,8], "OE3_OFF_DT"[VARCHAR2,8], "TI1_OFF_DT"[VARCHAR2,8], "RE_OFFER_TERM"[NUMBER,22],
"SELF_OFF_DD"[VARCHAR2,8], "OFFR_INSU_MI_TF"[NUMBER,22], "OFFR_NOINSU_SKB_TF"[NUMBER,22], "USE_INSU_TF"[NUMBER,22],
"OFFR_INSU_SS_TF"[NUMBER,22]
26 - "C"."BSNSS_CD"[VARCHAR2,10], "D"."BSNSS_CD"[VARCHAR2,10], "C"."OFFR_INSU_SS_TF"[NUMBER,22],
"C"."OFFR_INSU_MI_TF"[NUMBER,22], "C"."OFFR_NOINSU_SKB_TF"[NUMBER,22], "C"."USE_INSU_TF"[NUMBER,22],
"SYS_ALIAS_7".ROWID[ROWID,10], "D"."SPON_NO"[NUMBER,22], "D"."AGREE_START_DT"[VARCHAR2,14],
"D"."RE_OFFER_TERM"[NUMBER,22], "B"."AUTHCODE"[NUMBER,22], "B"."BIRTH_DAY"[VARCHAR2,8],
"B"."LATELY_AGREE_DT"[VARCHAR2,14], "B"."HP_AUTH_YN"[CHARACTER,1], "B"."CRM_MSTR_NO"[NUMBER,22],
"B"."INPUT_TP"[VARCHAR2,2]
27 - (#keys=1) "C"."BSNSS_CD"[VARCHAR2,10], "D"."BSNSS_CD"[VARCHAR2,10], "C"."OFFR_INSU_SS_TF"[NUMBER,22],
"C"."OFFR_INSU_MI_TF"[NUMBER,22], "C"."OFFR_NOINSU_SKB_TF"[NUMBER,22], "C"."USE_INSU_TF"[NUMBER,22],
"SYS_ALIAS_7".ROWID[ROWID,10], "D"."SPON_NO"[NUMBER,22], "D"."AGREE_START_DT"[VARCHAR2,14],
"D"."RE_OFFER_TERM"[NUMBER,22]
28 - "C"."BSNSS_CD"[VARCHAR2,10], "C"."OFFR_INSU_MI_TF"[NUMBER,22], "C"."OFFR_NOINSU_SKB_TF"[NUMBER,22],
"C"."USE_INSU_TF"[NUMBER,22], "C"."OFFR_INSU_SS_TF"[NUMBER,22]
29 - "SYS_ALIAS_7".ROWID[ROWID,10], "D"."SPON_NO"[NUMBER,22], "D"."BSNSS_CD"[VARCHAR2,10],
"D"."RE_OFFER_TERM"[NUMBER,22], "D"."AGREE_START_DT"[VARCHAR2,14]
30 - "SYS_ALIAS_7".ROWID[ROWID,10], "D"."SPON_NO"[NUMBER,22]
31 - "B"."AUTHCODE"[NUMBER,22], "B"."BIRTH_DAY"[VARCHAR2,8], "B"."LATELY_AGREE_DT"[VARCHAR2,14],
"B"."HP_AUTH_YN"[CHARACTER,1], "B"."CRM_MSTR_NO"[NUMBER,22], "B"."INPUT_TP"[VARCHAR2,2]
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
안녕하세요 튜닝 관련하여 질문 드립니다.
보시는 바와 같이 중점적으로 해결해야 할 사항이 group by와 lc_mem_mstr 풀 타는 것을 해결해야 할 듯
보입니다만 Outline Data에 있는 LEADING과 USE_NL 힌트를 줘봐도 시간이 줄질 않습니다.
답변 부탁드립니다.
by 부쉬맨
[2014.12.05 12:55:16]
LC_TPOF_BSNSS C, LC_SPON D
테이블기준 조인되는 조건을 따로 인라인뷰로 빼고
LC_MEM_MSTR 테이블과 조인 하세요
조인은 USE_HASH 방식으로 처리
SELECT /*+ LEADING(C D B) USE_HASH(C D) USE_HASH(B D) */B.CRM_MSTR_NO,
SUBSTR(B.BIRTH_DAY,0,2) BIRTH_YEAR,B.INPUT_TP, SUBSTR(B.BIRTH_DAY,7,1) GENDER,
B.LATELY_AGREE_DT,
B.EXPIRED_DD,
(SELECT MAX(INPUT_DD) FROM LC_AGREE WHERE AUTHCODE = B.AUTHCODE) INPUT_DD,
(SELECT MAX(OFFER_DD) FROM OFFER WHERE CRM_MSTR_NO = B.CRM_MSTR_NO
AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'OI1')) OI1_OFF_DT,
(SELECT MAX(OFFER_DD) FROM OFFER WHERE CRM_MSTR_NO = B.CRM_MSTR_NO
AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'OI3')) OI3_OFF_DT,
(SELECT MAX(OFFER_DD) FROM OFFER WHERE CRM_MSTR_NO = B.CRM_MSTR_NO
AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'OE3')) OE3_OFF_DT,
(SELECT MAX(OFFER_DD) FROM OFFER WHERE CONTENTS_NO = 200002 AND CRM_MSTR_NO = B.CRM_MSTR_NO
AND SPON_NO IN (SELECT SPON_NO FROM LC_SPON WHERE BSNSS_CD = 'TI1')) TI1_OFF_DT,
D.RE_OFFER_TERM,
(SELECT MAX(OFFER_DD) FROM LC_OFFER WHERE CONTENTS_NO = 200002 AND CRM_MSTR_NO = B.CRM_MSTR_NO AND SPON_NO = D.SPON_NO)
SELF_OFF_DD, C.*
FROM LC_MEM_MSTR B, LC_TPOF_BSNSS C, LC_SPON D
WHERE C.BSNSS_CD = D.BSNSS_CD
AND B.HP_AUTH_YN = 'Y'
AND D.SPON_NO = 123
AND B.LATELY_AGREE_DT >= D.AGREE_START_DT
AND B.LATELY_AGREE_DT BETWEEN '20141001'||'000000' AND '20141031'||'235959'
AND B.BIRTH_DAY BETWEEN '66'||'0000' AND '89'||'1231'
AND B.CRM_MSTR_NO IS NOT NULL
위 SQL은 힌트로 조작을 시도해보았습니다.
NESTED LOOPS | | 59461 | 4645K| 30774 (1)| 00:06:10 |
부분을 hash 조인처리하세요.
제대로된 조건으로 조인이 되지않아서
랜덤액세스 발생하므로 hash로 변경하여 랜덤액세스를 제어하는방향으로 진행하세요.
) 버튼을 클릭하여 작성 하시면 됩니다.