最近没遇到啥有意思的案例，都是些很简单的案例，但是又好久没写过博客了，决定水一波帖子，保持更新。??
今天这个是任总老婆小王同学提供的金融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 秒就能跑出结果了。????
其实还能帮任总老婆优化得更快点，但是任总太小气了，饭都不请我吃一顿，差不多这样行了。??????
经验首页