慢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
INNERJOIN TAWC22222 B
ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
INNERJOIN T2RRRR C
ON B.O_CODE = C.O_CODE
LEFTJOIN VRG99999 T1
ON C.O_CODE = T1.O_CODE
AND T1.BASE_DATE = A.BASE_DATE
LEFTJOIN 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
UNIONALL
SELECT T2.SET_ID AS O_CODE,
'GG' 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
INNERJOIN TAWC22222 B
ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
INNERJOIN 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
WHERE1=1
GROUPBY 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 (SELECTDISTINCT 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
INNERJOIN TAWC22222 A
ON STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
INNERJOIN 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 >0OR STATS.AWC_QUOTA >0)) A
INNERJOIN T2RRRR C
ON A.CUST_CODE = C.O_CODE
LEFTJOIN (SELECTDISTINCT DIM_CODE, DIM_CLASS_NAME
FROM T9X2CC
ORDERBY DIM_CODE ASC) D
ON A.P_CLASS = D.DIM_CODE
LEFTJOIN VRG99999 T1
ON C.O_CODE = T1.O_CODE
AND T1.BASE_DATE ='2024-04-01'
LEFTJOIN TAXC45200 T2
ON T1.SET_ID = T2.SET_ID
AND T1.BASE_DATE ='2024-04-01'
WHERE1=1
GROUPBY 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
INNERJOIN TAWC22222 T2
ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
LEFTJOIN TAWC_RESULT_DETAIL T3
ON T1.INST_ID = T3.INST_ID
AND T3.AWC_TYPE LIKE'AWC_%'
LEFTJOIN TAWC22222 T4
ON T3.DIM_OBJ_ID = T4.DIM_OBJ_ID
LEFTJOIN 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 ISNULLOR T1.ADJ_END_DATE >'2024-04-01'))
SELECTCOUNT(1)
FROM (SELECT T1.*,
TO_CHAR(DECODE(T1.DIM_CODE,
'DIM_PORT',
T3.ADJ_LINE,
DECODE(CUS_TYPE,
'GG',
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,
(SELECTMAX(PARENTID) PARENTID
FROM (SELECTCASEMAX(DIM_OBJ_ID)
WHENNULLTHEN
''
ELSE
MAX(TN.CUST_CODE)
END PARENTID
FROM DIM_9999 TN
WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
GROUPBY TN.DIM_OBJ_ID
HAVINGCOUNT(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
INNERJOIN CORP11111 C
ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
WHERE A.BASE_DATE ='2024-04-01'
UNIONALL
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
INNERJOIN PORT_11112 B
ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
LEFTJOIN (SELECTMAX(BASE_DATE) MAX_BASE_DATE
FROM TAWC11111
WHERE BASE_DATE <=
TO_CHAR(SYSDATE, 'YYYY-MM-DD')) C
ON1=1
WHERE A.BASE_DATE ='2024-04-01'
AND B.DIM_CODE ='DIM_PORT') T
GROUPBY 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
LEFTJOIN (SELECTSUM(ADJ_LINE) AS ADJ_LINE, O_CODE
FROM CREDIT9144
GROUPBY O_CODE) T2
ON T1.O_CODE = T2.O_CODE
LEFTJOIN (SELECTSUM(ADJ_LINE) AS ADJ_LINE, DIM_OBJ_ID
FROM CREDIT9144
GROUPBY DIM_OBJ_ID) T3
ON T1.DIM_OBJ_ID = T3.DIM_OBJ_ID
LEFTJOIN (SELECTSUM(ADJ_LINE) AS ADJ_LINE, SET_ID
FROM CREDIT9144
GROUPBY SET_ID) T4
ON T1.O_CODE = T4.SET_ID) T1
LEFTJOIN (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
LEFTJOIN TSYS_DEPT TD1
ON T1.D_CORP = TD1.SD_ID
LEFTJOIN TSYS_DEPT TD2
ON T1.D_DEPT = TD2.SD_ID
LEFTJOIN TSYS_DEPT TD3
ON T1.D_BIZLINE = TD3.SD_ID
LEFTJOIN (SELECTDISTINCT DIM_CODE, DIM_CLASS_NAME
FROM T9X2CC) T3
ON T1.P_CLASS = T3.DIM_CODE
LEFTJOIN T2RRRR TC
ON T1.CUST_CODE = TC.O_CODE
GROUPBY DIM_OBJ_ID) T2
ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
LEFTJOIN (SELECT*
FROM (SELECT T.*,
ROW_NUMBER() OVER (PARTITIONBY T.O_CODE ORDERBY 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真实执行计划：
#NSET2: [160580, 1, 3942]
#PIPE2: [160580, 1, 3942]
#PIPE2: [160579, 1, 3942]
#PIPE2: [160566, 1, 3942]
#PRJT2: [160557, 1, 3942]; exp_num(1), is_atom(FALSE)
#AAGR2: [160557, 1, 3942]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
#HASH RIGHT JOIN2: [160557, 362511, 3942]; key_num(1), ret_null(0), KEY(T3.O_CODE=T1.O_CODE)
#PRJT2: [24, 331, 528]; exp_num(1), is_atom(FALSE)
#SLCT2: [24, 331, 528]; C.SN = var15
#PRJT2: [24, 13278, 528]; exp_num(2), is_atom(FALSE)
#AFUN: [24, 13278, 528]; afun_num(1); partition_num(1)[T.O_CODE]; order_num(3)[T.BASEDATE, T.BEG_DATE, T.IMP_TIME]
#SORT3: [24, 13278, 528]; key_num(4), is_distinct(FALSE), top_flag(0), is_adaptive(0)
#SLCT2: [24, 13278, 528]; (T.BEG_DATE <='2024-04-01'AND T.END_DATE >='2024-04-01')
#BLKUP2: [24, 13619, 528]; TCRT_RESULT_TMPFLAG(T)
#SSEK2: [24, 13619, 528]; scan_type(ASC), TCRT_RESULT_TMPFLAG(TCRT_RESULT as T), scan_range['0','0']
#PRJT2: [160355, 362511, 3414]; exp_num(1), is_atom(FALSE)
#HASH RIGHT JOIN2: [160355, 362511, 3414]; key_num(1), ret_null(0), KEY(T4.SET_ID=T1.O_CODE)
#PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
#HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.SET_ID)
#HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
#HASH RIGHT JOIN2: [160213, 362511, 2568]; key_num(1), ret_null(0), KEY(T3.DIM_OBJ_ID=T1.DIM_OBJ_ID)
#PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
#HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.DIM_OBJ_ID)
#HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
#HASH RIGHT JOIN2: [160110, 362511, 1722]; key_num(1), ret_null(0), KEY(T2.O_CODE=T1.O_CODE)
#PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
#HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.O_CODE)
#HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
#PRJT2: [160045, 362511, 876]; exp_num(2), is_atom(FALSE)
#PRJT2: [160045, 362511, 876]; exp_num(2), is_atom(FALSE)
#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)
#PRJT2: [159784, 362511, 876]; exp_num(9), is_atom(FALSE)
#PRJT2: [159784, 362511, 876]; exp_num(9), is_atom(FALSE)
#UNIONALL: [159784, 362511, 876]
#PRJT2: [2907, 362494, 876]; exp_num(9), is_atom(FALSE)
#HASH2 INNERJOIN: [2907, 362494, 876]; KEY_NUM(1); KEY(A.DIM_OBJ_ID=C.DIM_OBJ_ID) KEY_NULL_EQU(0)
#PRJT2: [95, 69566, 294]; exp_num(2), is_atom(FALSE)
#BLKUP2: [95, 69566, 294]; TAWC_STATIC_RESULT_BASEDATE(A)
#SSEK2: [95, 69566, 294]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111 as A), scan_range['2024-04-01','2024-04-01']
#PRJT2: [2773, 172472, 582]; exp_num(6), is_atom(FALSE)
#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)
#PRJT2: [2015, 4081172, 582]; exp_num(8), is_atom(FALSE)
#UNIONALL: [2015, 4081172, 582]
#PRJT2: [845, 4081171, 582]; exp_num(8), is_atom(FALSE)
#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)
#CSCN2: [23, 172472, 144]; INDEX33559059(TAXC45200 as T2)
#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)
#NEST LOOP INDEX JOIN2: [116, 1717, 582]
#SLCT2: [105, 1717, 390]; B.DIM_CODE ='DIM_CORP'
#NEST LOOP INDEX JOIN2: [105, 1717, 390]
#PRJT2: [91, 1717, 246]; exp_num(2), is_atom(FALSE)
#SLCT2: [91, 1717, 246]; TAWC11111.AWC_QUOTA > var23
#BLKUP2: [91, 68827, 246]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
#SSEK2: [91, 68827, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01']
#BLKUP2: [11, 1, 48]; INDEX33559829(B)
#SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as B), scan_range[A.DIM_OBJ_ID,A.DIM_OBJ_ID]
#BLKUP2: [11, 1, 48]; INDEX33558827(C)
#SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[B.O_CODE,B.O_CODE]
#CSCN2: [75, 562125, 144]; INDEX33559061(VRG99999 as T1)
#PRJT2: [59, 1, 534]; exp_num(8), is_atom(FALSE)
#SLCT2: [59, 1, 534]; (A.BASE_DATE = T2.BASE_DATE AND A.DIM_OBJ_ID = B.DIM_OBJ_ID)
#NEST LOOP INNER JOIN2: [59, 1, 534]; [with var]
#HASH2 INNERJOIN: [40, 119, 288]; KEY_NUM(1); KEY(T2.SET_ID=B.VRG_SET_ID) KEY_NULL_EQU(0)
#SLCT2: [24, 1053, 144]; T2.BASE_DATE ='2024-04-01'
#CSCN2: [24, 172472, 144]; INDEX33559059(TAXC45200 as T2)
#BLKUP2: [13, 11989, 144]; IDX_YHY_24011901_01(B)
#SSEK2: [13, 11989, 144]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as B), scan_range['DIM_VRG','DIM_VRG']
#PRJT2: [1, 1, 246]; exp_num(2), is_atom(FALSE)
#SLCT2: [1, 1, 246]; (TAWC11111.AWC_QUOTA > var24 AND TAWC11111.BASE_DATE = var11)
#BLKUP2: [1, 2, 246]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
#SSEK2: [1, 2, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var12),('2024-04-01',var12)]
#PRJT2: [156751, 16, 1350]; exp_num(9), is_atom(FALSE)
#NEST LOOP LEFT JOIN2: [156751, 16, 1350]; joincondition( TRUE ) partition_keys_num(0) ret_null(0)
#SLCT2: [73, 16, 1302]; A.DIM_OBJ_ID = B.DIM_OBJ_ID
#NEST LOOP INNER JOIN2: [73, 16, 1302]; [with var]
#PRJT2: [71, 21, 978]; exp_num(4), is_atom(FALSE)
#SAGR2: [71, 21, 978]; grp_num(2), sfun_num(1), distinct_flag[1]; slave_empty(0) keys(A.DIM_OBJ_ID, T2.SET_NAME)
#SORT3: [71, 21, 978]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)
#INDEX JOINLEFT JOIN2: [70, 96, 978] joincondition(T1.BASE_DATE ='2024-04-01') ret_null(0)
#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)
#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)
#NEST LOOP INDEX JOIN2: [62, 9, 738]
#PRJT2: [61, 9, 642]; exp_num(3), is_atom(FALSE)
#DISTINCT: [61, 9, 642]
#HASH2 INNERJOIN: [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)
#SLCT2: [59, 2, 354]; STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
#NEST LOOP INNER JOIN2: [59, 2, 354]; [with var]
#BLKUP2: [1, 52, 96]; IDX_YHY_24011901_01(A)
#SSEK2: [1, 52, 96]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as A), scan_range['DIM_PORT','DIM_PORT']
#PRJT2: [1, 2, 258]; exp_num(1), is_atom(FALSE)
#UNIONFOR OR2: [1, 2, 258]; key_num(1), outer_join(-)
#SLCT2: [1, 1, 258]; TAWC11111.LIMIT_LINE > var26
#BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
#SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
#SLCT2: [1, 1, 258]; TAWC11111.AWC_QUOTA > var27
#BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
#SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
#CSCN2: [1, 374, 288]; INDEX33559058(DIM_9999 as B)
#BLKUP2: [1, 1, 48]; INDEX33558827(C)
#SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[A.CUST_CODE,A.CUST_CODE]
#PRJT2: [1, 98, 96]; exp_num(1), is_atom(FALSE)
#SORT3: [1, 98, 96]; key_num(2), is_distinct(TRUE), top_flag(0), is_adaptive(0)
#CSCN2: [1, 98, 96]; INDEX33559770(T9X2CC)
#BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T1)
#SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T1), scan_range['2024-04-01','2024-04-01']
#BLKUP2: [1, 8, 48]; INDEX33559689(T2)
#SSEK2: [1, 8, 48]; scan_type(ASC), INDEX33559689(TAXC45200 as T2), scan_range[(T1.SET_ID,min),(T1.SET_ID,max))
#PRJT2: [1, 2, 324]; exp_num(2), is_atom(FALSE)
#SSEK2: [1, 2, 324]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111 as A), scan_range[('2024-04-01',var13),('2024-04-01',var13)]
#PRJT2: [793, 1, 48]; exp_num(1), is_atom(FALSE)
#AAGR2: [793, 1, 48]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
#SLCT2: [793, 6453684, 48]; TAWC11111.BASE_DATE <= var29
#SSCN: [793, 6385120, 48]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
#HEAP TABLE: [8, 4, 846]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
#PRJT2: [8, 4, 846]; exp_num(3), is_atom(FALSE)
#UNIONFOR OR2: [8, 4, 846]; key_num(1), outer_join(-)
#UNIONFOR OR2: [5, 3, 846]; key_num(1), outer_join(-)
#UNIONFOR OR2: [2, 2, 846]; key_num(1), outer_join(-)
#HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896344441.colname=DMTEMPVIEW_896344370.TMPCOL5) KEY_NULL_EQU(0)
#CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 ISNULL
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896344442.colname=DMTEMPVIEW_896344370.TMPCOL5) KEY_NULL_EQU(0)
#CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 >'2024-04-01'
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896344370.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 ISNULL
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG ='1'
#CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
#HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896344370.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 >'2024-04-01'
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG ='1'
#CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
#HEAP TABLE: [13, 1, 846]; table_no(1) full(0), mpp_full(0) autoid(1), sites(-)
#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)
#INDEX JOINLEFT JOIN2: [8, 1, 702] ret_null(0)
#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)
#NEST LOOP INDEX JOIN2: [7, 1, 558]
#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')
#CSCN2: [7, 33528, 462]; INDEX33559064(TAWXV999 as T1)
#BLKUP2: [1, 1, 48]; INDEX33559829(T2)
#SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T2), scan_range[T1.DIM_OBJ_ID,T1.DIM_OBJ_ID]
#SLCT2: [1, 22, 144]; (T3.AWC_TYPE >='AWC'AND T3.AWC_TYPE <'AWD'AND T3.AWC_TYPE LIKE'AWC_%')
#CSCN2: [1, 505, 144]; INDEX33559057(TAWC_RESULT_DETAIL as T3)
#SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T4), scan_range[T3.DIM_OBJ_ID,T3.DIM_OBJ_ID]
#BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T5)
#SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T5), scan_range['2024-04-01','2024-04-01']
#SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
#PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
#AAGR2: [1, 1, 96]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
#PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)
#SLCT2: [1, 1, 96]; exp_sfun1 <= var32
#SAGR2: [1, 7, 96]; grp_num(1), sfun_num(3), distinct_flag[1,0,0]; slave_empty(0) keys(TN.DIM_OBJ_ID)
#SORT3: [1, 7, 96]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
#SLCT2: [1, 7, 96]; TN.DIM_OBJ_ID = var10
#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
152rows 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,
(SELECTMAX(PARENTID) PARENTID
FROM (SELECTCASEMAX(DIM_OBJ_ID)
WHENNULLTHEN
''
ELSE
MAX(TN.CUST_CODE)
END PARENTID
FROM DIM_9999 TN
WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
GROUPBY TN.DIM_OBJ_ID
HAVINGCOUNT(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,
NULLAS 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
INNERJOIN CORP11111 C
ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
WHERE A.BASE_DATE ='2024-04-01'
UNIONALL
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'
ENDAS 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
INNERJOIN PORT_11112 B
ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
LEFTJOIN (SELECTMAX(BASE_DATE) MAX_BASE_DATE
FROM TAWC11111
WHERE BASE_DATE <=
TO_CHAR(SYSDATE, 'YYYY-MM-DD')) C
ON1=1
WHERE A.BASE_DATE ='2024-04-01'
AND B.DIM_CODE ='DIM_PORT') T
GROUPBY 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
INNERJOIN TAWC22222 B
ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
INNERJOIN T2RRRR C
ON B.O_CODE = C.O_CODE
LEFTJOIN VRG99999 T1
ON C.O_CODE = T1.O_CODE
AND T1.BASE_DATE = A.BASE_DATE
LEFTJOIN 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
UNIONALL
SELECT T2.SET_ID AS O_CODE,
'GG' 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
INNERJOIN TAWC22222 B
ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
INNERJOIN 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
WHERE1=1
GROUPBY 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 (SELECTDISTINCT 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
INNERJOIN TAWC22222 A
ON STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
INNERJOIN 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
INNERJOIN T2RRRR C
ON A.CUST_CODE = C.O_CODE
LEFTJOIN (SELECTDISTINCT DIM_CODE,
DIM_CLASS_NAME
FROM T9X2CC
ORDERBY DIM_CODE ASC) D
ON A.P_CLASS = D.DIM_CODE
LEFTJOIN VRG99999 T1
ON C.O_CODE = T1.O_CODE
AND T1.BASE_DATE ='2024-04-01'
LEFTJOIN TAXC45200 T2
ON T1.SET_ID = T2.SET_ID
AND T1.BASE_DATE ='2024-04-01'
WHERE1=1
GROUPBY 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
INNERJOIN TAWC22222 T2
ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
LEFTJOIN TAWC_RESULT_DETAIL T3
ON T1.INST_ID = T3.INST_ID
AND T3.AWC_TYPE LIKE'AWC_%'
LEFTJOIN TAWC22222 T4
ON T3.DIM_OBJ_ID = T4.DIM_OBJ_ID
LEFTJOIN 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 ISNULL
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')
)
SELECTCOUNT(1) FROM (
SELECT T1.*,
TO_CHAR(DECODE(T1.DIM_CODE,
'DIM_PORT', T3.ADJ_LINE,
DECODE(CUS_TYPE,
'GG', 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,
(SELECTMAX(PARENTID) PARENTID
FROM (SELECTCASEMAX(DIM_OBJ_ID) WHENNULLTHEN''ELSEMAX(TN.CUST_CODE) END PARENTID
FROM DIM_9999 TN
WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
GROUPBY TN.DIM_OBJ_ID
HAVINGCOUNT(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(CASEWHEN T.AWC_TYPE ='AWC_GENERAL'THEN NVL(LIMIT_LINE, 0) ELSE0END) /10000,
'fm999999999999999990.00') AS LIMIT_LINE,
TO_CHAR(SUM(CASEWHEN T.AWC_TYPE ='AWC_GENERAL'THEN NVL(AWC_QUOTA, 0) ELSE0END) /10000,
'fm999999999999999990.00') AS AWC_QUOTA,
TO_CHAR(SUM(CASEWHEN T.AWC_TYPE ='AWC_GENERAL'THEN NVL(OCC_QUOTA, 0) ELSE0END) /10000,
'fm999999999999999990.00') AS OCC_QUOTA,
TO_CHAR(SUM(CASEWHEN T.AWC_TYPE ='AWC_GENERAL'THEN NVL(LIMIT_LINE_RATE, 0) *100ELSE0END),
'fm999999999999999990.00') AS LIMIT_LINE_RATE,
TO_CHAR(GREATEST(SUM(CASE
WHEN T.AWC_TYPE ='AWC_GENERAL'THEN NVL(LIMIT_LINE_ABLE, 0)
ELSE0END) /10000, 0), 'fm999999999999999990.00') AS LIMIT_LINE_ABLE,
TO_CHAR(SUM(CASEWHEN T.AWC_TYPE ='CE_GENERAL'THEN NVL(AWC_QUOTA, 0) ELSE0END) /10000,
'fm999999999999999990.00') AS CE_QUOTA,
TO_CHAR(SUM(CASEWHEN T.AWC_TYPE ='ELUL_GENERAL'THEN NVL(AWC_QUOTA, 0) ELSE0END) /10000,
'fm999999999999999990.00') AS ELUL_QUOTA,
SUM(CASEWHEN T.AWC_TYPE ='AWC_GENERAL'THEN NVL(AWC_QUOTA_ABLE, 0) ELSE0END) 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
INNERJOIN CORP11111 C
ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
WHERE A.BASE_DATE ='2024-04-01'
UNIONALL
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,
CASEWHEN C.MAX_BASE_DATE ='2024-04-01'THEN'1'ELSE'0'ENDAS 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
INNERJOIN PORT_11112 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
LEFTJOIN (SELECTMAX(BASE_DATE) MAX_BASE_DATE FROM X WHERE BASE_DATE <= TO_CHAR(SYSDATE, 'YYYY-MM-DD')
) C
ON1=1
WHERE A.BASE_DATE ='2024-04-01'
AND B.DIM_CODE ='DIM_PORT'
) T
GROUPBY 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
LEFTJOIN (SELECTSUM(ADJ_LINE) AS ADJ_LINE, O_CODE FROM CREDIT9144 GROUPBY O_CODE) T2
ON T1.O_CODE = T2.O_CODE
LEFTJOIN (SELECTSUM(ADJ_LINE) AS ADJ_LINE,
DIM_OBJ_ID
FROM CREDIT9144
GROUPBY DIM_OBJ_ID) T3
ON T1.DIM_OBJ_ID = T3.DIM_OBJ_ID
LEFTJOIN (SELECTSUM(ADJ_LINE) AS ADJ_LINE, SET_ID FROM CREDIT9144 GROUPBY SET_ID) T4
ON T1.O_CODE = T4.SET_ID) T1
LEFTJOIN (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
LEFTJOIN TSYS_DEPT TD1
ON T1.D_CORP = TD1.SD_ID
LEFTJOIN TSYS_DEPT TD2
ON T1.D_DEPT = TD2.SD_ID
LEFTJOIN TSYS_DEPT TD3
ON T1.D_BIZLINE = TD3.SD_ID
LEFTJOIN (SELECTDISTINCT DIM_CODE, DIM_CLASS_NAME FROM T9X2CC) T3
ON T1.P_CLASS = T3.DIM_CODE
LEFTJOIN T2RRRR TC
ON T1.CUST_CODE = TC.O_CODE
GROUPBY DIM_OBJ_ID) T2
ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
LEFTJOIN (SELECT*
FROM (SELECT T.*,
ROW_NUMBER() OVER (
PARTITIONBY T.O_CODE
ORDERBY 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;
改写后执行计划：
#NSET2: [17484, 1, 4470]
#PIPE2: [17484, 1, 4470]
#PIPE2: [17483, 1, 4470]
#PIPE2: [17470, 1, 4470]
#PRJT2: [17461, 1, 4470]; exp_num(1), is_atom(FALSE)
#AAGR2: [17461, 1, 4470]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
#HASH RIGHT JOIN2: [17461, 172479, 4470]; key_num(1), ret_null(0), KEY(T3.O_CODE=T1.O_CODE)
#PRJT2: [24, 331, 528]; exp_num(1), is_atom(FALSE)
#SLCT2: [24, 331, 528]; C.SN = var15
#PRJT2: [24, 13278, 528]; exp_num(2), is_atom(FALSE)
#AFUN: [24, 13278, 528]; afun_num(1); partition_num(1)[T.O_CODE]; order_num(3)[T.BASEDATE, T.BEG_DATE, T.IMP_TIME]
#SORT3: [24, 13278, 528]; key_num(4), is_distinct(FALSE), top_flag(0), is_adaptive(0)
#SLCT2: [24, 13278, 528]; (T.BEG_DATE <='2024-04-01'AND T.END_DATE >='2024-04-01')
#BLKUP2: [24, 13619, 528]; TCRT_RESULT_TMPFLAG(T)
#SSEK2: [24, 13619, 528]; scan_type(ASC), TCRT_RESULT_TMPFLAG(TCRT_RESULT as T), scan_range['0','0']
#PRJT2: [17340, 172479, 3942]; exp_num(1), is_atom(FALSE)
#HASH RIGHT JOIN2: [17340, 172479, 3942]; key_num(1), ret_null(0), KEY(T4.SET_ID=T1.O_CODE)
#PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
#HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.SET_ID)
#HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
#HASH RIGHT JOIN2: [17260, 172479, 3096]; key_num(1), ret_null(0), KEY(T3.DIM_OBJ_ID=T1.DIM_OBJ_ID)
#PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
#HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.DIM_OBJ_ID)
#HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
#HASH RIGHT JOIN2: [17198, 172479, 2250]; key_num(1), ret_null(0), KEY(T2.O_CODE=T1.O_CODE)
#PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
#HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.O_CODE)
#HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
#PRJT2: [17154, 172479, 1404]; exp_num(2), is_atom(FALSE)
#PRJT2: [17154, 172479, 1404]; exp_num(2), is_atom(FALSE)
#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)
#PRJT2: [17060, 172479, 1404]; exp_num(9), is_atom(FALSE)
#PRJT2: [17060, 172479, 1404]; exp_num(9), is_atom(FALSE)
#UNIONALL: [17060, 172479, 1404]
#PRJT2: [2953, 172471, 1404]; exp_num(9), is_atom(FALSE)
#HASH2 INNERJOIN: [2953, 172471, 1404]; KEY_NUM(1); KEY(A.DIM_OBJ_ID=C.DIM_OBJ_ID) KEY_NULL_EQU(0)
#PRJT2: [152, 3480, 822]; exp_num(2), is_atom(FALSE)
#PRJT2: [152, 3480, 822]; exp_num(2), is_atom(FALSE)
#SLCT2: [152, 3480, 822]; TAWC11111.BASE_DATE <= var24
#BLKUP2: [152, 68827, 822]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
#SSEK2: [152, 68827, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01']
#PRJT2: [2774, 172472, 582]; exp_num(6), is_atom(FALSE)
#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)
#PRJT2: [2016, 4084117, 582]; exp_num(8), is_atom(FALSE)
#UNIONALL: [2016, 4084117, 582]
#PRJT2: [846, 4084116, 582]; exp_num(8), is_atom(FALSE)
#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)
#CSCN2: [23, 172472, 144]; INDEX33559059(TAXC45200 as T2)
#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)
#NEST LOOP INDEX JOIN2: [116, 1718, 582]
#SLCT2: [105, 1718, 390]; B.DIM_CODE ='DIM_CORP'
#NEST LOOP INDEX JOIN2: [105, 1718, 390]
#PRJT2: [91, 1718, 246]; exp_num(2), is_atom(FALSE)
#SLCT2: [91, 1718, 246]; TAWC11111.AWC_QUOTA > var25
#BLKUP2: [91, 68827, 246]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
#SSEK2: [91, 68827, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01']
#BLKUP2: [11, 1, 48]; INDEX33559829(B)
#SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as B), scan_range[A.DIM_OBJ_ID,A.DIM_OBJ_ID]
#BLKUP2: [11, 1, 48]; INDEX33558827(C)
#SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[B.O_CODE,B.O_CODE]
#CSCN2: [75, 562125, 144]; INDEX33559061(VRG99999 as T1)
#PRJT2: [59, 1, 534]; exp_num(8), is_atom(FALSE)
#SLCT2: [59, 1, 534]; (A.BASE_DATE = T2.BASE_DATE AND A.DIM_OBJ_ID = B.DIM_OBJ_ID)
#NEST LOOP INNER JOIN2: [59, 1, 534]; [with var]
#HASH2 INNERJOIN: [40, 119, 288]; KEY_NUM(1); KEY(T2.SET_ID=B.VRG_SET_ID) KEY_NULL_EQU(0)
#SLCT2: [24, 1053, 144]; T2.BASE_DATE ='2024-04-01'
#CSCN2: [24, 172472, 144]; INDEX33559059(TAXC45200 as T2)
#BLKUP2: [13, 11990, 144]; IDX_YHY_24011901_01(B)
#SSEK2: [13, 11990, 144]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as B), scan_range['DIM_VRG','DIM_VRG']
#PRJT2: [1, 1, 246]; exp_num(2), is_atom(FALSE)
#SLCT2: [1, 1, 246]; (TAWC11111.AWC_QUOTA > var26 AND TAWC11111.BASE_DATE = var11)
#BLKUP2: [1, 2, 246]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
#SSEK2: [1, 2, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var12),('2024-04-01',var12)]
#PRJT2: [14024, 8, 2622]; exp_num(9), is_atom(FALSE)
#NEST LOOP LEFT JOIN2: [14024, 8, 2622]; joincondition( TRUE ) partition_keys_num(0) ret_null(0)
#SLCT2: [74, 8, 1800]; A.DIM_OBJ_ID = B.DIM_OBJ_ID
#NEST LOOP INNER JOIN2: [74, 8, 1800]; [with var]
#PRJT2: [71, 21, 978]; exp_num(4), is_atom(FALSE)
#SAGR2: [71, 21, 978]; grp_num(2), sfun_num(1), distinct_flag[1]; slave_empty(0) keys(A.DIM_OBJ_ID, T2.SET_NAME)
#SORT3: [71, 21, 978]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)
#INDEX JOINLEFT JOIN2: [70, 96, 978] joincondition(T1.BASE_DATE ='2024-04-01') ret_null(0)
#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)
#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)
#NEST LOOP INDEX JOIN2: [62, 9, 738]
#PRJT2: [61, 9, 642]; exp_num(3), is_atom(FALSE)
#DISTINCT: [61, 9, 642]
#HASH2 INNERJOIN: [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)
#SLCT2: [59, 2, 354]; STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
#NEST LOOP INNER JOIN2: [59, 2, 354]; [with var]
#BLKUP2: [1, 52, 96]; IDX_YHY_24011901_01(A)
#SSEK2: [1, 52, 96]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as A), scan_range['DIM_PORT','DIM_PORT']
#PRJT2: [1, 2, 258]; exp_num(1), is_atom(FALSE)
#UNIONFOR OR2: [1, 2, 258]; key_num(1), outer_join(-)
#SLCT2: [1, 1, 258]; TAWC11111.LIMIT_LINE > var28
#BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
#SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
#SLCT2: [1, 1, 258]; TAWC11111.AWC_QUOTA > var29
#BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
#SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
#CSCN2: [1, 374, 288]; INDEX33559058(DIM_9999 as B)
#BLKUP2: [1, 1, 48]; INDEX33558827(C)
#SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[A.CUST_CODE,A.CUST_CODE]
#PRJT2: [1, 98, 96]; exp_num(1), is_atom(FALSE)
#SORT3: [1, 98, 96]; key_num(2), is_distinct(TRUE), top_flag(0), is_adaptive(0)
#CSCN2: [1, 98, 96]; INDEX33559770(T9X2CC)
#BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T1)
#SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T1), scan_range['2024-04-01','2024-04-01']
#BLKUP2: [1, 8, 48]; INDEX33559689(T2)
#SSEK2: [1, 8, 48]; scan_type(ASC), INDEX33559689(TAXC45200 as T2), scan_range[(T1.SET_ID,min),(T1.SET_ID,max))
#PRJT2: [1, 1, 822]; exp_num(2), is_atom(FALSE)
#PRJT2: [1, 1, 822]; exp_num(2), is_atom(FALSE)
#SLCT2: [1, 1, 822]; TAWC11111.BASE_DATE <= var31
#SSEK2: [1, 2, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var13),('2024-04-01',var13)]
#PRJT2: [152, 1, 822]; exp_num(1), is_atom(FALSE)
#AAGR2: [152, 1, 822]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
#PRJT2: [152, 69616, 822]; exp_num(1), is_atom(FALSE)
#SLCT2: [152, 69616, 822]; TAWC11111.BASE_DATE <= var33
#SSEK2: [152, 68827, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01',exp11]
#HEAP TABLE: [8, 4, 846]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
#PRJT2: [8, 4, 846]; exp_num(3), is_atom(FALSE)
#UNIONFOR OR2: [8, 4, 846]; key_num(1), outer_join(-)
#UNIONFOR OR2: [5, 3, 846]; key_num(1), outer_join(-)
#UNIONFOR OR2: [2, 2, 846]; key_num(1), outer_join(-)
#HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896943136.colname=DMTEMPVIEW_896943065.TMPCOL5) KEY_NULL_EQU(0)
#CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 ISNULL
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896943137.colname=DMTEMPVIEW_896943065.TMPCOL5) KEY_NULL_EQU(0)
#CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 >'2024-04-01'
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896943065.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 ISNULL
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG ='1'
#CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
#HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896943065.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
#SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 >'2024-04-01'
#HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
#SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG ='1'
#CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
#HEAP TABLE: [13, 1, 846]; table_no(1) full(0), mpp_full(0) autoid(1), sites(-)
#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)
#INDEX JOINLEFT JOIN2: [8, 1, 702] ret_null(0)
#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)
#NEST LOOP INDEX JOIN2: [7, 1, 558]
#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')
#CSCN2: [7, 33528, 462]; INDEX33559064(TAWXV999 as T1)
#BLKUP2: [1, 1, 48]; INDEX33559829(T2)
#SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T2), scan_range[T1.DIM_OBJ_ID,T1.DIM_OBJ_ID]
#SLCT2: [1, 22, 144]; (T3.AWC_TYPE >='AWC'AND T3.AWC_TYPE <'AWD'AND T3.AWC_TYPE LIKE'AWC_%')
#CSCN2: [1, 505, 144]; INDEX33559057(TAWC_RESULT_DETAIL as T3)
#SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T4), scan_range[T3.DIM_OBJ_ID,T3.DIM_OBJ_ID]
#BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T5)
#SSEK2: [4, 3429, 144]; scan_type(ASC), TAWC_VRG_SET_ITEM_BASEDATE(VRG99999 as T5), scan_range['2024-04-01','2024-04-01']
#SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
#PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
#AAGR2: [1, 1, 96]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
#PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)
#SLCT2: [1, 1, 96]; exp_sfun1 <= var36
#SAGR2: [1, 7, 96]; grp_num(1), sfun_num(3), distinct_flag[1,0,0]; slave_empty(0) keys(TN.DIM_OBJ_ID)
#SORT3: [1, 7, 96]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
#SLCT2: [1, 7, 96]; TN.DIM_OBJ_ID = var10
#CSCN2: [1, 374, 96]; INDEX33559058(DIM_9999 as TN)
优化前后比较：
可以看到随便搞了一下，原来 11.6秒出结果的，现在 1.6 秒就能跑出结果了。😎😎
结语
希望以上内容能助大家一臂之力，解决工作中的难题。欢迎随时交流探讨，感谢您的关注和支持！
文章转载自国产数据库学堂，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
