最近沒遇到啥有意思的案例，都是些很簡單的案例，但是又好久沒寫過部落格了，決定水一波帖子，保持更新。😂
今天這個是任總老婆小王同學提供的金融SQL案例，難是不難，但是遠端的時候網路卡得要命， 心累。😩
慢SQL（關鍵資訊已經加密）：
WITH CORP11111 AS (SELECT T.O_CODE, T.O_NAME, T.CUS_TYPE, T.O_TYPE, T.SET_NAME, T.SET_ID, T.DIM_CODE, T.DIM_OBJ_ID FROM (SELECT C.O_CODE, C.CUS_TYPE, C.O_TYPE, C.O_NAME, T2.SET_NAME, T2.SET_ID, B.DIM_CODE, B.DIM_OBJ_ID, '' AS D_DEPT, '' AS D_NAME, '' AS D_BIZLINE, '' AS P_CLASS, '' AS P_CLASS_NAME FROM (SELECT DIM_OBJ_ID, BASE_DATE, LIMIT_LINE, AWC_QUOTA, OCC_QUOTA, LIMIT_LINE_ABLE, LIMIT_LINE_RATE FROM TAWC11111 WHERE BASE_DATE = '2024-04-01') A INNER JOIN TAWC22222 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID INNER JOIN T2RRRR C ON B.O_CODE = C.O_CODE LEFT JOIN VRG99999 T1 ON C.O_CODE = T1.O_CODE AND T1.BASE_DATE = A.BASE_DATE LEFT JOIN TAXC45200 T2 ON T1.SET_ID = T2.SET_ID AND T2.BASE_DATE = A.BASE_DATE WHERE A.BASE_DATE = '2024-04-01' AND B.DIM_CODE = 'DIM_CORP' AND A.AWC_QUOTA > 0 UNION ALL SELECT T2.SET_ID AS O_CODE, '集團' AS CUS_TYPE, 'group' AS O_TYPE, '' AS O_NAME, T2.SET_NAME, T2.SET_ID, B.DIM_CODE, B.DIM_OBJ_ID, '' AS D_DEPT, '' AS D_NAME, '' AS D_BIZLINE, '' AS P_CLASS, '' AS P_CLASS_NAME FROM (SELECT DIM_OBJ_ID, BASE_DATE, LIMIT_LINE, AWC_QUOTA, OCC_QUOTA, LIMIT_LINE_ABLE, LIMIT_LINE_RATE FROM TAWC11111 WHERE BASE_DATE = '2024-04-01') A INNER JOIN TAWC22222 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID INNER JOIN TAXC45200 T2 ON B.VRG_SET_ID = T2.SET_ID AND A.BASE_DATE = T2.BASE_DATE WHERE A.BASE_DATE = '2024-04-01' AND B.DIM_CODE = 'DIM_VRG' AND A.AWC_QUOTA > 0) T WHERE 1 = 1 GROUP BY O_CODE, O_NAME, CUS_TYPE, O_TYPE, SET_NAME, SET_ID, DIM_CODE, DIM_OBJ_ID), PORT_11112 AS (SELECT A.DIM_OBJ_ID AS CUST_CODE, '' CUS_TYPE, '2' O_TYPE, WM_CONCAT(DISTINCT(C.O_NAME)) O_NAME, WM_CONCAT(DISTINCT(A.D_DEPT)) D_DEPT, WM_CONCAT(DISTINCT(A.D_NAME)) D_NAME, WM_CONCAT(DISTINCT(A.D_BIZLINE)) D_BIZLINE, WM_CONCAT(DISTINCT(A.P_CLASS)) P_CLASS, WM_CONCAT(DISTINCT(DIM_CLASS_NAME)) P_CLASS_NAME, 'DIM_PORT' DIM_CODE, A.DIM_OBJ_ID, T2.SET_NAME FROM (SELECT DISTINCT A.DIM_OBJ_ID, A.DIM_CODE, B.CUST_CODE, B.D_DEPT, B.D_NAME, B.D_BIZLINE, B.P_CLASS FROM (SELECT AWC_QUOTA, LIMIT_LINE, BASE_DATE, DIM_OBJ_ID, OCC_QUOTA, LIMIT_LINE_ABLE, LIMIT_LINE_RATE FROM TAWC11111 WHERE BASE_DATE = '2024-04-01') STATS INNER JOIN TAWC22222 A ON STATS.DIM_OBJ_ID = A.DIM_OBJ_ID INNER JOIN DIM_9999 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID WHERE A.DIM_CODE = 'DIM_PORT' AND STATS.BASE_DATE = '2024-04-01' AND (STATS.LIMIT_LINE > 0 OR STATS.AWC_QUOTA > 0)) A INNER JOIN T2RRRR C ON A.CUST_CODE = C.O_CODE LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME FROM T9X2CC ORDER BY DIM_CODE ASC) D ON A.P_CLASS = D.DIM_CODE LEFT JOIN VRG99999 T1 ON C.O_CODE = T1.O_CODE AND T1.BASE_DATE = '2024-04-01' LEFT JOIN TAXC45200 T2 ON T1.SET_ID = T2.SET_ID AND T1.BASE_DATE = '2024-04-01' WHERE 1 = 1 GROUP BY A.DIM_OBJ_ID, T2.SET_NAME), CREDIT9144 AS (SELECT DECODE(T1.ADJ_TYPE, '調減', -1, 1) * T1.ADJ_LINE AS ADJ_LINE, T2.O_CODE, T1.INST_ID, T4.DIM_OBJ_ID, T5.SET_ID FROM TAWXV999 T1 INNER JOIN TAWC22222 T2 ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID LEFT JOIN TAWC_RESULT_DETAIL T3 ON T1.INST_ID = T3.INST_ID AND T3.AWC_TYPE LIKE 'AWC_%' LEFT JOIN TAWC22222 T4 ON T3.DIM_OBJ_ID = T4.DIM_OBJ_ID LEFT JOIN VRG99999 T5 ON T2.O_CODE = T5.O_CODE AND T5.BASE_DATE = '2024-04-01' WHERE T1.DATA_SOURCE = 'ADJ' AND T1.TMP_FLAG = '0' AND T1.AWC_TYPE = 'AWC_CREDIT' AND (T1.REMARK = '初始化-佔用信用債' OR T1.REMARK = '釋放信用債' OR T1.INST_ID IN (SELECT INST_ID FROM CFLSSX WHERE AWC_CREDIT_FLAG = '1')) AND T1.ADJ_BEG_DATE <= '2024-04-01' AND (T1.ADJ_END_DATE IS NULL OR T1.ADJ_END_DATE > '2024-04-01')) SELECT COUNT(1) FROM (SELECT T1.*, TO_CHAR(DECODE(T1.DIM_CODE, 'DIM_PORT', T3.ADJ_LINE, DECODE(CUS_TYPE, '集團', T4.ADJ_LINE, T2.ADJ_LINE)) / 10000, 'fm999999999999999990.00') AS ADJ_LINE FROM (SELECT T.O_CODE AS ID, T.O_NAME AS CORP_NAME, (SELECT MAX(PARENTID) PARENTID FROM (SELECT CASE MAX(DIM_OBJ_ID) WHEN NULL THEN '' ELSE MAX(TN.CUST_CODE) END PARENTID FROM DIM_9999 TN WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID GROUP BY TN.DIM_OBJ_ID HAVING COUNT(DISTINCT(CUST_CODE)) <= 1) A) PARENTID, T.O_CODE, T.BASE_DATE, T.DIM_OBJ_ID, T.CUS_TYPE, T.O_NAME AS CUS_NAME, T.DIM_CODE, T.IS_NEW_DATE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS LIMIT_LINE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS AWC_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(OCC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS OCC_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_RATE, 0) * 100 ELSE 0 END), 'fm999999999999999990.00') AS LIMIT_LINE_RATE, TO_CHAR(GREATEST(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_ABLE, 0) ELSE 0 END) / 10000, 0), 'fm999999999999999990.00') AS LIMIT_LINE_ABLE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'CE_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS CE_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS ELUL_QUOTA, SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA_ABLE, 0) ELSE 0 END) AS AWC_QUOTA_ABLE, t.set_id, t.set_name FROM (SELECT A.AWC_TYPE, C.O_CODE, C.O_NAME, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, C.CUS_TYPE, 'DIM_CORP' AS DIM_CODE, NULL AS AWC_QUOTA_ABLE, '' AS IS_NEW_DATE, c.set_id, c.set_name FROM (SELECT A.AWC_TYPE, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID FROM TAWC11111 A WHERE A.BASE_DATE = '2024-04-01') A INNER JOIN CORP11111 C ON A.DIM_OBJ_ID = C.DIM_OBJ_ID WHERE A.BASE_DATE = '2024-04-01' UNION ALL SELECT A.AWC_TYPE, B.CUST_CODE AS O_CODE, B.O_NAME || '(組合)' AS NAME, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, B.CUS_TYPE, 'DIM_PORT' AS DIM_CODE, A.AWC_QUOTA_ABLE, CASE WHEN C.MAX_BASE_DATE = '2024-04-01' THEN '1' ELSE '0' END AS IS_NEW_DATE, '' AS set_id, '' AS set_name FROM (SELECT A.AWC_TYPE, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, A.AWC_QUOTA_ABLE FROM TAWC11111 A WHERE A.BASE_DATE = '2024-04-01') A INNER JOIN PORT_11112 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE FROM TAWC11111 WHERE BASE_DATE <= TO_CHAR(SYSDATE, 'YYYY-MM-DD')) C ON 1 = 1 WHERE A.BASE_DATE = '2024-04-01' AND B.DIM_CODE = 'DIM_PORT') T GROUP BY T.O_CODE, T.O_NAME, T.CUS_TYPE, T.BASE_DATE, T.DIM_OBJ_ID, T.DIM_CODE, T.IS_NEW_DATE, t.set_id, t.set_name) T1 LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, O_CODE FROM CREDIT9144 GROUP BY O_CODE) T2 ON T1.O_CODE = T2.O_CODE LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, DIM_OBJ_ID FROM CREDIT9144 GROUP BY DIM_OBJ_ID) T3 ON T1.DIM_OBJ_ID = T3.DIM_OBJ_ID LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, SET_ID FROM CREDIT9144 GROUP BY SET_ID) T4 ON T1.O_CODE = T4.SET_ID) T1 LEFT JOIN (SELECT DIM_OBJ_ID, WM_CONCAT(DISTINCT(TC.O_NAME)) || '(組合)' AS PORT_NAME, WM_CONCAT(DISTINCT(TD1.SD_NAME || '-' || TD2.SD_NAME || '-' || TD3.SD_NAME)) PORT_DEPT, WM_CONCAT(DISTINCT(T3.DIM_CLASS_NAME)) PORT_CLASS FROM DIM_9999 T1 LEFT JOIN TSYS_DEPT TD1 ON T1.D_CORP = TD1.SD_ID LEFT JOIN TSYS_DEPT TD2 ON T1.D_DEPT = TD2.SD_ID LEFT JOIN TSYS_DEPT TD3 ON T1.D_BIZLINE = TD3.SD_ID LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME FROM T9X2CC) T3 ON T1.P_CLASS = T3.DIM_CODE LEFT JOIN T2RRRR TC ON T1.CUST_CODE = TC.O_CODE GROUP BY DIM_OBJ_ID) T2 ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID LEFT JOIN (SELECT * FROM (SELECT T.*, ROW_NUMBER() OVER(PARTITION BY T.O_CODE ORDER BY T.BASEDATE DESC, T.BEG_DATE DESC, IMP_TIME DESC) AS SN FROM TCRT_RESULT T WHERE T.TMP_FLAG = '0' AND T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01') C WHERE C.SN = 1) T3 ON T1.O_CODE = T3.O_CODE;
SQL真實執行計劃：
1 #NSET2: [160580, 1, 3942] 2 #PIPE2: [160580, 1, 3942] 3 #PIPE2: [160579, 1, 3942] 4 #PIPE2: [160566, 1, 3942] 5 #PRJT2: [160557, 1, 3942]; exp_num(1), is_atom(FALSE) 6 #AAGR2: [160557, 1, 3942]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 7 #HASH RIGHT JOIN2: [160557, 362511, 3942]; key_num(1), ret_null(0), KEY(T3.O_CODE=T1.O_CODE) 8 #PRJT2: [24, 331, 528]; exp_num(1), is_atom(FALSE) 9 #SLCT2: [24, 331, 528]; C.SN = var15 10 #PRJT2: [24, 13278, 528]; exp_num(2), is_atom(FALSE) 11 #AFUN: [24, 13278, 528]; afun_num(1); partition_num(1)[T.O_CODE]; order_num(3)[T.BASEDATE, T.BEG_DATE, T.IMP_TIME] 12 #SORT3: [24, 13278, 528]; key_num(4), is_distinct(FALSE), top_flag(0), is_adaptive(0) 13 #SLCT2: [24, 13278, 528]; (T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01') 14 #BLKUP2: [24, 13619, 528]; TCRT_RESULT_TMPFLAG(T) 15 #SSEK2: [24, 13619, 528]; scan_type(ASC), TCRT_RESULT_TMPFLAG(TCRT_RESULT as T), scan_range['0','0'] 16 #PRJT2: [160355, 362511, 3414]; exp_num(1), is_atom(FALSE) 17 #HASH RIGHT JOIN2: [160355, 362511, 3414]; key_num(1), ret_null(0), KEY(T4.SET_ID=T1.O_CODE) 18 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE) 19 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.SET_ID) 20 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0) 21 #HASH RIGHT JOIN2: [160213, 362511, 2568]; key_num(1), ret_null(0), KEY(T3.DIM_OBJ_ID=T1.DIM_OBJ_ID) 22 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE) 23 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.DIM_OBJ_ID) 24 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0) 25 #HASH RIGHT JOIN2: [160110, 362511, 1722]; key_num(1), ret_null(0), KEY(T2.O_CODE=T1.O_CODE) 26 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE) 27 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.O_CODE) 28 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0) 29 #PRJT2: [160045, 362511, 876]; exp_num(2), is_atom(FALSE) 30 #PRJT2: [160045, 362511, 876]; exp_num(2), is_atom(FALSE) 31 #HAGR2: [160045, 362511, 876]; grp_num(9), sfun_num(0); slave_empty(0) keys(DMTEMPVIEW_896344366.TMPCOL0, DMTEMPVIEW_896344366.TMPCOL1, DMTEMPVIEW_896344366.TMPCOL2, DMTEMPVIEW_896344366.TMPCOL3, DMTEMPVIEW_896344366.TMPCOL4, DMTEMPVIEW_896344366.TMPCOL5, DMTEMPVIEW_896344366.TMPCOL6, DMTEMPVIEW_896344366.TMPCOL7, DMTEMPVIEW_896344366.TMPCOL8) 32 #PRJT2: [159784, 362511, 876]; exp_num(9), is_atom(FALSE) 33 #PRJT2: [159784, 362511, 876]; exp_num(9), is_atom(FALSE) 34 #UNION ALL: [159784, 362511, 876] 35 #PRJT2: [2907, 362494, 876]; exp_num(9), is_atom(FALSE) 36 #HASH2 INNER JOIN: [2907, 362494, 876]; KEY_NUM(1); KEY(A.DIM_OBJ_ID=C.DIM_OBJ_ID) KEY_NULL_EQU(0) 37 #PRJT2: [95, 69566, 294]; exp_num(2), is_atom(FALSE) 38 #BLKUP2: [95, 69566, 294]; TAWC_STATIC_RESULT_BASEDATE(A) 39 #SSEK2: [95, 69566, 294]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111 as A), scan_range['2024-04-01','2024-04-01'] 40 #PRJT2: [2773, 172472, 582]; exp_num(6), is_atom(FALSE) 41 #HAGR2: [2773, 172472, 582]; grp_num(8), sfun_num(0); slave_empty(0) keys(T.O_CODE, T.O_NAME, T.CUS_TYPE, T.O_TYPE, T.SET_NAME, T.SET_ID, T.DIM_CODE, T.DIM_OBJ_ID) 42 #PRJT2: [2015, 4081172, 582]; exp_num(8), is_atom(FALSE) 43 #UNION ALL: [2015, 4081172, 582] 44 #PRJT2: [845, 4081171, 582]; exp_num(8), is_atom(FALSE) 45 #HASH RIGHT JOIN2: [845, 4081171, 582]; key_num(2), ret_null(0), KEY(T2.SET_ID=T1.SET_ID AND T2.BASE_DATE=A.BASE_DATE) 46 #CSCN2: [23, 172472, 144]; INDEX33559059(TAXC45200 as T2) 47 #HASH LEFT JOIN2: [239, 4081171, 582]; key_num(2), partition_keys_num(0), ret_null(0), mix(0) KEY(C.O_CODE=T1.O_CODE AND A.BASE_DATE=T1.BASE_DATE) 48 #NEST LOOP INDEX JOIN2: [116, 1717, 582] 49 #SLCT2: [105, 1717, 390]; B.DIM_CODE = 'DIM_CORP' 50 #NEST LOOP INDEX JOIN2: [105, 1717, 390] 51 #PRJT2: [91, 1717, 246]; exp_num(2), is_atom(FALSE) 52 #SLCT2: [91, 1717, 246]; TAWC11111.AWC_QUOTA > var23 53 #BLKUP2: [91, 68827, 246]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111) 54 #SSEK2: [91, 68827, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01'] 55 #BLKUP2: [11, 1, 48]; INDEX33559829(B) 56 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as B), scan_range[A.DIM_OBJ_ID,A.DIM_OBJ_ID] 57 #BLKUP2: [11, 1, 48]; INDEX33558827(C) 58 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[B.O_CODE,B.O_CODE] 59 #CSCN2: [75, 562125, 144]; INDEX33559061(VRG99999 as T1) 60 #PRJT2: [59, 1, 534]; exp_num(8), is_atom(FALSE) 61 #SLCT2: [59, 1, 534]; (A.BASE_DATE = T2.BASE_DATE AND A.DIM_OBJ_ID = B.DIM_OBJ_ID) 62 #NEST LOOP INNER JOIN2: [59, 1, 534]; [with var] 63 #HASH2 INNER JOIN: [40, 119, 288]; KEY_NUM(1); KEY(T2.SET_ID=B.VRG_SET_ID) KEY_NULL_EQU(0) 64 #SLCT2: [24, 1053, 144]; T2.BASE_DATE = '2024-04-01' 65 #CSCN2: [24, 172472, 144]; INDEX33559059(TAXC45200 as T2) 66 #BLKUP2: [13, 11989, 144]; IDX_YHY_24011901_01(B) 67 #SSEK2: [13, 11989, 144]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as B), scan_range['DIM_VRG','DIM_VRG'] 68 #PRJT2: [1, 1, 246]; exp_num(2), is_atom(FALSE) 69 #SLCT2: [1, 1, 246]; (TAWC11111.AWC_QUOTA > var24 AND TAWC11111.BASE_DATE = var11) 70 #BLKUP2: [1, 2, 246]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111) 71 #SSEK2: [1, 2, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var12),('2024-04-01',var12)] 72 #PRJT2: [156751, 16, 1350]; exp_num(9), is_atom(FALSE) 73 #NEST LOOP LEFT JOIN2: [156751, 16, 1350]; join condition( TRUE ) partition_keys_num(0) ret_null(0) 74 #SLCT2: [73, 16, 1302]; A.DIM_OBJ_ID = B.DIM_OBJ_ID 75 #NEST LOOP INNER JOIN2: [73, 16, 1302]; [with var] 76 #PRJT2: [71, 21, 978]; exp_num(4), is_atom(FALSE) 77 #SAGR2: [71, 21, 978]; grp_num(2), sfun_num(1), distinct_flag[1]; slave_empty(0) keys(A.DIM_OBJ_ID, T2.SET_NAME) 78 #SORT3: [71, 21, 978]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0) 79 #INDEX JOIN LEFT JOIN2: [70, 96, 978] join condition(T1.BASE_DATE = '2024-04-01') ret_null(0) 80 #HASH LEFT JOIN2: [69, 12, 978]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(C.O_CODE=T1.O_CODE) 81 #HASH LEFT JOIN2: [64, 12, 834]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.P_CLASS=D.DIM_CODE) 82 #NEST LOOP INDEX JOIN2: [62, 9, 738] 83 #PRJT2: [61, 9, 642]; exp_num(3), is_atom(FALSE) 84 #DISTINCT: [61, 9, 642] 85 #HASH2 INNER JOIN: [60, 9, 642]; KEY_NUM(2); KEY(A.DIM_OBJ_ID=B.DIM_OBJ_ID AND STATS.DIM_OBJ_ID=B.DIM_OBJ_ID) KEY_NULL_EQU(0, 0) 86 #SLCT2: [59, 2, 354]; STATS.DIM_OBJ_ID = A.DIM_OBJ_ID 87 #NEST LOOP INNER JOIN2: [59, 2, 354]; [with var] 88 #BLKUP2: [1, 52, 96]; IDX_YHY_24011901_01(A) 89 #SSEK2: [1, 52, 96]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as A), scan_range['DIM_PORT','DIM_PORT'] 90 #PRJT2: [1, 2, 258]; exp_num(1), is_atom(FALSE) 91 #UNION FOR OR2: [1, 2, 258]; key_num(1), outer_join(-) 92 #SLCT2: [1, 1, 258]; TAWC11111.LIMIT_LINE > var26 93 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111) 94 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)] 95 #SLCT2: [1, 1, 258]; TAWC11111.AWC_QUOTA > var27 96 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111) 97 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)] 98 #CSCN2: [1, 374, 288]; INDEX33559058(DIM_9999 as B) 99 #BLKUP2: [1, 1, 48]; INDEX33558827(C) 100 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[A.CUST_CODE,A.CUST_CODE] 101 #PRJT2: [1, 98, 96]; exp_num(1), is_atom(FALSE) 102 #SORT3: [1, 98, 96]; key_num(2), is_distinct(TRUE), top_flag(0), is_adaptive(0) 103 #CSCN2: [1, 98, 96]; INDEX33559770(T9X2CC) 104 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T1) 105 #SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T1), scan_range['2024-04-01','2024-04-01'] 106 #BLKUP2: [1, 8, 48]; INDEX33559689(T2) 107 #SSEK2: [1, 8, 48]; scan_type(ASC), INDEX33559689(TAXC45200 as T2), scan_range[(T1.SET_ID,min),(T1.SET_ID,max)) 108 #PRJT2: [1, 2, 324]; exp_num(2), is_atom(FALSE) 109 #SSEK2: [1, 2, 324]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111 as A), scan_range[('2024-04-01',var13),('2024-04-01',var13)] 110 #PRJT2: [793, 1, 48]; exp_num(1), is_atom(FALSE) 111 #AAGR2: [793, 1, 48]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 112 #SLCT2: [793, 6453684, 48]; TAWC11111.BASE_DATE <= var29 113 #SSCN: [793, 6385120, 48]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111) 114 #HEAP TABLE: [8, 4, 846]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-) 115 #PRJT2: [8, 4, 846]; exp_num(3), is_atom(FALSE) 116 #UNION FOR OR2: [8, 4, 846]; key_num(1), outer_join(-) 117 #UNION FOR OR2: [5, 3, 846]; key_num(1), outer_join(-) 118 #UNION FOR OR2: [2, 2, 846]; key_num(1), outer_join(-) 119 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896344441.colname=DMTEMPVIEW_896344370.TMPCOL5) KEY_NULL_EQU(0) 120 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 121 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 IS NULL 122 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 123 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896344442.colname=DMTEMPVIEW_896344370.TMPCOL5) KEY_NULL_EQU(0) 124 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 125 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 > '2024-04-01' 126 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 127 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896344370.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0) 128 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 IS NULL 129 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 130 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1' 131 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX) 132 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896344370.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0) 133 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 > '2024-04-01' 134 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 135 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1' 136 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX) 137 #HEAP TABLE: [13, 1, 846]; table_no(1) full(0), mpp_full(0) autoid(1), sites(-) 138 #HASH LEFT JOIN2: [13, 1, 846]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(T2.O_CODE=T5.O_CODE) 139 #INDEX JOIN LEFT JOIN2: [8, 1, 702] ret_null(0) 140 #HASH LEFT JOIN2: [8, 1, 702]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(T1.INST_ID=T3.INST_ID) 141 #NEST LOOP INDEX JOIN2: [7, 1, 558] 142 #SLCT2: [7, 1, 462]; (T1.DATA_SOURCE = 'ADJ' AND T1.TMP_FLAG = '0' AND T1.AWC_TYPE = 'AWC_CREDIT' AND T1.ADJ_BEG_DATE <= '2024-04-01') 143 #CSCN2: [7, 33528, 462]; INDEX33559064(TAWXV999 as T1) 144 #BLKUP2: [1, 1, 48]; INDEX33559829(T2) 145 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T2), scan_range[T1.DIM_OBJ_ID,T1.DIM_OBJ_ID] 146 #SLCT2: [1, 22, 144]; (T3.AWC_TYPE >= 'AWC' AND T3.AWC_TYPE < 'AWD' AND T3.AWC_TYPE LIKE 'AWC_%') 147 #CSCN2: [1, 505, 144]; INDEX33559057(TAWC_RESULT_DETAIL as T3) 148 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T4), scan_range[T3.DIM_OBJ_ID,T3.DIM_OBJ_ID] 149 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T5) 150 #SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T5), scan_range['2024-04-01','2024-04-01'] 151 #SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-) 152 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE) 153 #AAGR2: [1, 1, 96]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 154 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE) 155 #SLCT2: [1, 1, 96]; exp_sfun1 <= var32 156 #SAGR2: [1, 7, 96]; grp_num(1), sfun_num(3), distinct_flag[1,0,0]; slave_empty(0) keys(TN.DIM_OBJ_ID) 157 #SORT3: [1, 7, 96]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0) 158 #SLCT2: [1, 7, 96]; TN.DIM_OBJ_ID = var10 159 #CSCN2: [1, 374, 96]; INDEX33559058(DIM_9999 as TN) --ET LINEID OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE ---------- ---------- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- -------------------- 1 HSCN 1 0% 145 126 2 0 0 0 0 NULL NULL 0 2 PRJT2 1 0% 145 22 4 0 0 0 0 NULL NULL 0 3 SPL2 1 0% 145 151 1 0 0 0 0 NULL NULL 0 4 PRJT2 1 0% 145 16 6 0 0 0 0 NULL NULL 0 5 PRJT2 1 0% 145 29 6 0 0 0 0 NULL NULL 0 6 PRJT2 1 0% 145 40 6 0 0 0 0 NULL NULL 0 7 HSCN 1 0% 145 122 2 0 0 0 0 NULL NULL 0 8 PRJT2 1 0% 145 18 4 0 0 0 0 NULL NULL 0 9 CONSTV 2 0% 139 120 2 0 0 0 0 NULL NULL 0 10 PIPE2 2 0% 139 4 5 0 0 0 0 NULL NULL 0 11 PRJT2 2 0% 139 5 4 0 0 0 0 NULL NULL 0 12 PRJT2 2 0% 139 30 6 0 0 0 0 NULL NULL 0 13 PIPE2 2 0% 139 2 5 0 0 0 0 NULL NULL 0 14 PRJT2 2 0% 139 83 4 0 0 0 0 NULL NULL 0 15 PIPE2 3 0% 132 3 5 0 0 0 0 NULL NULL 0 16 HSCN 3 0% 132 24 2 0 0 0 0 NULL NULL 0 17 HSCN 3 0% 132 20 3 0 0 0 0 NULL NULL 0 18 PRJT2 3 0% 132 101 4 0 0 0 0 NULL NULL 0 19 PRJT2 3 0% 132 26 4 0 0 0 0 NULL NULL 0 20 HSCN 3 0% 132 28 2 0 0 0 0 NULL NULL 0 21 HSCN 3 0% 132 129 2 0 0 0 0 NULL NULL 0 22 UNION_OR2 4 0% 128 118 5 0 0 0 0 NULL NULL 0 23 CONSTV 4 0% 128 124 2 0 0 0 0 NULL NULL 0 24 PRJT2 4 0% 128 115 8 0 0 0 0 NULL NULL 0 25 SLCT2 4 0% 128 121 4 0 0 0 0 NULL NULL 0 26 AAGR2 5 0% 126 6 5 0 0 0 0 NULL NULL 0 27 HSCN 5 0% 126 134 3 0 0 0 0 NULL NULL 0 28 DLCK 6 0% 124 0 2 0 0 0 0 NULL NULL 0 29 SLCT2 6 0% 124 128 4 0 0 0 0 NULL NULL 0 30 PRJT2 7 0% 122 44 26 0 0 0 0 NULL NULL 0 31 SLCT2 7 0% 122 125 4 0 0 0 0 NULL NULL 0 32 PRJT2 10 0% 121 8 90 0 0 0 0 NULL NULL 0 33 SLCT2 11 0% 119 135 2 0 0 0 0 NULL NULL 0 34 SLCT2 11 0% 119 130 4 0 0 0 0 NULL NULL 0 35 SLCT2 16 0% 118 133 4 0 0 0 0 NULL NULL 0 36 UNION_OR2 17 0% 115 117 7 0 0 0 0 NULL NULL 0 37 PRJT2 17 0% 115 10 90 0 0 0 0 NULL NULL 0 38 SLCT2 17 0% 115 86 44 0 0 0 0 NULL NULL 0 39 HRS2 19 0% 113 119 6 224 0 2 0 NULL NULL 0 40 HTAB 19 0% 113 114 6 0 0 0 0 NULL NULL 0 41 UNION_OR2 20 0% 112 116 9 0 0 0 0 NULL NULL 0 42 PRJT2 21 0% 111 33 56 0 0 0 0 NULL NULL 0 43 PRJT2 22 0% 110 108 88 0 0 0 0 NULL NULL 0 44 PRJT2 23 0% 109 32 56 0 0 0 0 NULL NULL 0 45 SSEK2 27 0% 108 89 2 0 0 0 0 NULL NULL 0 46 CSCN2 28 0% 106 103 2 0 0 0 0 NULL NULL 0 47 PRJT2 28 0% 106 90 146 0 0 0 0 NULL NULL 0 48 PRJT2 29 0% 105 76 44 0 0 0 0 NULL NULL 0 49 UNION_OR2 33 0% 104 91 198 0 0 0 0 NULL NULL 0 50 UNION_ALL2 36 0% 102 34 57 0 0 0 0 NULL NULL 0 51 UNION_ALL2 36 0% 102 43 269 0 0 0 0 NULL NULL 0 52 HRS2 39 0% 101 123 5 224 0 2 0 NULL NULL 0 53 NLI2 43 0% 100 87 97 0 0 0 0 NULL NULL 0 54 IJLO2 50 0% 99 139 244 0 0 0 0 NULL NULL 0 55 HTAB 52 0% 98 137 12 0 0 0 0 NULL NULL 0 56 PRJT2 61 0% 97 42 268 0 0 0 0 NULL NULL 0 57 SLCT2 71 0% 95 74 46 0 0 0 0 NULL NULL 0 58 SLCT2 71 0% 95 92 125 0 0 0 0 NULL NULL 0 59 SLCT2 73 0% 94 95 146 0 0 0 0 NULL NULL 0 60 CSCN2 74 0% 93 136 1 0 0 0 0 NULL NULL 0 61 PRJT2 75 0% 92 110 88 0 0 0 0 NULL NULL 0 62 CSCN2 77 0% 90 131 2 0 0 0 0 NULL NULL 0 63 IJI2 77 0% 90 141 260 0 0 0 0 NULL NULL 0 64 SLCT2 88 0% 89 146 6 0 0 0 0 NULL NULL 0 65 PRJT2 89 0% 88 51 400 0 0 0 0 NULL NULL 0 66 PRJT2 96 0% 87 37 462 0 0 0 0 NULL NULL 0 67 BLKUP2 104 0% 86 93 146 0 0 0 0 NULL NULL 0 68 NLI2 116 0% 85 75 89 0 0 0 0 NULL NULL 0 69 SLCT2 121 0% 84 9 90 0 0 0 0 NULL NULL 0 70 CSCN2 122 0% 83 147 3 0 0 0 0 NULL NULL 0 71 NLLO2 124 0% 82 73 90 0 0 0 0 NULL NULL 0 72 SLCT2 134 0% 81 61 244 0 0 0 0 NULL NULL 0 73 CSCN2 136 0% 79 98 3 0 0 0 0 NULL NULL 0 74 BLKUP2 136 0% 79 96 146 0 0 0 0 NULL NULL 0 75 DIST 138 0% 78 84 5 17 0 242 34 NULL NULL 0 76 IJI2 144 0% 77 82 831 0 0 0 0 NULL NULL 0 77 PRJT2 151 0% 76 60 244 0 0 0 0 NULL NULL 0 78 BLKUP2 154 0% 75 88 4 0 0 0 0 NULL NULL 0 79 SORT3 226 0% 74 102 4 49472 0 0 0 NULL NULL 0 80 HAGR2 230 0% 73 27 4 1654 0 64 0 NULL NULL 0 81 NSET2 241 0% 72 1 3 0 0 0 0 NULL NULL 0 82 HAGR2 254 0% 71 19 4 1653 0 46 0 NULL NULL 0 83 HAGR2 259 0% 70 23 4 1654 0 69 0 NULL NULL 0 84 SLCT2 261 0% 69 142 118 0 0 0 0 NULL NULL 0 85 PRJT2 368 0% 68 68 2310 0 0 0 0 NULL NULL 0 86 SSEK2 377 0% 67 94 73 0 0 0 0 NULL NULL 0 87 PRJT2 391 0% 66 72 46 0 0 0 0 NULL NULL 0 88 IJI2 409 0% 65 48 2410 0 0 0 0 NULL NULL 0 89 SSEK2 473 0% 64 150 13 0 0 0 0 NULL NULL 0 90 SLCT2 476 0% 63 13 96 0 0 0 0 NULL NULL 0 91 BLKUP2 486 0% 62 144 340 0 0 0 0 NULL NULL 0 92 SSEK2 491 0% 61 105 13 0 0 0 0 NULL NULL 0 93 SSEK2 560 0.01% 60 97 73 0 0 0 0 NULL NULL 0 94 PRJT2 603 0.01% 59 35 12 0 0 0 0 NULL NULL 0 95 SSEK2 646 0.01% 58 148 155 0 0 0 0 NULL NULL 0 96 NLI2 699 0.01% 57 62 1291 0 0 0 0 NULL NULL 0 97 SSEK2 774 0.01% 56 145 170 0 0 0 0 NULL NULL 0 98 HAGR2 869 0.01% 55 41 137 1668 0 424 0 NULL NULL 0 99 IJI2 884 0.01% 54 50 5424 0 0 0 0 NULL NULL 0 100 SSEK2 906 0.01% 53 109 44 0 0 0 0 NULL NULL 0 101 HRO2 916 0.01% 52 25 8 11950 0 64 0 NULL NULL 0 102 BLKUP2 929 0.01% 51 99 1104 0 0 0 0 NULL NULL 0 103 HRO2 944 0.01% 50 17 8 11950 0 46 0 NULL NULL 0 104 HI3 957 0.01% 49 85 28 16046 0 21 0 NULL NULL 0 105 HLS2 971 0.01% 48 132 5 16558 0 8 0 NULL NULL 0 106 HRO2 982 0.01% 47 21 8 11950 0 69 0 NULL NULL 0 107 SLCT2 1025 0.01% 46 52 431 0 0 0 0 NULL NULL 0 108 HLS2 1088 0.01% 45 127 6 16558 0 73 0 NULL NULL 0 109 AFUN 1126 0.01% 44 11 90 0 0 0 0 NULL NULL 0 110 SLCT2 1326 0.01% 43 49 2345 0 0 0 0 NULL NULL 0 111 SSEK2 1540 0.01% 42 100 552 0 0 0 0 NULL NULL 0 112 SSEK2 1649 0.01% 41 67 41 0 0 0 0 NULL NULL 0 113 HAGR2 1751 0.02% 40 31 31 1669 0 444 1 NULL NULL 0 114 SLCT2 1759 0.02% 39 69 3223 0 0 0 0 NULL NULL 0 115 SSEK2 1867 0.02% 38 15 48 0 0 0 0 NULL NULL 0 116 SLCT2 1901 0.02% 37 64 585 0 0 0 0 NULL NULL 0 117 HRO2 2055 0.02% 36 7 51 11950 0 13017 158 NULL NULL 0 118 HI3 2124 0.02% 35 63 60 16046 0 1047 0 NULL NULL 0 119 SAGR2 2598 0.02% 34 77 92 0 0 0 0 NULL NULL 0 120 SSEK2 2658 0.02% 33 107 524 0 0 0 0 NULL NULL 0 121 HLO2 3097 0.03% 32 81 281 16558 0 13 0 NULL NULL 275 122 HLO2 3193 0.03% 31 140 92 16558 0 84 0 NULL NULL 0 123 HLO2 3272 0.03% 30 138 108 12462 0 65 0 NULL NULL 29 124 BLKUP2 3375 0.03% 29 57 2408 0 0 0 0 NULL NULL 0 125 HLO2 3396 0.03% 28 80 21 12462 0 20 0 NULL NULL 275 126 SSEK2 4182 0.04% 27 58 1204 0 0 0 0 NULL NULL 0 127 IJLO2 4622 0.04% 26 79 807 0 0 0 0 NULL NULL 0 128 BLKUP2 7286 0.07% 25 70 4136 0 0 0 0 NULL NULL 0 129 SORT3 7495 0.07% 24 12 93 59392 0 0 0 NULL NULL 0 130 BLKUP2 7522 0.07% 23 149 26 0 0 0 0 NULL NULL 0 131 BLKUP2 7943 0.07% 22 104 26 0 0 0 0 NULL NULL 0 132 BLKUP2 8173 0.07% 21 55 6964 0 0 0 0 NULL NULL 0 133 SSEK2 9143 0.08% 20 54 231 0 0 0 0 NULL NULL 0 134 SSEK2 9206 0.08% 19 39 231 0 0 0 0 NULL NULL 0 135 SSEK2 10375 0.09% 18 71 2068 0 0 0 0 NULL NULL 0 136 SORT3 10605 0.1% 17 78 347 116736 0 0 0 NULL NULL 0 137 CSCN2 11154 0.1% 16 143 113 0 0 0 0 NULL NULL 0 138 HI3 12422 0.11% 15 36 240 40622 0 27630 698 NULL NULL 62610 139 SSEK2 14431 0.13% 14 56 3482 0 0 0 0 NULL NULL 0 140 BLKUP2 26941 0.24% 13 66 82 0 0 0 0 NULL NULL 0 141 BLKUP2 33447 0.3% 12 14 96 0 0 0 0 NULL NULL 0 142 CSCN2 36753 0.33% 11 46 580 0 0 0 0 NULL NULL 0 143 CSCN2 37268 0.34% 10 65 580 0 0 0 0 NULL NULL 0 144 HRO2 37567 0.34% 9 45 606 73971 0 161179 12340 NULL NULL 0 145 HLO2 41093 0.37% 8 47 2503 12718 0 303 0 NULL NULL 597 146 BLKUP2 44604 0.4% 7 106 1048 0 0 0 0 NULL NULL 0 147 CSCN2 124217 1.12% 6 59 1887 0 0 0 0 NULL NULL 0 148 BLKUP2 156910 1.41% 5 38 462 0 0 0 0 NULL NULL 0 149 BLKUP2 166844 1.5% 4 53 462 0 0 0 0 NULL NULL 0 150 AAGR2 1555430 14% 3 111 473352 0 0 0 0 NULL NULL 0 151 SLCT2 1986956 17.89% 2 112 946616 0 0 0 0 NULL NULL 0 152 SSCN 6673379 60.09% 1 113 473308 0 0 0 0 NULL NULL 0 152 rows got
只返回一條資料 Success, cost 11 second(s)663 millsecond(s). 執行時間 11秒左右。
平時簡單的SQL基本不用看執行計劃大概都能知道哪裡慢的，但是像這種看著是挺複雜的，也不知道慢在哪，DM執行計劃就算難看也要分析了。🤣
從 DM 的 ET 工具可以看到本條SQL最慢的在 152行，SSCN 佔了 整條SQL 60%的時間，對應執行計劃中 #SSCN: [793, 6385120, 48]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
SSCN 是什麼意思各位讀者同學感興趣的可以自己去 DM 官網查查，反正我不知道，但是我知道哪裡慢，下面這段很慢，要 11秒。
SELECT T.O_CODE AS ID, T.O_NAME AS CORP_NAME, (SELECT MAX(PARENTID) PARENTID FROM (SELECT CASE MAX(DIM_OBJ_ID) WHEN NULL THEN '' ELSE MAX(TN.CUST_CODE) END PARENTID FROM DIM_9999 TN WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID GROUP BY TN.DIM_OBJ_ID HAVING COUNT(DISTINCT(CUST_CODE)) <= 1) A) PARENTID, T.O_CODE, T.BASE_DATE, T.DIM_OBJ_ID, T.CUS_TYPE, T.O_NAME AS CUS_NAME, T.DIM_CODE, T.IS_NEW_DATE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS LIMIT_LINE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS AWC_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(OCC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS OCC_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_RATE, 0) * 100 ELSE 0 END), 'fm999999999999999990.00') AS LIMIT_LINE_RATE, TO_CHAR(GREATEST(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_ABLE, 0) ELSE 0 END) / 10000, 0), 'fm999999999999999990.00') AS LIMIT_LINE_ABLE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'CE_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS CE_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS ELUL_QUOTA, SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA_ABLE, 0) ELSE 0 END) AS AWC_QUOTA_ABLE, t.set_id, t.set_name FROM (SELECT A.AWC_TYPE, C.O_CODE, C.O_NAME, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, C.CUS_TYPE, 'DIM_CORP' AS DIM_CODE, NULL AS AWC_QUOTA_ABLE, '' AS IS_NEW_DATE, c.set_id, c.set_name FROM (SELECT A.AWC_TYPE, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID FROM TAWC11111 A WHERE A.BASE_DATE = '2024-04-01') A INNER JOIN CORP11111 C ON A.DIM_OBJ_ID = C.DIM_OBJ_ID WHERE A.BASE_DATE = '2024-04-01' UNION ALL SELECT A.AWC_TYPE, B.CUST_CODE AS O_CODE, B.O_NAME || '(組合)' AS NAME, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, B.CUS_TYPE, 'DIM_PORT' AS DIM_CODE, A.AWC_QUOTA_ABLE, CASE WHEN C.MAX_BASE_DATE = '2024-04-01' THEN '1' ELSE '0' END AS IS_NEW_DATE, '' AS set_id, '' AS set_name FROM (SELECT A.AWC_TYPE, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, A.AWC_QUOTA_ABLE FROM TAWC11111 A WHERE A.BASE_DATE = '2024-04-01') A INNER JOIN PORT_11112 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE FROM TAWC11111 WHERE BASE_DATE <= TO_CHAR(SYSDATE, 'YYYY-MM-DD')) C ON 1 = 1 WHERE A.BASE_DATE = '2024-04-01' AND B.DIM_CODE = 'DIM_PORT') T GROUP BY T.O_CODE, T.O_NAME, T.CUS_TYPE, T.BASE_DATE, T.DIM_OBJ_ID, T.DIM_CODE, T.IS_NEW_DATE, t.set_id, t.set_name
看到現在應該會有槓精同學問我，那這裡為什麼會慢？是不是寫SQL的研發太垃圾了？還是DM資料庫太垃圾了？
可以明確回答，因為我看不懂 DM 的執行計劃所以不知道上面為什麼慢，不知道CBO做了什麼查詢轉換這些動作。
但是可以明確的是，寫SQL開發不垃圾，能寫出這麼複雜的統計SQL也是很厲害的。
但是DM確實有點拉胯，執行計劃讓人看不懂這就說不過去了，DM的執行計劃是我目前遇到資料庫中最難看懂的計劃，就像上面的SQL，想分析些CBO做了什麼查詢轉換都分析不了，因為看不懂，只能靠猜。
SQL改寫最佳化：
WITH CORP11111 AS (SELECT T.O_CODE, T.O_NAME, T.CUS_TYPE, T.O_TYPE, T.SET_NAME, T.SET_ID, T.DIM_CODE, T.DIM_OBJ_ID FROM (SELECT C.O_CODE, C.CUS_TYPE, C.O_TYPE, C.O_NAME, T2.SET_NAME, T2.SET_ID, B.DIM_CODE, B.DIM_OBJ_ID, '' AS D_DEPT, '' AS D_NAME, '' AS D_BIZLINE, '' AS P_CLASS, '' AS P_CLASS_NAME FROM (SELECT DIM_OBJ_ID, BASE_DATE, LIMIT_LINE, AWC_QUOTA, OCC_QUOTA, LIMIT_LINE_ABLE, LIMIT_LINE_RATE FROM TAWC11111 WHERE BASE_DATE = '2024-04-01') A INNER JOIN TAWC22222 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID INNER JOIN T2RRRR C ON B.O_CODE = C.O_CODE LEFT JOIN VRG99999 T1 ON C.O_CODE = T1.O_CODE AND T1.BASE_DATE = A.BASE_DATE LEFT JOIN TAXC45200 T2 ON T1.SET_ID = T2.SET_ID AND T2.BASE_DATE = A.BASE_DATE WHERE A.BASE_DATE = '2024-04-01' AND B.DIM_CODE = 'DIM_CORP' AND A.AWC_QUOTA > 0 UNION ALL SELECT T2.SET_ID AS O_CODE, '集團' AS CUS_TYPE, 'group' AS O_TYPE, '' AS O_NAME, T2.SET_NAME, T2.SET_ID, B.DIM_CODE, B.DIM_OBJ_ID, '' AS D_DEPT, '' AS D_NAME, '' AS D_BIZLINE, '' AS P_CLASS, '' AS P_CLASS_NAME FROM (SELECT DIM_OBJ_ID, BASE_DATE, LIMIT_LINE, AWC_QUOTA, OCC_QUOTA, LIMIT_LINE_ABLE, LIMIT_LINE_RATE FROM TAWC11111 WHERE BASE_DATE = '2024-04-01') A INNER JOIN TAWC22222 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID INNER JOIN TAXC45200 T2 ON B.VRG_SET_ID = T2.SET_ID AND A.BASE_DATE = T2.BASE_DATE WHERE A.BASE_DATE = '2024-04-01' AND B.DIM_CODE = 'DIM_VRG' AND A.AWC_QUOTA > 0) T WHERE 1 = 1 GROUP BY O_CODE, O_NAME, CUS_TYPE, O_TYPE, SET_NAME, SET_ID, DIM_CODE, DIM_OBJ_ID) , PORT_11112 AS (SELECT A.DIM_OBJ_ID AS CUST_CODE, '' CUS_TYPE, '2' O_TYPE, WM_CONCAT(DISTINCT (C.O_NAME)) O_NAME, WM_CONCAT(DISTINCT (A.D_DEPT)) D_DEPT, WM_CONCAT(DISTINCT (A.D_NAME)) D_NAME, WM_CONCAT(DISTINCT (A.D_BIZLINE)) D_BIZLINE, WM_CONCAT(DISTINCT (A.P_CLASS)) P_CLASS, WM_CONCAT(DISTINCT (DIM_CLASS_NAME)) P_CLASS_NAME, 'DIM_PORT' DIM_CODE, A.DIM_OBJ_ID, T2.SET_NAME FROM (SELECT DISTINCT A.DIM_OBJ_ID, A.DIM_CODE, B.CUST_CODE, B.D_DEPT, B.D_NAME, B.D_BIZLINE, B.P_CLASS FROM (SELECT AWC_QUOTA, LIMIT_LINE, BASE_DATE, DIM_OBJ_ID, OCC_QUOTA, LIMIT_LINE_ABLE, LIMIT_LINE_RATE FROM TAWC11111 WHERE BASE_DATE = '2024-04-01') STATS INNER JOIN TAWC22222 A ON STATS.DIM_OBJ_ID = A.DIM_OBJ_ID INNER JOIN DIM_9999 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID WHERE A.DIM_CODE = 'DIM_PORT' AND STATS.BASE_DATE = '2024-04-01' AND (STATS.LIMIT_LINE > 0 OR STATS.AWC_QUOTA > 0)) A INNER JOIN T2RRRR C ON A.CUST_CODE = C.O_CODE LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME FROM T9X2CC ORDER BY DIM_CODE ASC) D ON A.P_CLASS = D.DIM_CODE LEFT JOIN VRG99999 T1 ON C.O_CODE = T1.O_CODE AND T1.BASE_DATE = '2024-04-01' LEFT JOIN TAXC45200 T2 ON T1.SET_ID = T2.SET_ID AND T1.BASE_DATE = '2024-04-01' WHERE 1 = 1 GROUP BY A.DIM_OBJ_ID, T2.SET_NAME) , CREDIT9144 AS (SELECT DECODE(T1.ADJ_TYPE, '調減', -1, 1) * T1.ADJ_LINE AS ADJ_LINE, T2.O_CODE, T1.INST_ID, T4.DIM_OBJ_ID, T5.SET_ID FROM TAWXV999 T1 INNER JOIN TAWC22222 T2 ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID LEFT JOIN TAWC_RESULT_DETAIL T3 ON T1.INST_ID = T3.INST_ID AND T3.AWC_TYPE LIKE 'AWC_%' LEFT JOIN TAWC22222 T4 ON T3.DIM_OBJ_ID = T4.DIM_OBJ_ID LEFT JOIN VRG99999 T5 ON T2.O_CODE = T5.O_CODE AND T5.BASE_DATE = '2024-04-01' WHERE T1.DATA_SOURCE = 'ADJ' AND T1.TMP_FLAG = '0' AND T1.AWC_TYPE = 'AWC_CREDIT' AND (T1.REMARK = '初始化-佔用信用債' OR T1.REMARK = '釋放信用債' OR T1.INST_ID IN (SELECT INST_ID FROM CFLSSX WHERE AWC_CREDIT_FLAG = '1')) AND T1.ADJ_BEG_DATE <= '2024-04-01' AND (T1.ADJ_END_DATE IS NULL OR T1.ADJ_END_DATE > '2024-04-01')) , X AS ( SELECT * FROM TAWC11111 WHERE BASE_DATE = '2024-04-01' and TO_CHAR(SYSDATE, 'YYYY-MM-DD') ) SELECT COUNT(1) FROM ( SELECT T1.*, TO_CHAR(DECODE(T1.DIM_CODE, 'DIM_PORT', T3.ADJ_LINE, DECODE(CUS_TYPE, '集團', T4.ADJ_LINE, T2.ADJ_LINE)) / 10000, 'fm999999999999999990.00') AS ADJ_LINE FROM ( SELECT T.O_CODE AS ID, T.O_NAME AS CORP_NAME, (SELECT MAX(PARENTID) PARENTID FROM (SELECT CASE MAX(DIM_OBJ_ID) WHEN NULL THEN '' ELSE MAX(TN.CUST_CODE) END PARENTID FROM DIM_9999 TN WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID GROUP BY TN.DIM_OBJ_ID HAVING COUNT(DISTINCT (CUST_CODE)) <= 1) A) PARENTID, T.O_CODE, T.BASE_DATE, T.DIM_OBJ_ID, T.CUS_TYPE, T.O_NAME AS CUS_NAME, T.DIM_CODE, T.IS_NEW_DATE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS LIMIT_LINE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS AWC_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(OCC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS OCC_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_RATE, 0) * 100 ELSE 0 END), 'fm999999999999999990.00') AS LIMIT_LINE_RATE, TO_CHAR(GREATEST(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_ABLE, 0) ELSE 0 END) / 10000, 0), 'fm999999999999999990.00') AS LIMIT_LINE_ABLE, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'CE_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS CE_QUOTA, TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000, 'fm999999999999999990.00') AS ELUL_QUOTA, SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA_ABLE, 0) ELSE 0 END) AS AWC_QUOTA_ABLE, t.set_id, t.set_name FROM ( SELECT A.AWC_TYPE, C.O_CODE, C.O_NAME, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, C.CUS_TYPE, 'DIM_CORP' AS DIM_CODE, NULL AS AWC_QUOTA_ABLE, '' AS IS_NEW_DATE, c.set_id, c.set_name FROM (SELECT A.AWC_TYPE, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID FROM X A WHERE A.BASE_DATE = '2024-04-01') A INNER JOIN CORP11111 C ON A.DIM_OBJ_ID = C.DIM_OBJ_ID WHERE A.BASE_DATE = '2024-04-01' UNION ALL SELECT A.AWC_TYPE, B.CUST_CODE AS O_CODE, B.O_NAME || '(組合)' AS NAME, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, B.CUS_TYPE, 'DIM_PORT' AS DIM_CODE, A.AWC_QUOTA_ABLE, CASE WHEN C.MAX_BASE_DATE = '2024-04-01' THEN '1' ELSE '0' END AS IS_NEW_DATE, '' AS set_id, '' AS set_name FROM ( SELECT A.AWC_TYPE, A.AWC_QUOTA, A.OCC_QUOTA, A.LIMIT_LINE_ABLE, A.LIMIT_LINE_RATE, A.LIMIT_LINE, A.BASE_DATE, A.DIM_OBJ_ID, A.AWC_QUOTA_ABLE FROM X A WHERE A.BASE_DATE = '2024-04-01' ) A INNER JOIN PORT_11112 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE FROM X WHERE BASE_DATE <= TO_CHAR(SYSDATE, 'YYYY-MM-DD') ) C ON 1 = 1 WHERE A.BASE_DATE = '2024-04-01' AND B.DIM_CODE = 'DIM_PORT' ) T GROUP BY T.O_CODE, T.O_NAME, T.CUS_TYPE, T.BASE_DATE, T.DIM_OBJ_ID, T.DIM_CODE, T.IS_NEW_DATE, t.set_id, t.set_name ) T1 LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, O_CODE FROM CREDIT9144 GROUP BY O_CODE) T2 ON T1.O_CODE = T2.O_CODE LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, DIM_OBJ_ID FROM CREDIT9144 GROUP BY DIM_OBJ_ID) T3 ON T1.DIM_OBJ_ID = T3.DIM_OBJ_ID LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, SET_ID FROM CREDIT9144 GROUP BY SET_ID) T4 ON T1.O_CODE = T4.SET_ID) T1 LEFT JOIN (SELECT DIM_OBJ_ID, WM_CONCAT(DISTINCT (TC.O_NAME)) || '(組合)' AS PORT_NAME, WM_CONCAT(DISTINCT (TD1.SD_NAME || '-' || TD2.SD_NAME || '-' || TD3.SD_NAME)) PORT_DEPT, WM_CONCAT(DISTINCT (T3.DIM_CLASS_NAME)) PORT_CLASS FROM DIM_9999 T1 LEFT JOIN TSYS_DEPT TD1 ON T1.D_CORP = TD1.SD_ID LEFT JOIN TSYS_DEPT TD2 ON T1.D_DEPT = TD2.SD_ID LEFT JOIN TSYS_DEPT TD3 ON T1.D_BIZLINE = TD3.SD_ID LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME FROM T9X2CC) T3 ON T1.P_CLASS = T3.DIM_CODE LEFT JOIN T2RRRR TC ON T1.CUST_CODE = TC.O_CODE GROUP BY DIM_OBJ_ID) T2 ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID LEFT JOIN (SELECT * FROM (SELECT T.*, ROW_NUMBER() OVER ( PARTITION BY T.O_CODE ORDER BY T.BASEDATE DESC, T.BEG_DATE DESC, IMP_TIME DESC) AS SN FROM TCRT_RESULT T WHERE T.TMP_FLAG = '0' AND T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01') C WHERE C.SN = 1) T3 ON T1.O_CODE = T3.O_CODE;
改寫後執行計劃：
1 #NSET2: [17484, 1, 4470] 2 #PIPE2: [17484, 1, 4470] 3 #PIPE2: [17483, 1, 4470] 4 #PIPE2: [17470, 1, 4470] 5 #PRJT2: [17461, 1, 4470]; exp_num(1), is_atom(FALSE) 6 #AAGR2: [17461, 1, 4470]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 7 #HASH RIGHT JOIN2: [17461, 172479, 4470]; key_num(1), ret_null(0), KEY(T3.O_CODE=T1.O_CODE) 8 #PRJT2: [24, 331, 528]; exp_num(1), is_atom(FALSE) 9 #SLCT2: [24, 331, 528]; C.SN = var15 10 #PRJT2: [24, 13278, 528]; exp_num(2), is_atom(FALSE) 11 #AFUN: [24, 13278, 528]; afun_num(1); partition_num(1)[T.O_CODE]; order_num(3)[T.BASEDATE, T.BEG_DATE, T.IMP_TIME] 12 #SORT3: [24, 13278, 528]; key_num(4), is_distinct(FALSE), top_flag(0), is_adaptive(0) 13 #SLCT2: [24, 13278, 528]; (T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01') 14 #BLKUP2: [24, 13619, 528]; TCRT_RESULT_TMPFLAG(T) 15 #SSEK2: [24, 13619, 528]; scan_type(ASC), TCRT_RESULT_TMPFLAG(TCRT_RESULT as T), scan_range['0','0'] 16 #PRJT2: [17340, 172479, 3942]; exp_num(1), is_atom(FALSE) 17 #HASH RIGHT JOIN2: [17340, 172479, 3942]; key_num(1), ret_null(0), KEY(T4.SET_ID=T1.O_CODE) 18 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE) 19 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.SET_ID) 20 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0) 21 #HASH RIGHT JOIN2: [17260, 172479, 3096]; key_num(1), ret_null(0), KEY(T3.DIM_OBJ_ID=T1.DIM_OBJ_ID) 22 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE) 23 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.DIM_OBJ_ID) 24 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0) 25 #HASH RIGHT JOIN2: [17198, 172479, 2250]; key_num(1), ret_null(0), KEY(T2.O_CODE=T1.O_CODE) 26 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE) 27 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.O_CODE) 28 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0) 29 #PRJT2: [17154, 172479, 1404]; exp_num(2), is_atom(FALSE) 30 #PRJT2: [17154, 172479, 1404]; exp_num(2), is_atom(FALSE) 31 #HAGR2: [17154, 172479, 1404]; grp_num(9), sfun_num(0); slave_empty(0) keys(DMTEMPVIEW_896943061.TMPCOL0, DMTEMPVIEW_896943061.TMPCOL1, DMTEMPVIEW_896943061.TMPCOL2, DMTEMPVIEW_896943061.TMPCOL3, DMTEMPVIEW_896943061.TMPCOL4, DMTEMPVIEW_896943061.TMPCOL5, DMTEMPVIEW_896943061.TMPCOL6, DMTEMPVIEW_896943061.TMPCOL7, DMTEMPVIEW_896943061.TMPCOL8) 32 #PRJT2: [17060, 172479, 1404]; exp_num(9), is_atom(FALSE) 33 #PRJT2: [17060, 172479, 1404]; exp_num(9), is_atom(FALSE) 34 #UNION ALL: [17060, 172479, 1404] 35 #PRJT2: [2953, 172471, 1404]; exp_num(9), is_atom(FALSE) 36 #HASH2 INNER JOIN: [2953, 172471, 1404]; KEY_NUM(1); KEY(A.DIM_OBJ_ID=C.DIM_OBJ_ID) KEY_NULL_EQU(0) 37 #PRJT2: [152, 3480, 822]; exp_num(2), is_atom(FALSE) 38 #PRJT2: [152, 3480, 822]; exp_num(2), is_atom(FALSE) 39 #SLCT2: [152, 3480, 822]; TAWC11111.BASE_DATE <= var24 40 #BLKUP2: [152, 68827, 822]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111) 41 #SSEK2: [152, 68827, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01'] 42 #PRJT2: [2774, 172472, 582]; exp_num(6), is_atom(FALSE) 43 #HAGR2: [2774, 172472, 582]; grp_num(8), sfun_num(0); slave_empty(0) keys(T.O_CODE, T.O_NAME, T.CUS_TYPE, T.O_TYPE, T.SET_NAME, T.SET_ID, T.DIM_CODE, T.DIM_OBJ_ID) 44 #PRJT2: [2016, 4084117, 582]; exp_num(8), is_atom(FALSE) 45 #UNION ALL: [2016, 4084117, 582] 46 #PRJT2: [846, 4084116, 582]; exp_num(8), is_atom(FALSE) 47 #HASH RIGHT JOIN2: [846, 4084116, 582]; key_num(2), ret_null(0), KEY(T2.SET_ID=T1.SET_ID AND T2.BASE_DATE=A.BASE_DATE) 48 #CSCN2: [23, 172472, 144]; INDEX33559059(TAXC45200 as T2) 49 #HASH LEFT JOIN2: [239, 4084116, 582]; key_num(2), partition_keys_num(0), ret_null(0), mix(0) KEY(C.O_CODE=T1.O_CODE AND A.BASE_DATE=T1.BASE_DATE) 50 #NEST LOOP INDEX JOIN2: [116, 1718, 582] 51 #SLCT2: [105, 1718, 390]; B.DIM_CODE = 'DIM_CORP' 52 #NEST LOOP INDEX JOIN2: [105, 1718, 390] 53 #PRJT2: [91, 1718, 246]; exp_num(2), is_atom(FALSE) 54 #SLCT2: [91, 1718, 246]; TAWC11111.AWC_QUOTA > var25 55 #BLKUP2: [91, 68827, 246]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111) 56 #SSEK2: [91, 68827, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01'] 57 #BLKUP2: [11, 1, 48]; INDEX33559829(B) 58 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as B), scan_range[A.DIM_OBJ_ID,A.DIM_OBJ_ID] 59 #BLKUP2: [11, 1, 48]; INDEX33558827(C) 60 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[B.O_CODE,B.O_CODE] 61 #CSCN2: [75, 562125, 144]; INDEX33559061(VRG99999 as T1) 62 #PRJT2: [59, 1, 534]; exp_num(8), is_atom(FALSE) 63 #SLCT2: [59, 1, 534]; (A.BASE_DATE = T2.BASE_DATE AND A.DIM_OBJ_ID = B.DIM_OBJ_ID) 64 #NEST LOOP INNER JOIN2: [59, 1, 534]; [with var] 65 #HASH2 INNER JOIN: [40, 119, 288]; KEY_NUM(1); KEY(T2.SET_ID=B.VRG_SET_ID) KEY_NULL_EQU(0) 66 #SLCT2: [24, 1053, 144]; T2.BASE_DATE = '2024-04-01' 67 #CSCN2: [24, 172472, 144]; INDEX33559059(TAXC45200 as T2) 68 #BLKUP2: [13, 11990, 144]; IDX_YHY_24011901_01(B) 69 #SSEK2: [13, 11990, 144]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as B), scan_range['DIM_VRG','DIM_VRG'] 70 #PRJT2: [1, 1, 246]; exp_num(2), is_atom(FALSE) 71 #SLCT2: [1, 1, 246]; (TAWC11111.AWC_QUOTA > var26 AND TAWC11111.BASE_DATE = var11) 72 #BLKUP2: [1, 2, 246]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111) 73 #SSEK2: [1, 2, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var12),('2024-04-01',var12)] 74 #PRJT2: [14024, 8, 2622]; exp_num(9), is_atom(FALSE) 75 #NEST LOOP LEFT JOIN2: [14024, 8, 2622]; join condition( TRUE ) partition_keys_num(0) ret_null(0) 76 #SLCT2: [74, 8, 1800]; A.DIM_OBJ_ID = B.DIM_OBJ_ID 77 #NEST LOOP INNER JOIN2: [74, 8, 1800]; [with var] 78 #PRJT2: [71, 21, 978]; exp_num(4), is_atom(FALSE) 79 #SAGR2: [71, 21, 978]; grp_num(2), sfun_num(1), distinct_flag[1]; slave_empty(0) keys(A.DIM_OBJ_ID, T2.SET_NAME) 80 #SORT3: [71, 21, 978]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0) 81 #INDEX JOIN LEFT JOIN2: [70, 96, 978] join condition(T1.BASE_DATE = '2024-04-01') ret_null(0) 82 #HASH LEFT JOIN2: [69, 12, 978]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(C.O_CODE=T1.O_CODE) 83 #HASH LEFT JOIN2: [64, 12, 834]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(A.P_CLASS=D.DIM_CODE) 84 #NEST LOOP INDEX JOIN2: [62, 9, 738] 85 #PRJT2: [61, 9, 642]; exp_num(3), is_atom(FALSE) 86 #DISTINCT: [61, 9, 642] 87 #HASH2 INNER JOIN: [60, 9, 642]; KEY_NUM(2); KEY(A.DIM_OBJ_ID=B.DIM_OBJ_ID AND STATS.DIM_OBJ_ID=B.DIM_OBJ_ID) KEY_NULL_EQU(0, 0) 88 #SLCT2: [59, 2, 354]; STATS.DIM_OBJ_ID = A.DIM_OBJ_ID 89 #NEST LOOP INNER JOIN2: [59, 2, 354]; [with var] 90 #BLKUP2: [1, 52, 96]; IDX_YHY_24011901_01(A) 91 #SSEK2: [1, 52, 96]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as A), scan_range['DIM_PORT','DIM_PORT'] 92 #PRJT2: [1, 2, 258]; exp_num(1), is_atom(FALSE) 93 #UNION FOR OR2: [1, 2, 258]; key_num(1), outer_join(-) 94 #SLCT2: [1, 1, 258]; TAWC11111.LIMIT_LINE > var28 95 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111) 96 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)] 97 #SLCT2: [1, 1, 258]; TAWC11111.AWC_QUOTA > var29 98 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111) 99 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)] 100 #CSCN2: [1, 374, 288]; INDEX33559058(DIM_9999 as B) 101 #BLKUP2: [1, 1, 48]; INDEX33558827(C) 102 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[A.CUST_CODE,A.CUST_CODE] 103 #PRJT2: [1, 98, 96]; exp_num(1), is_atom(FALSE) 104 #SORT3: [1, 98, 96]; key_num(2), is_distinct(TRUE), top_flag(0), is_adaptive(0) 105 #CSCN2: [1, 98, 96]; INDEX33559770(T9X2CC) 106 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T1) 107 #SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T1), scan_range['2024-04-01','2024-04-01'] 108 #BLKUP2: [1, 8, 48]; INDEX33559689(T2) 109 #SSEK2: [1, 8, 48]; scan_type(ASC), INDEX33559689(TAXC45200 as T2), scan_range[(T1.SET_ID,min),(T1.SET_ID,max)) 110 #PRJT2: [1, 1, 822]; exp_num(2), is_atom(FALSE) 111 #PRJT2: [1, 1, 822]; exp_num(2), is_atom(FALSE) 112 #SLCT2: [1, 1, 822]; TAWC11111.BASE_DATE <= var31 113 #SSEK2: [1, 2, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var13),('2024-04-01',var13)] 114 #PRJT2: [152, 1, 822]; exp_num(1), is_atom(FALSE) 115 #AAGR2: [152, 1, 822]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 116 #PRJT2: [152, 69616, 822]; exp_num(1), is_atom(FALSE) 117 #SLCT2: [152, 69616, 822]; TAWC11111.BASE_DATE <= var33 118 #SSEK2: [152, 68827, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01',exp11] 119 #HEAP TABLE: [8, 4, 846]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-) 120 #PRJT2: [8, 4, 846]; exp_num(3), is_atom(FALSE) 121 #UNION FOR OR2: [8, 4, 846]; key_num(1), outer_join(-) 122 #UNION FOR OR2: [5, 3, 846]; key_num(1), outer_join(-) 123 #UNION FOR OR2: [2, 2, 846]; key_num(1), outer_join(-) 124 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896943136.colname=DMTEMPVIEW_896943065.TMPCOL5) KEY_NULL_EQU(0) 125 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 126 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 IS NULL 127 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 128 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896943137.colname=DMTEMPVIEW_896943065.TMPCOL5) KEY_NULL_EQU(0) 129 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1), 130 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 > '2024-04-01' 131 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 132 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896943065.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0) 133 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 IS NULL 134 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 135 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1' 136 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX) 137 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896943065.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0) 138 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 > '2024-04-01' 139 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1) 140 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1' 141 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX) 142 #HEAP TABLE: [13, 1, 846]; table_no(1) full(0), mpp_full(0) autoid(1), sites(-) 143 #HASH LEFT JOIN2: [13, 1, 846]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(T2.O_CODE=T5.O_CODE) 144 #INDEX JOIN LEFT JOIN2: [8, 1, 702] ret_null(0) 145 #HASH LEFT JOIN2: [8, 1, 702]; key_num(1), partition_keys_num(0), ret_null(0), mix(0) KEY(T1.INST_ID=T3.INST_ID) 146 #NEST LOOP INDEX JOIN2: [7, 1, 558] 147 #SLCT2: [7, 1, 462]; (T1.DATA_SOURCE = 'ADJ' AND T1.TMP_FLAG = '0' AND T1.AWC_TYPE = 'AWC_CREDIT' AND T1.ADJ_BEG_DATE <= '2024-04-01') 148 #CSCN2: [7, 33528, 462]; INDEX33559064(TAWXV999 as T1) 149 #BLKUP2: [1, 1, 48]; INDEX33559829(T2) 150 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T2), scan_range[T1.DIM_OBJ_ID,T1.DIM_OBJ_ID] 151 #SLCT2: [1, 22, 144]; (T3.AWC_TYPE >= 'AWC' AND T3.AWC_TYPE < 'AWD' AND T3.AWC_TYPE LIKE 'AWC_%') 152 #CSCN2: [1, 505, 144]; INDEX33559057(TAWC_RESULT_DETAIL as T3) 153 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T4), scan_range[T3.DIM_OBJ_ID,T3.DIM_OBJ_ID] 154 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T5) 155 #SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T5), scan_range['2024-04-01','2024-04-01'] 156 #SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-) 157 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE) 158 #AAGR2: [1, 1, 96]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0) 159 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE) 160 #SLCT2: [1, 1, 96]; exp_sfun1 <= var36 161 #SAGR2: [1, 7, 96]; grp_num(1), sfun_num(3), distinct_flag[1,0,0]; slave_empty(0) keys(TN.DIM_OBJ_ID) 162 #SORT3: [1, 7, 96]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0) 163 #SLCT2: [1, 7, 96]; TN.DIM_OBJ_ID = var10 164 #CSCN2: [1, 374, 96]; INDEX33559058(DIM_9999 as TN)
最佳化前後比較：
可以看到隨便搞了一下，原來 11.6秒出結果的，現在 1.6 秒就能跑出結果了。😎😎
其實還能幫任總老婆最佳化得更快點，但是任總太小氣了，飯都不請我吃一頓，差不多這樣行了。😼😼😼