经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置:技术经验 » 程序设计 » SQL语言 » 查看文章
DM数据库金融行业案例(水贴一波)
来源:cnblogs  作者:小至尖尖  时间:2024/4/3 9:20:58  对本文有异议

 

  最近没遇到啥有意思的案例,都是些很简单的案例,但是又好久没写过博客了,决定水一波帖子,保持更新。??

  今天这个是任总老婆小王同学提供的金融SQL案例,难是不难,但是远程的时候网络卡得要命, 心累。??

 

慢SQL(关键信息已经加密):

  1. WITH CORP11111 AS
  2. (SELECT T.O_CODE,
  3. T.O_NAME,
  4. T.CUS_TYPE,
  5. T.O_TYPE,
  6. T.SET_NAME,
  7. T.SET_ID,
  8. T.DIM_CODE,
  9. T.DIM_OBJ_ID
  10. FROM (SELECT C.O_CODE,
  11. C.CUS_TYPE,
  12. C.O_TYPE,
  13. C.O_NAME,
  14. T2.SET_NAME,
  15. T2.SET_ID,
  16. B.DIM_CODE,
  17. B.DIM_OBJ_ID,
  18. '' AS D_DEPT,
  19. '' AS D_NAME,
  20. '' AS D_BIZLINE,
  21. '' AS P_CLASS,
  22. '' AS P_CLASS_NAME
  23. FROM (SELECT DIM_OBJ_ID,
  24. BASE_DATE,
  25. LIMIT_LINE,
  26. AWC_QUOTA,
  27. OCC_QUOTA,
  28. LIMIT_LINE_ABLE,
  29. LIMIT_LINE_RATE
  30. FROM TAWC11111
  31. WHERE BASE_DATE = '2024-04-01') A
  32. INNER JOIN TAWC22222 B
  33. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  34. INNER JOIN T2RRRR C
  35. ON B.O_CODE = C.O_CODE
  36. LEFT JOIN VRG99999 T1
  37. ON C.O_CODE = T1.O_CODE
  38. AND T1.BASE_DATE = A.BASE_DATE
  39. LEFT JOIN TAXC45200 T2
  40. ON T1.SET_ID = T2.SET_ID
  41. AND T2.BASE_DATE = A.BASE_DATE
  42. WHERE A.BASE_DATE = '2024-04-01'
  43. AND B.DIM_CODE = 'DIM_CORP'
  44. AND A.AWC_QUOTA > 0
  45. UNION ALL
  46. SELECT T2.SET_ID AS O_CODE,
  47. '集团' AS CUS_TYPE,
  48. 'group' AS O_TYPE,
  49. '' AS O_NAME,
  50. T2.SET_NAME,
  51. T2.SET_ID,
  52. B.DIM_CODE,
  53. B.DIM_OBJ_ID,
  54. '' AS D_DEPT,
  55. '' AS D_NAME,
  56. '' AS D_BIZLINE,
  57. '' AS P_CLASS,
  58. '' AS P_CLASS_NAME
  59. FROM (SELECT DIM_OBJ_ID,
  60. BASE_DATE,
  61. LIMIT_LINE,
  62. AWC_QUOTA,
  63. OCC_QUOTA,
  64. LIMIT_LINE_ABLE,
  65. LIMIT_LINE_RATE
  66. FROM TAWC11111
  67. WHERE BASE_DATE = '2024-04-01') A
  68. INNER JOIN TAWC22222 B
  69. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  70. INNER JOIN TAXC45200 T2
  71. ON B.VRG_SET_ID = T2.SET_ID
  72. AND A.BASE_DATE = T2.BASE_DATE
  73. WHERE A.BASE_DATE = '2024-04-01'
  74. AND B.DIM_CODE = 'DIM_VRG'
  75. AND A.AWC_QUOTA > 0) T
  76. WHERE 1 = 1
  77. GROUP BY O_CODE,
  78. O_NAME,
  79. CUS_TYPE,
  80. O_TYPE,
  81. SET_NAME,
  82. SET_ID,
  83. DIM_CODE,
  84. DIM_OBJ_ID),
  85. PORT_11112 AS
  86. (SELECT A.DIM_OBJ_ID AS CUST_CODE,
  87. '' CUS_TYPE,
  88. '2' O_TYPE,
  89. WM_CONCAT(DISTINCT(C.O_NAME)) O_NAME,
  90. WM_CONCAT(DISTINCT(A.D_DEPT)) D_DEPT,
  91. WM_CONCAT(DISTINCT(A.D_NAME)) D_NAME,
  92. WM_CONCAT(DISTINCT(A.D_BIZLINE)) D_BIZLINE,
  93. WM_CONCAT(DISTINCT(A.P_CLASS)) P_CLASS,
  94. WM_CONCAT(DISTINCT(DIM_CLASS_NAME)) P_CLASS_NAME,
  95. 'DIM_PORT' DIM_CODE,
  96. A.DIM_OBJ_ID,
  97. T2.SET_NAME
  98. FROM (SELECT DISTINCT A.DIM_OBJ_ID,
  99. A.DIM_CODE,
  100. B.CUST_CODE,
  101. B.D_DEPT,
  102. B.D_NAME,
  103. B.D_BIZLINE,
  104. B.P_CLASS
  105. FROM (SELECT AWC_QUOTA,
  106. LIMIT_LINE,
  107. BASE_DATE,
  108. DIM_OBJ_ID,
  109. OCC_QUOTA,
  110. LIMIT_LINE_ABLE,
  111. LIMIT_LINE_RATE
  112. FROM TAWC11111
  113. WHERE BASE_DATE = '2024-04-01') STATS
  114. INNER JOIN TAWC22222 A
  115. ON STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
  116. INNER JOIN DIM_9999 B
  117. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  118. WHERE A.DIM_CODE = 'DIM_PORT'
  119. AND STATS.BASE_DATE = '2024-04-01'
  120. AND (STATS.LIMIT_LINE > 0 OR STATS.AWC_QUOTA > 0)) A
  121. INNER JOIN T2RRRR C
  122. ON A.CUST_CODE = C.O_CODE
  123. LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME
  124. FROM T9X2CC
  125. ORDER BY DIM_CODE ASC) D
  126. ON A.P_CLASS = D.DIM_CODE
  127. LEFT JOIN VRG99999 T1
  128. ON C.O_CODE = T1.O_CODE
  129. AND T1.BASE_DATE = '2024-04-01'
  130. LEFT JOIN TAXC45200 T2
  131. ON T1.SET_ID = T2.SET_ID
  132. AND T1.BASE_DATE = '2024-04-01'
  133. WHERE 1 = 1
  134. GROUP BY A.DIM_OBJ_ID, T2.SET_NAME),
  135. CREDIT9144 AS
  136. (SELECT DECODE(T1.ADJ_TYPE, '调减', -1, 1) * T1.ADJ_LINE AS ADJ_LINE,
  137. T2.O_CODE,
  138. T1.INST_ID,
  139. T4.DIM_OBJ_ID,
  140. T5.SET_ID
  141. FROM TAWXV999 T1
  142. INNER JOIN TAWC22222 T2
  143. ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
  144. LEFT JOIN TAWC_RESULT_DETAIL T3
  145. ON T1.INST_ID = T3.INST_ID
  146. AND T3.AWC_TYPE LIKE 'AWC_%'
  147. LEFT JOIN TAWC22222 T4
  148. ON T3.DIM_OBJ_ID = T4.DIM_OBJ_ID
  149. LEFT JOIN VRG99999 T5
  150. ON T2.O_CODE = T5.O_CODE
  151. AND T5.BASE_DATE = '2024-04-01'
  152. WHERE T1.DATA_SOURCE = 'ADJ'
  153. AND T1.TMP_FLAG = '0'
  154. AND T1.AWC_TYPE = 'AWC_CREDIT'
  155. AND (T1.REMARK = '初始化-占用信用债' OR T1.REMARK = '释放信用债' OR
  156. T1.INST_ID IN
  157. (SELECT INST_ID FROM CFLSSX WHERE AWC_CREDIT_FLAG = '1'))
  158. AND T1.ADJ_BEG_DATE <= '2024-04-01'
  159. AND (T1.ADJ_END_DATE IS NULL OR T1.ADJ_END_DATE > '2024-04-01'))
  160. SELECT COUNT(1)
  161. FROM (SELECT T1.*,
  162. TO_CHAR(DECODE(T1.DIM_CODE,
  163. 'DIM_PORT',
  164. T3.ADJ_LINE,
  165. DECODE(CUS_TYPE,
  166. '集团',
  167. T4.ADJ_LINE,
  168. T2.ADJ_LINE)) / 10000,
  169. 'fm999999999999999990.00') AS ADJ_LINE
  170. FROM (SELECT T.O_CODE AS ID,
  171. T.O_NAME AS CORP_NAME,
  172. (SELECT MAX(PARENTID) PARENTID
  173. FROM (SELECT CASE MAX(DIM_OBJ_ID)
  174. WHEN NULL THEN
  175. ''
  176. ELSE
  177. MAX(TN.CUST_CODE)
  178. END PARENTID
  179. FROM DIM_9999 TN
  180. WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
  181. GROUP BY TN.DIM_OBJ_ID
  182. HAVING COUNT(DISTINCT(CUST_CODE)) <= 1) A) PARENTID,
  183. T.O_CODE,
  184. T.BASE_DATE,
  185. T.DIM_OBJ_ID,
  186. T.CUS_TYPE,
  187. T.O_NAME AS CUS_NAME,
  188. T.DIM_CODE,
  189. T.IS_NEW_DATE,
  190. TO_CHAR(SUM(CASE
  191. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  192. NVL(LIMIT_LINE, 0)
  193. ELSE
  194. 0
  195. END) / 10000,
  196. 'fm999999999999999990.00') AS LIMIT_LINE,
  197. TO_CHAR(SUM(CASE
  198. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  199. NVL(AWC_QUOTA, 0)
  200. ELSE
  201. 0
  202. END) / 10000,
  203. 'fm999999999999999990.00') AS AWC_QUOTA,
  204. TO_CHAR(SUM(CASE
  205. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  206. NVL(OCC_QUOTA, 0)
  207. ELSE
  208. 0
  209. END) / 10000,
  210. 'fm999999999999999990.00') AS OCC_QUOTA,
  211. TO_CHAR(SUM(CASE
  212. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  213. NVL(LIMIT_LINE_RATE, 0) * 100
  214. ELSE
  215. 0
  216. END),
  217. 'fm999999999999999990.00') AS LIMIT_LINE_RATE,
  218. TO_CHAR(GREATEST(SUM(CASE
  219. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  220. NVL(LIMIT_LINE_ABLE, 0)
  221. ELSE
  222. 0
  223. END) / 10000,
  224. 0),
  225. 'fm999999999999999990.00') AS LIMIT_LINE_ABLE,
  226. TO_CHAR(SUM(CASE
  227. WHEN T.AWC_TYPE = 'CE_GENERAL' THEN
  228. NVL(AWC_QUOTA, 0)
  229. ELSE
  230. 0
  231. END) / 10000,
  232. 'fm999999999999999990.00') AS CE_QUOTA,
  233. TO_CHAR(SUM(CASE
  234. WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN
  235. NVL(AWC_QUOTA, 0)
  236. ELSE
  237. 0
  238. END) / 10000,
  239. 'fm999999999999999990.00') AS ELUL_QUOTA,
  240. SUM(CASE
  241. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  242. NVL(AWC_QUOTA_ABLE, 0)
  243. ELSE
  244. 0
  245. END) AS AWC_QUOTA_ABLE,
  246. t.set_id,
  247. t.set_name
  248. FROM (SELECT A.AWC_TYPE,
  249. C.O_CODE,
  250. C.O_NAME,
  251. A.AWC_QUOTA,
  252. A.OCC_QUOTA,
  253. A.LIMIT_LINE_ABLE,
  254. A.LIMIT_LINE_RATE,
  255. A.LIMIT_LINE,
  256. A.BASE_DATE,
  257. A.DIM_OBJ_ID,
  258. C.CUS_TYPE,
  259. 'DIM_CORP' AS DIM_CODE,
  260. NULL AS AWC_QUOTA_ABLE,
  261. '' AS IS_NEW_DATE,
  262. c.set_id,
  263. c.set_name
  264. FROM (SELECT A.AWC_TYPE,
  265. A.AWC_QUOTA,
  266. A.OCC_QUOTA,
  267. A.LIMIT_LINE_ABLE,
  268. A.LIMIT_LINE_RATE,
  269. A.LIMIT_LINE,
  270. A.BASE_DATE,
  271. A.DIM_OBJ_ID
  272. FROM TAWC11111 A
  273. WHERE A.BASE_DATE = '2024-04-01') A
  274. INNER JOIN CORP11111 C
  275. ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
  276. WHERE A.BASE_DATE = '2024-04-01'
  277. UNION ALL
  278. SELECT A.AWC_TYPE,
  279. B.CUST_CODE AS O_CODE,
  280. B.O_NAME || '(组合)' AS NAME,
  281. A.AWC_QUOTA,
  282. A.OCC_QUOTA,
  283. A.LIMIT_LINE_ABLE,
  284. A.LIMIT_LINE_RATE,
  285. A.LIMIT_LINE,
  286. A.BASE_DATE,
  287. A.DIM_OBJ_ID,
  288. B.CUS_TYPE,
  289. 'DIM_PORT' AS DIM_CODE,
  290. A.AWC_QUOTA_ABLE,
  291. CASE
  292. WHEN C.MAX_BASE_DATE = '2024-04-01' THEN
  293. '1'
  294. ELSE
  295. '0'
  296. END AS IS_NEW_DATE,
  297. '' AS set_id,
  298. '' AS set_name
  299. FROM (SELECT A.AWC_TYPE,
  300. A.AWC_QUOTA,
  301. A.OCC_QUOTA,
  302. A.LIMIT_LINE_ABLE,
  303. A.LIMIT_LINE_RATE,
  304. A.LIMIT_LINE,
  305. A.BASE_DATE,
  306. A.DIM_OBJ_ID,
  307. A.AWC_QUOTA_ABLE
  308. FROM TAWC11111 A
  309. WHERE A.BASE_DATE = '2024-04-01') A
  310. INNER JOIN PORT_11112 B
  311. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  312. LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE
  313. FROM TAWC11111
  314. WHERE BASE_DATE <=
  315. TO_CHAR(SYSDATE, 'YYYY-MM-DD')) C
  316. ON 1 = 1
  317. WHERE A.BASE_DATE = '2024-04-01'
  318. AND B.DIM_CODE = 'DIM_PORT') T
  319. GROUP BY T.O_CODE,
  320. T.O_NAME,
  321. T.CUS_TYPE,
  322. T.BASE_DATE,
  323. T.DIM_OBJ_ID,
  324. T.DIM_CODE,
  325. T.IS_NEW_DATE,
  326. t.set_id,
  327. t.set_name) T1
  328. LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, O_CODE
  329. FROM CREDIT9144
  330. GROUP BY O_CODE) T2
  331. ON T1.O_CODE = T2.O_CODE
  332. LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, DIM_OBJ_ID
  333. FROM CREDIT9144
  334. GROUP BY DIM_OBJ_ID) T3
  335. ON T1.DIM_OBJ_ID = T3.DIM_OBJ_ID
  336. LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, SET_ID
  337. FROM CREDIT9144
  338. GROUP BY SET_ID) T4
  339. ON T1.O_CODE = T4.SET_ID) T1
  340. LEFT JOIN (SELECT DIM_OBJ_ID,
  341. WM_CONCAT(DISTINCT(TC.O_NAME)) || '(组合)' AS PORT_NAME,
  342. WM_CONCAT(DISTINCT(TD1.SD_NAME || '-' || TD2.SD_NAME || '-' ||
  343. TD3.SD_NAME)) PORT_DEPT,
  344. WM_CONCAT(DISTINCT(T3.DIM_CLASS_NAME)) PORT_CLASS
  345. FROM DIM_9999 T1
  346. LEFT JOIN TSYS_DEPT TD1
  347. ON T1.D_CORP = TD1.SD_ID
  348. LEFT JOIN TSYS_DEPT TD2
  349. ON T1.D_DEPT = TD2.SD_ID
  350. LEFT JOIN TSYS_DEPT TD3
  351. ON T1.D_BIZLINE = TD3.SD_ID
  352. LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME
  353. FROM T9X2CC) T3
  354. ON T1.P_CLASS = T3.DIM_CODE
  355. LEFT JOIN T2RRRR TC
  356. ON T1.CUST_CODE = TC.O_CODE
  357. GROUP BY DIM_OBJ_ID) T2
  358. ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
  359. LEFT JOIN (SELECT *
  360. FROM (SELECT T.*,
  361. ROW_NUMBER() OVER(PARTITION BY T.O_CODE ORDER BY T.BASEDATE DESC, T.BEG_DATE DESC, IMP_TIME DESC) AS SN
  362. FROM TCRT_RESULT T
  363. WHERE T.TMP_FLAG = '0'
  364. AND T.BEG_DATE <= '2024-04-01'
  365. AND T.END_DATE >= '2024-04-01') C
  366. WHERE C.SN = 1) T3
  367. ON T1.O_CODE = T3.O_CODE;

SQL真实执行计划:

  1. 1 #NSET2: [160580, 1, 3942]
  2. 2 #PIPE2: [160580, 1, 3942]
  3. 3 #PIPE2: [160579, 1, 3942]
  4. 4 #PIPE2: [160566, 1, 3942]
  5. 5 #PRJT2: [160557, 1, 3942]; exp_num(1), is_atom(FALSE)
  6. 6 #AAGR2: [160557, 1, 3942]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
  7. 7 #HASH RIGHT JOIN2: [160557, 362511, 3942]; key_num(1), ret_null(0), KEY(T3.O_CODE=T1.O_CODE)
  8. 8 #PRJT2: [24, 331, 528]; exp_num(1), is_atom(FALSE)
  9. 9 #SLCT2: [24, 331, 528]; C.SN = var15
  10. 10 #PRJT2: [24, 13278, 528]; exp_num(2), is_atom(FALSE)
  11. 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. 12 #SORT3: [24, 13278, 528]; key_num(4), is_distinct(FALSE), top_flag(0), is_adaptive(0)
  13. 13 #SLCT2: [24, 13278, 528]; (T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01')
  14. 14 #BLKUP2: [24, 13619, 528]; TCRT_RESULT_TMPFLAG(T)
  15. 15 #SSEK2: [24, 13619, 528]; scan_type(ASC), TCRT_RESULT_TMPFLAG(TCRT_RESULT as T), scan_range['0','0']
  16. 16 #PRJT2: [160355, 362511, 3414]; exp_num(1), is_atom(FALSE)
  17. 17 #HASH RIGHT JOIN2: [160355, 362511, 3414]; key_num(1), ret_null(0), KEY(T4.SET_ID=T1.O_CODE)
  18. 18 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
  19. 19 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.SET_ID)
  20. 20 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
  21. 21 #HASH RIGHT JOIN2: [160213, 362511, 2568]; key_num(1), ret_null(0), KEY(T3.DIM_OBJ_ID=T1.DIM_OBJ_ID)
  22. 22 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
  23. 23 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.DIM_OBJ_ID)
  24. 24 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
  25. 25 #HASH RIGHT JOIN2: [160110, 362511, 1722]; key_num(1), ret_null(0), KEY(T2.O_CODE=T1.O_CODE)
  26. 26 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
  27. 27 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.O_CODE)
  28. 28 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
  29. 29 #PRJT2: [160045, 362511, 876]; exp_num(2), is_atom(FALSE)
  30. 30 #PRJT2: [160045, 362511, 876]; exp_num(2), is_atom(FALSE)
  31. 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. 32 #PRJT2: [159784, 362511, 876]; exp_num(9), is_atom(FALSE)
  33. 33 #PRJT2: [159784, 362511, 876]; exp_num(9), is_atom(FALSE)
  34. 34 #UNION ALL: [159784, 362511, 876]
  35. 35 #PRJT2: [2907, 362494, 876]; exp_num(9), is_atom(FALSE)
  36. 36 #HASH2 INNER JOIN: [2907, 362494, 876]; KEY_NUM(1); KEY(A.DIM_OBJ_ID=C.DIM_OBJ_ID) KEY_NULL_EQU(0)
  37. 37 #PRJT2: [95, 69566, 294]; exp_num(2), is_atom(FALSE)
  38. 38 #BLKUP2: [95, 69566, 294]; TAWC_STATIC_RESULT_BASEDATE(A)
  39. 39 #SSEK2: [95, 69566, 294]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111 as A), scan_range['2024-04-01','2024-04-01']
  40. 40 #PRJT2: [2773, 172472, 582]; exp_num(6), is_atom(FALSE)
  41. 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. 42 #PRJT2: [2015, 4081172, 582]; exp_num(8), is_atom(FALSE)
  43. 43 #UNION ALL: [2015, 4081172, 582]
  44. 44 #PRJT2: [845, 4081171, 582]; exp_num(8), is_atom(FALSE)
  45. 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. 46 #CSCN2: [23, 172472, 144]; INDEX33559059(TAXC45200 as T2)
  47. 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. 48 #NEST LOOP INDEX JOIN2: [116, 1717, 582]
  49. 49 #SLCT2: [105, 1717, 390]; B.DIM_CODE = 'DIM_CORP'
  50. 50 #NEST LOOP INDEX JOIN2: [105, 1717, 390]
  51. 51 #PRJT2: [91, 1717, 246]; exp_num(2), is_atom(FALSE)
  52. 52 #SLCT2: [91, 1717, 246]; TAWC11111.AWC_QUOTA > var23
  53. 53 #BLKUP2: [91, 68827, 246]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
  54. 54 #SSEK2: [91, 68827, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01']
  55. 55 #BLKUP2: [11, 1, 48]; INDEX33559829(B)
  56. 56 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as B), scan_range[A.DIM_OBJ_ID,A.DIM_OBJ_ID]
  57. 57 #BLKUP2: [11, 1, 48]; INDEX33558827(C)
  58. 58 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[B.O_CODE,B.O_CODE]
  59. 59 #CSCN2: [75, 562125, 144]; INDEX33559061(VRG99999 as T1)
  60. 60 #PRJT2: [59, 1, 534]; exp_num(8), is_atom(FALSE)
  61. 61 #SLCT2: [59, 1, 534]; (A.BASE_DATE = T2.BASE_DATE AND A.DIM_OBJ_ID = B.DIM_OBJ_ID)
  62. 62 #NEST LOOP INNER JOIN2: [59, 1, 534]; [with var]
  63. 63 #HASH2 INNER JOIN: [40, 119, 288]; KEY_NUM(1); KEY(T2.SET_ID=B.VRG_SET_ID) KEY_NULL_EQU(0)
  64. 64 #SLCT2: [24, 1053, 144]; T2.BASE_DATE = '2024-04-01'
  65. 65 #CSCN2: [24, 172472, 144]; INDEX33559059(TAXC45200 as T2)
  66. 66 #BLKUP2: [13, 11989, 144]; IDX_YHY_24011901_01(B)
  67. 67 #SSEK2: [13, 11989, 144]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as B), scan_range['DIM_VRG','DIM_VRG']
  68. 68 #PRJT2: [1, 1, 246]; exp_num(2), is_atom(FALSE)
  69. 69 #SLCT2: [1, 1, 246]; (TAWC11111.AWC_QUOTA > var24 AND TAWC11111.BASE_DATE = var11)
  70. 70 #BLKUP2: [1, 2, 246]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
  71. 71 #SSEK2: [1, 2, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var12),('2024-04-01',var12)]
  72. 72 #PRJT2: [156751, 16, 1350]; exp_num(9), is_atom(FALSE)
  73. 73 #NEST LOOP LEFT JOIN2: [156751, 16, 1350]; join condition( TRUE ) partition_keys_num(0) ret_null(0)
  74. 74 #SLCT2: [73, 16, 1302]; A.DIM_OBJ_ID = B.DIM_OBJ_ID
  75. 75 #NEST LOOP INNER JOIN2: [73, 16, 1302]; [with var]
  76. 76 #PRJT2: [71, 21, 978]; exp_num(4), is_atom(FALSE)
  77. 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. 78 #SORT3: [71, 21, 978]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)
  79. 79 #INDEX JOIN LEFT JOIN2: [70, 96, 978] join condition(T1.BASE_DATE = '2024-04-01') ret_null(0)
  80. 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. 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. 82 #NEST LOOP INDEX JOIN2: [62, 9, 738]
  83. 83 #PRJT2: [61, 9, 642]; exp_num(3), is_atom(FALSE)
  84. 84 #DISTINCT: [61, 9, 642]
  85. 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. 86 #SLCT2: [59, 2, 354]; STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
  87. 87 #NEST LOOP INNER JOIN2: [59, 2, 354]; [with var]
  88. 88 #BLKUP2: [1, 52, 96]; IDX_YHY_24011901_01(A)
  89. 89 #SSEK2: [1, 52, 96]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as A), scan_range['DIM_PORT','DIM_PORT']
  90. 90 #PRJT2: [1, 2, 258]; exp_num(1), is_atom(FALSE)
  91. 91 #UNION FOR OR2: [1, 2, 258]; key_num(1), outer_join(-)
  92. 92 #SLCT2: [1, 1, 258]; TAWC11111.LIMIT_LINE > var26
  93. 93 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
  94. 94 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
  95. 95 #SLCT2: [1, 1, 258]; TAWC11111.AWC_QUOTA > var27
  96. 96 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
  97. 97 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
  98. 98 #CSCN2: [1, 374, 288]; INDEX33559058(DIM_9999 as B)
  99. 99 #BLKUP2: [1, 1, 48]; INDEX33558827(C)
  100. 100 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[A.CUST_CODE,A.CUST_CODE]
  101. 101 #PRJT2: [1, 98, 96]; exp_num(1), is_atom(FALSE)
  102. 102 #SORT3: [1, 98, 96]; key_num(2), is_distinct(TRUE), top_flag(0), is_adaptive(0)
  103. 103 #CSCN2: [1, 98, 96]; INDEX33559770(T9X2CC)
  104. 104 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T1)
  105. 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. 106 #BLKUP2: [1, 8, 48]; INDEX33559689(T2)
  107. 107 #SSEK2: [1, 8, 48]; scan_type(ASC), INDEX33559689(TAXC45200 as T2), scan_range[(T1.SET_ID,min),(T1.SET_ID,max))
  108. 108 #PRJT2: [1, 2, 324]; exp_num(2), is_atom(FALSE)
  109. 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. 110 #PRJT2: [793, 1, 48]; exp_num(1), is_atom(FALSE)
  111. 111 #AAGR2: [793, 1, 48]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
  112. 112 #SLCT2: [793, 6453684, 48]; TAWC11111.BASE_DATE <= var29
  113. 113 #SSCN: [793, 6385120, 48]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
  114. 114 #HEAP TABLE: [8, 4, 846]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
  115. 115 #PRJT2: [8, 4, 846]; exp_num(3), is_atom(FALSE)
  116. 116 #UNION FOR OR2: [8, 4, 846]; key_num(1), outer_join(-)
  117. 117 #UNION FOR OR2: [5, 3, 846]; key_num(1), outer_join(-)
  118. 118 #UNION FOR OR2: [2, 2, 846]; key_num(1), outer_join(-)
  119. 119 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896344441.colname=DMTEMPVIEW_896344370.TMPCOL5) KEY_NULL_EQU(0)
  120. 120 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
  121. 121 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 IS NULL
  122. 122 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  123. 123 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896344442.colname=DMTEMPVIEW_896344370.TMPCOL5) KEY_NULL_EQU(0)
  124. 124 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
  125. 125 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 > '2024-04-01'
  126. 126 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  127. 127 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896344370.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
  128. 128 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 IS NULL
  129. 129 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  130. 130 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1'
  131. 131 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
  132. 132 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896344370.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
  133. 133 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896344370.TMPCOL7 > '2024-04-01'
  134. 134 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  135. 135 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1'
  136. 136 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
  137. 137 #HEAP TABLE: [13, 1, 846]; table_no(1) full(0), mpp_full(0) autoid(1), sites(-)
  138. 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. 139 #INDEX JOIN LEFT JOIN2: [8, 1, 702] ret_null(0)
  140. 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. 141 #NEST LOOP INDEX JOIN2: [7, 1, 558]
  142. 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. 143 #CSCN2: [7, 33528, 462]; INDEX33559064(TAWXV999 as T1)
  144. 144 #BLKUP2: [1, 1, 48]; INDEX33559829(T2)
  145. 145 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T2), scan_range[T1.DIM_OBJ_ID,T1.DIM_OBJ_ID]
  146. 146 #SLCT2: [1, 22, 144]; (T3.AWC_TYPE >= 'AWC' AND T3.AWC_TYPE < 'AWD' AND T3.AWC_TYPE LIKE 'AWC_%')
  147. 147 #CSCN2: [1, 505, 144]; INDEX33559057(TAWC_RESULT_DETAIL as T3)
  148. 148 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T4), scan_range[T3.DIM_OBJ_ID,T3.DIM_OBJ_ID]
  149. 149 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T5)
  150. 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. 151 #SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
  152. 152 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
  153. 153 #AAGR2: [1, 1, 96]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
  154. 154 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)
  155. 155 #SLCT2: [1, 1, 96]; exp_sfun1 <= var32
  156. 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. 157 #SORT3: [1, 7, 96]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
  158. 158 #SLCT2: [1, 7, 96]; TN.DIM_OBJ_ID = var10
  159. 159 #CSCN2: [1, 374, 96]; INDEX33559058(DIM_9999 as TN)
  160. --ET
  161. 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
  162. ---------- ---------- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- --------------------
  163. 1 HSCN 1 0% 145 126 2 0 0 0 0 NULL NULL 0
  164. 2 PRJT2 1 0% 145 22 4 0 0 0 0 NULL NULL 0
  165. 3 SPL2 1 0% 145 151 1 0 0 0 0 NULL NULL 0
  166. 4 PRJT2 1 0% 145 16 6 0 0 0 0 NULL NULL 0
  167. 5 PRJT2 1 0% 145 29 6 0 0 0 0 NULL NULL 0
  168. 6 PRJT2 1 0% 145 40 6 0 0 0 0 NULL NULL 0
  169. 7 HSCN 1 0% 145 122 2 0 0 0 0 NULL NULL 0
  170. 8 PRJT2 1 0% 145 18 4 0 0 0 0 NULL NULL 0
  171. 9 CONSTV 2 0% 139 120 2 0 0 0 0 NULL NULL 0
  172. 10 PIPE2 2 0% 139 4 5 0 0 0 0 NULL NULL 0
  173. 11 PRJT2 2 0% 139 5 4 0 0 0 0 NULL NULL 0
  174. 12 PRJT2 2 0% 139 30 6 0 0 0 0 NULL NULL 0
  175. 13 PIPE2 2 0% 139 2 5 0 0 0 0 NULL NULL 0
  176. 14 PRJT2 2 0% 139 83 4 0 0 0 0 NULL NULL 0
  177. 15 PIPE2 3 0% 132 3 5 0 0 0 0 NULL NULL 0
  178. 16 HSCN 3 0% 132 24 2 0 0 0 0 NULL NULL 0
  179. 17 HSCN 3 0% 132 20 3 0 0 0 0 NULL NULL 0
  180. 18 PRJT2 3 0% 132 101 4 0 0 0 0 NULL NULL 0
  181. 19 PRJT2 3 0% 132 26 4 0 0 0 0 NULL NULL 0
  182. 20 HSCN 3 0% 132 28 2 0 0 0 0 NULL NULL 0
  183. 21 HSCN 3 0% 132 129 2 0 0 0 0 NULL NULL 0
  184. 22 UNION_OR2 4 0% 128 118 5 0 0 0 0 NULL NULL 0
  185. 23 CONSTV 4 0% 128 124 2 0 0 0 0 NULL NULL 0
  186. 24 PRJT2 4 0% 128 115 8 0 0 0 0 NULL NULL 0
  187. 25 SLCT2 4 0% 128 121 4 0 0 0 0 NULL NULL 0
  188. 26 AAGR2 5 0% 126 6 5 0 0 0 0 NULL NULL 0
  189. 27 HSCN 5 0% 126 134 3 0 0 0 0 NULL NULL 0
  190. 28 DLCK 6 0% 124 0 2 0 0 0 0 NULL NULL 0
  191. 29 SLCT2 6 0% 124 128 4 0 0 0 0 NULL NULL 0
  192. 30 PRJT2 7 0% 122 44 26 0 0 0 0 NULL NULL 0
  193. 31 SLCT2 7 0% 122 125 4 0 0 0 0 NULL NULL 0
  194. 32 PRJT2 10 0% 121 8 90 0 0 0 0 NULL NULL 0
  195. 33 SLCT2 11 0% 119 135 2 0 0 0 0 NULL NULL 0
  196. 34 SLCT2 11 0% 119 130 4 0 0 0 0 NULL NULL 0
  197. 35 SLCT2 16 0% 118 133 4 0 0 0 0 NULL NULL 0
  198. 36 UNION_OR2 17 0% 115 117 7 0 0 0 0 NULL NULL 0
  199. 37 PRJT2 17 0% 115 10 90 0 0 0 0 NULL NULL 0
  200. 38 SLCT2 17 0% 115 86 44 0 0 0 0 NULL NULL 0
  201. 39 HRS2 19 0% 113 119 6 224 0 2 0 NULL NULL 0
  202. 40 HTAB 19 0% 113 114 6 0 0 0 0 NULL NULL 0
  203. 41 UNION_OR2 20 0% 112 116 9 0 0 0 0 NULL NULL 0
  204. 42 PRJT2 21 0% 111 33 56 0 0 0 0 NULL NULL 0
  205. 43 PRJT2 22 0% 110 108 88 0 0 0 0 NULL NULL 0
  206. 44 PRJT2 23 0% 109 32 56 0 0 0 0 NULL NULL 0
  207. 45 SSEK2 27 0% 108 89 2 0 0 0 0 NULL NULL 0
  208. 46 CSCN2 28 0% 106 103 2 0 0 0 0 NULL NULL 0
  209. 47 PRJT2 28 0% 106 90 146 0 0 0 0 NULL NULL 0
  210. 48 PRJT2 29 0% 105 76 44 0 0 0 0 NULL NULL 0
  211. 49 UNION_OR2 33 0% 104 91 198 0 0 0 0 NULL NULL 0
  212. 50 UNION_ALL2 36 0% 102 34 57 0 0 0 0 NULL NULL 0
  213. 51 UNION_ALL2 36 0% 102 43 269 0 0 0 0 NULL NULL 0
  214. 52 HRS2 39 0% 101 123 5 224 0 2 0 NULL NULL 0
  215. 53 NLI2 43 0% 100 87 97 0 0 0 0 NULL NULL 0
  216. 54 IJLO2 50 0% 99 139 244 0 0 0 0 NULL NULL 0
  217. 55 HTAB 52 0% 98 137 12 0 0 0 0 NULL NULL 0
  218. 56 PRJT2 61 0% 97 42 268 0 0 0 0 NULL NULL 0
  219. 57 SLCT2 71 0% 95 74 46 0 0 0 0 NULL NULL 0
  220. 58 SLCT2 71 0% 95 92 125 0 0 0 0 NULL NULL 0
  221. 59 SLCT2 73 0% 94 95 146 0 0 0 0 NULL NULL 0
  222. 60 CSCN2 74 0% 93 136 1 0 0 0 0 NULL NULL 0
  223. 61 PRJT2 75 0% 92 110 88 0 0 0 0 NULL NULL 0
  224. 62 CSCN2 77 0% 90 131 2 0 0 0 0 NULL NULL 0
  225. 63 IJI2 77 0% 90 141 260 0 0 0 0 NULL NULL 0
  226. 64 SLCT2 88 0% 89 146 6 0 0 0 0 NULL NULL 0
  227. 65 PRJT2 89 0% 88 51 400 0 0 0 0 NULL NULL 0
  228. 66 PRJT2 96 0% 87 37 462 0 0 0 0 NULL NULL 0
  229. 67 BLKUP2 104 0% 86 93 146 0 0 0 0 NULL NULL 0
  230. 68 NLI2 116 0% 85 75 89 0 0 0 0 NULL NULL 0
  231. 69 SLCT2 121 0% 84 9 90 0 0 0 0 NULL NULL 0
  232. 70 CSCN2 122 0% 83 147 3 0 0 0 0 NULL NULL 0
  233. 71 NLLO2 124 0% 82 73 90 0 0 0 0 NULL NULL 0
  234. 72 SLCT2 134 0% 81 61 244 0 0 0 0 NULL NULL 0
  235. 73 CSCN2 136 0% 79 98 3 0 0 0 0 NULL NULL 0
  236. 74 BLKUP2 136 0% 79 96 146 0 0 0 0 NULL NULL 0
  237. 75 DIST 138 0% 78 84 5 17 0 242 34 NULL NULL 0
  238. 76 IJI2 144 0% 77 82 831 0 0 0 0 NULL NULL 0
  239. 77 PRJT2 151 0% 76 60 244 0 0 0 0 NULL NULL 0
  240. 78 BLKUP2 154 0% 75 88 4 0 0 0 0 NULL NULL 0
  241. 79 SORT3 226 0% 74 102 4 49472 0 0 0 NULL NULL 0
  242. 80 HAGR2 230 0% 73 27 4 1654 0 64 0 NULL NULL 0
  243. 81 NSET2 241 0% 72 1 3 0 0 0 0 NULL NULL 0
  244. 82 HAGR2 254 0% 71 19 4 1653 0 46 0 NULL NULL 0
  245. 83 HAGR2 259 0% 70 23 4 1654 0 69 0 NULL NULL 0
  246. 84 SLCT2 261 0% 69 142 118 0 0 0 0 NULL NULL 0
  247. 85 PRJT2 368 0% 68 68 2310 0 0 0 0 NULL NULL 0
  248. 86 SSEK2 377 0% 67 94 73 0 0 0 0 NULL NULL 0
  249. 87 PRJT2 391 0% 66 72 46 0 0 0 0 NULL NULL 0
  250. 88 IJI2 409 0% 65 48 2410 0 0 0 0 NULL NULL 0
  251. 89 SSEK2 473 0% 64 150 13 0 0 0 0 NULL NULL 0
  252. 90 SLCT2 476 0% 63 13 96 0 0 0 0 NULL NULL 0
  253. 91 BLKUP2 486 0% 62 144 340 0 0 0 0 NULL NULL 0
  254. 92 SSEK2 491 0% 61 105 13 0 0 0 0 NULL NULL 0
  255. 93 SSEK2 560 0.01% 60 97 73 0 0 0 0 NULL NULL 0
  256. 94 PRJT2 603 0.01% 59 35 12 0 0 0 0 NULL NULL 0
  257. 95 SSEK2 646 0.01% 58 148 155 0 0 0 0 NULL NULL 0
  258. 96 NLI2 699 0.01% 57 62 1291 0 0 0 0 NULL NULL 0
  259. 97 SSEK2 774 0.01% 56 145 170 0 0 0 0 NULL NULL 0
  260. 98 HAGR2 869 0.01% 55 41 137 1668 0 424 0 NULL NULL 0
  261. 99 IJI2 884 0.01% 54 50 5424 0 0 0 0 NULL NULL 0
  262. 100 SSEK2 906 0.01% 53 109 44 0 0 0 0 NULL NULL 0
  263. 101 HRO2 916 0.01% 52 25 8 11950 0 64 0 NULL NULL 0
  264. 102 BLKUP2 929 0.01% 51 99 1104 0 0 0 0 NULL NULL 0
  265. 103 HRO2 944 0.01% 50 17 8 11950 0 46 0 NULL NULL 0
  266. 104 HI3 957 0.01% 49 85 28 16046 0 21 0 NULL NULL 0
  267. 105 HLS2 971 0.01% 48 132 5 16558 0 8 0 NULL NULL 0
  268. 106 HRO2 982 0.01% 47 21 8 11950 0 69 0 NULL NULL 0
  269. 107 SLCT2 1025 0.01% 46 52 431 0 0 0 0 NULL NULL 0
  270. 108 HLS2 1088 0.01% 45 127 6 16558 0 73 0 NULL NULL 0
  271. 109 AFUN 1126 0.01% 44 11 90 0 0 0 0 NULL NULL 0
  272. 110 SLCT2 1326 0.01% 43 49 2345 0 0 0 0 NULL NULL 0
  273. 111 SSEK2 1540 0.01% 42 100 552 0 0 0 0 NULL NULL 0
  274. 112 SSEK2 1649 0.01% 41 67 41 0 0 0 0 NULL NULL 0
  275. 113 HAGR2 1751 0.02% 40 31 31 1669 0 444 1 NULL NULL 0
  276. 114 SLCT2 1759 0.02% 39 69 3223 0 0 0 0 NULL NULL 0
  277. 115 SSEK2 1867 0.02% 38 15 48 0 0 0 0 NULL NULL 0
  278. 116 SLCT2 1901 0.02% 37 64 585 0 0 0 0 NULL NULL 0
  279. 117 HRO2 2055 0.02% 36 7 51 11950 0 13017 158 NULL NULL 0
  280. 118 HI3 2124 0.02% 35 63 60 16046 0 1047 0 NULL NULL 0
  281. 119 SAGR2 2598 0.02% 34 77 92 0 0 0 0 NULL NULL 0
  282. 120 SSEK2 2658 0.02% 33 107 524 0 0 0 0 NULL NULL 0
  283. 121 HLO2 3097 0.03% 32 81 281 16558 0 13 0 NULL NULL 275
  284. 122 HLO2 3193 0.03% 31 140 92 16558 0 84 0 NULL NULL 0
  285. 123 HLO2 3272 0.03% 30 138 108 12462 0 65 0 NULL NULL 29
  286. 124 BLKUP2 3375 0.03% 29 57 2408 0 0 0 0 NULL NULL 0
  287. 125 HLO2 3396 0.03% 28 80 21 12462 0 20 0 NULL NULL 275
  288. 126 SSEK2 4182 0.04% 27 58 1204 0 0 0 0 NULL NULL 0
  289. 127 IJLO2 4622 0.04% 26 79 807 0 0 0 0 NULL NULL 0
  290. 128 BLKUP2 7286 0.07% 25 70 4136 0 0 0 0 NULL NULL 0
  291. 129 SORT3 7495 0.07% 24 12 93 59392 0 0 0 NULL NULL 0
  292. 130 BLKUP2 7522 0.07% 23 149 26 0 0 0 0 NULL NULL 0
  293. 131 BLKUP2 7943 0.07% 22 104 26 0 0 0 0 NULL NULL 0
  294. 132 BLKUP2 8173 0.07% 21 55 6964 0 0 0 0 NULL NULL 0
  295. 133 SSEK2 9143 0.08% 20 54 231 0 0 0 0 NULL NULL 0
  296. 134 SSEK2 9206 0.08% 19 39 231 0 0 0 0 NULL NULL 0
  297. 135 SSEK2 10375 0.09% 18 71 2068 0 0 0 0 NULL NULL 0
  298. 136 SORT3 10605 0.1% 17 78 347 116736 0 0 0 NULL NULL 0
  299. 137 CSCN2 11154 0.1% 16 143 113 0 0 0 0 NULL NULL 0
  300. 138 HI3 12422 0.11% 15 36 240 40622 0 27630 698 NULL NULL 62610
  301. 139 SSEK2 14431 0.13% 14 56 3482 0 0 0 0 NULL NULL 0
  302. 140 BLKUP2 26941 0.24% 13 66 82 0 0 0 0 NULL NULL 0
  303. 141 BLKUP2 33447 0.3% 12 14 96 0 0 0 0 NULL NULL 0
  304. 142 CSCN2 36753 0.33% 11 46 580 0 0 0 0 NULL NULL 0
  305. 143 CSCN2 37268 0.34% 10 65 580 0 0 0 0 NULL NULL 0
  306. 144 HRO2 37567 0.34% 9 45 606 73971 0 161179 12340 NULL NULL 0
  307. 145 HLO2 41093 0.37% 8 47 2503 12718 0 303 0 NULL NULL 597
  308. 146 BLKUP2 44604 0.4% 7 106 1048 0 0 0 0 NULL NULL 0
  309. 147 CSCN2 124217 1.12% 6 59 1887 0 0 0 0 NULL NULL 0
  310. 148 BLKUP2 156910 1.41% 5 38 462 0 0 0 0 NULL NULL 0
  311. 149 BLKUP2 166844 1.5% 4 53 462 0 0 0 0 NULL NULL 0
  312. 150 AAGR2 1555430 14% 3 111 473352 0 0 0 0 NULL NULL 0
  313. 151 SLCT2 1986956 17.89% 2 112 946616 0 0 0 0 NULL NULL 0
  314. 152 SSCN 6673379 60.09% 1 113 473308 0 0 0 0 NULL NULL 0
  315.  
  316. 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秒。

  1. SELECT T.O_CODE AS ID,
  2. T.O_NAME AS CORP_NAME,
  3. (SELECT MAX(PARENTID) PARENTID
  4. FROM (SELECT CASE MAX(DIM_OBJ_ID)
  5. WHEN NULL THEN
  6. ''
  7. ELSE
  8. MAX(TN.CUST_CODE)
  9. END PARENTID
  10. FROM DIM_9999 TN
  11. WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
  12. GROUP BY TN.DIM_OBJ_ID
  13. HAVING COUNT(DISTINCT(CUST_CODE)) <= 1) A) PARENTID,
  14. T.O_CODE,
  15. T.BASE_DATE,
  16. T.DIM_OBJ_ID,
  17. T.CUS_TYPE,
  18. T.O_NAME AS CUS_NAME,
  19. T.DIM_CODE,
  20. T.IS_NEW_DATE,
  21. TO_CHAR(SUM(CASE
  22. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  23. NVL(LIMIT_LINE, 0)
  24. ELSE
  25. 0
  26. END) / 10000,
  27. 'fm999999999999999990.00') AS LIMIT_LINE,
  28. TO_CHAR(SUM(CASE
  29. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  30. NVL(AWC_QUOTA, 0)
  31. ELSE
  32. 0
  33. END) / 10000,
  34. 'fm999999999999999990.00') AS AWC_QUOTA,
  35. TO_CHAR(SUM(CASE
  36. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  37. NVL(OCC_QUOTA, 0)
  38. ELSE
  39. 0
  40. END) / 10000,
  41. 'fm999999999999999990.00') AS OCC_QUOTA,
  42. TO_CHAR(SUM(CASE
  43. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  44. NVL(LIMIT_LINE_RATE, 0) * 100
  45. ELSE
  46. 0
  47. END),
  48. 'fm999999999999999990.00') AS LIMIT_LINE_RATE,
  49. TO_CHAR(GREATEST(SUM(CASE
  50. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  51. NVL(LIMIT_LINE_ABLE, 0)
  52. ELSE
  53. 0
  54. END) / 10000,
  55. 0),
  56. 'fm999999999999999990.00') AS LIMIT_LINE_ABLE,
  57. TO_CHAR(SUM(CASE
  58. WHEN T.AWC_TYPE = 'CE_GENERAL' THEN
  59. NVL(AWC_QUOTA, 0)
  60. ELSE
  61. 0
  62. END) / 10000,
  63. 'fm999999999999999990.00') AS CE_QUOTA,
  64. TO_CHAR(SUM(CASE
  65. WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN
  66. NVL(AWC_QUOTA, 0)
  67. ELSE
  68. 0
  69. END) / 10000,
  70. 'fm999999999999999990.00') AS ELUL_QUOTA,
  71. SUM(CASE
  72. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
  73. NVL(AWC_QUOTA_ABLE, 0)
  74. ELSE
  75. 0
  76. END) AS AWC_QUOTA_ABLE,
  77. t.set_id,
  78. t.set_name
  79. FROM (SELECT A.AWC_TYPE,
  80. C.O_CODE,
  81. C.O_NAME,
  82. A.AWC_QUOTA,
  83. A.OCC_QUOTA,
  84. A.LIMIT_LINE_ABLE,
  85. A.LIMIT_LINE_RATE,
  86. A.LIMIT_LINE,
  87. A.BASE_DATE,
  88. A.DIM_OBJ_ID,
  89. C.CUS_TYPE,
  90. 'DIM_CORP' AS DIM_CODE,
  91. NULL AS AWC_QUOTA_ABLE,
  92. '' AS IS_NEW_DATE,
  93. c.set_id,
  94. c.set_name
  95. FROM (SELECT A.AWC_TYPE,
  96. A.AWC_QUOTA,
  97. A.OCC_QUOTA,
  98. A.LIMIT_LINE_ABLE,
  99. A.LIMIT_LINE_RATE,
  100. A.LIMIT_LINE,
  101. A.BASE_DATE,
  102. A.DIM_OBJ_ID
  103. FROM TAWC11111 A
  104. WHERE A.BASE_DATE = '2024-04-01') A
  105. INNER JOIN CORP11111 C
  106. ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
  107. WHERE A.BASE_DATE = '2024-04-01'
  108. UNION ALL
  109. SELECT A.AWC_TYPE,
  110. B.CUST_CODE AS O_CODE,
  111. B.O_NAME || '(组合)' AS NAME,
  112. A.AWC_QUOTA,
  113. A.OCC_QUOTA,
  114. A.LIMIT_LINE_ABLE,
  115. A.LIMIT_LINE_RATE,
  116. A.LIMIT_LINE,
  117. A.BASE_DATE,
  118. A.DIM_OBJ_ID,
  119. B.CUS_TYPE,
  120. 'DIM_PORT' AS DIM_CODE,
  121. A.AWC_QUOTA_ABLE,
  122. CASE
  123. WHEN C.MAX_BASE_DATE = '2024-04-01' THEN
  124. '1'
  125. ELSE
  126. '0'
  127. END AS IS_NEW_DATE,
  128. '' AS set_id,
  129. '' AS set_name
  130. FROM (SELECT A.AWC_TYPE,
  131. A.AWC_QUOTA,
  132. A.OCC_QUOTA,
  133. A.LIMIT_LINE_ABLE,
  134. A.LIMIT_LINE_RATE,
  135. A.LIMIT_LINE,
  136. A.BASE_DATE,
  137. A.DIM_OBJ_ID,
  138. A.AWC_QUOTA_ABLE
  139. FROM TAWC11111 A
  140. WHERE A.BASE_DATE = '2024-04-01') A
  141. INNER JOIN PORT_11112 B
  142. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  143. LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE
  144. FROM TAWC11111
  145. WHERE BASE_DATE <=
  146. TO_CHAR(SYSDATE, 'YYYY-MM-DD')) C
  147. ON 1 = 1
  148. WHERE A.BASE_DATE = '2024-04-01'
  149. AND B.DIM_CODE = 'DIM_PORT') T
  150. GROUP BY T.O_CODE,
  151. T.O_NAME,
  152. T.CUS_TYPE,
  153. T.BASE_DATE,
  154. T.DIM_OBJ_ID,
  155. T.DIM_CODE,
  156. T.IS_NEW_DATE,
  157. t.set_id,
  158. t.set_name

       看到现在应该会有杠精同学问我,那这里为什么会慢?是不是写SQL的研发太垃圾了?还是DM数据库太垃圾了?

  可以明确回答,因为我看不懂 DM 的执行计划所以不知道上面为什么慢,不知道CBO做了什么查询转换这些动作。

  但是可以明确的是,写SQL开发不垃圾,能写出这么复杂的统计SQL也是很厉害的。

       但是DM确实有点拉胯,执行计划让人看不懂这就说不过去了,DM的执行计划是我目前遇到数据库中最难看懂的计划,就像上面的SQL,想分析些CBO做了什么查询转换都分析不了,因为看不懂,只能靠猜。

 

SQL改写优化:

  1. WITH CORP11111 AS
  2. (SELECT T.O_CODE,
  3. T.O_NAME,
  4. T.CUS_TYPE,
  5. T.O_TYPE,
  6. T.SET_NAME,
  7. T.SET_ID,
  8. T.DIM_CODE,
  9. T.DIM_OBJ_ID
  10. FROM (SELECT C.O_CODE,
  11. C.CUS_TYPE,
  12. C.O_TYPE,
  13. C.O_NAME,
  14. T2.SET_NAME,
  15. T2.SET_ID,
  16. B.DIM_CODE,
  17. B.DIM_OBJ_ID,
  18. '' AS D_DEPT,
  19. '' AS D_NAME,
  20. '' AS D_BIZLINE,
  21. '' AS P_CLASS,
  22. '' AS P_CLASS_NAME
  23. FROM (SELECT DIM_OBJ_ID,
  24. BASE_DATE,
  25. LIMIT_LINE,
  26. AWC_QUOTA,
  27. OCC_QUOTA,
  28. LIMIT_LINE_ABLE,
  29. LIMIT_LINE_RATE
  30. FROM TAWC11111
  31. WHERE BASE_DATE = '2024-04-01') A
  32. INNER JOIN TAWC22222 B
  33. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  34. INNER JOIN T2RRRR C
  35. ON B.O_CODE = C.O_CODE
  36. LEFT JOIN VRG99999 T1
  37. ON C.O_CODE = T1.O_CODE
  38. AND T1.BASE_DATE = A.BASE_DATE
  39. LEFT JOIN TAXC45200 T2
  40. ON T1.SET_ID = T2.SET_ID
  41. AND T2.BASE_DATE = A.BASE_DATE
  42. WHERE A.BASE_DATE = '2024-04-01'
  43. AND B.DIM_CODE = 'DIM_CORP'
  44. AND A.AWC_QUOTA > 0
  45. UNION ALL
  46. SELECT T2.SET_ID AS O_CODE,
  47. '集团' AS CUS_TYPE,
  48. 'group' AS O_TYPE,
  49. '' AS O_NAME,
  50. T2.SET_NAME,
  51. T2.SET_ID,
  52. B.DIM_CODE,
  53. B.DIM_OBJ_ID,
  54. '' AS D_DEPT,
  55. '' AS D_NAME,
  56. '' AS D_BIZLINE,
  57. '' AS P_CLASS,
  58. '' AS P_CLASS_NAME
  59. FROM (SELECT DIM_OBJ_ID,
  60. BASE_DATE,
  61. LIMIT_LINE,
  62. AWC_QUOTA,
  63. OCC_QUOTA,
  64. LIMIT_LINE_ABLE,
  65. LIMIT_LINE_RATE
  66. FROM TAWC11111
  67. WHERE BASE_DATE = '2024-04-01') A
  68. INNER JOIN TAWC22222 B
  69. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  70. INNER JOIN TAXC45200 T2
  71. ON B.VRG_SET_ID = T2.SET_ID
  72. AND A.BASE_DATE = T2.BASE_DATE
  73. WHERE A.BASE_DATE = '2024-04-01'
  74. AND B.DIM_CODE = 'DIM_VRG'
  75. AND A.AWC_QUOTA > 0) T
  76. WHERE 1 = 1
  77. GROUP BY O_CODE,
  78. O_NAME,
  79. CUS_TYPE,
  80. O_TYPE,
  81. SET_NAME,
  82. SET_ID,
  83. DIM_CODE,
  84. DIM_OBJ_ID)
  85. ,
  86. PORT_11112 AS
  87. (SELECT A.DIM_OBJ_ID AS CUST_CODE,
  88. '' CUS_TYPE,
  89. '2' O_TYPE,
  90. WM_CONCAT(DISTINCT (C.O_NAME)) O_NAME,
  91. WM_CONCAT(DISTINCT (A.D_DEPT)) D_DEPT,
  92. WM_CONCAT(DISTINCT (A.D_NAME)) D_NAME,
  93. WM_CONCAT(DISTINCT (A.D_BIZLINE)) D_BIZLINE,
  94. WM_CONCAT(DISTINCT (A.P_CLASS)) P_CLASS,
  95. WM_CONCAT(DISTINCT (DIM_CLASS_NAME)) P_CLASS_NAME,
  96. 'DIM_PORT' DIM_CODE,
  97. A.DIM_OBJ_ID,
  98. T2.SET_NAME
  99. FROM (SELECT DISTINCT A.DIM_OBJ_ID,
  100. A.DIM_CODE,
  101. B.CUST_CODE,
  102. B.D_DEPT,
  103. B.D_NAME,
  104. B.D_BIZLINE,
  105. B.P_CLASS
  106. FROM (SELECT AWC_QUOTA,
  107. LIMIT_LINE,
  108. BASE_DATE,
  109. DIM_OBJ_ID,
  110. OCC_QUOTA,
  111. LIMIT_LINE_ABLE,
  112. LIMIT_LINE_RATE
  113. FROM TAWC11111
  114. WHERE BASE_DATE = '2024-04-01') STATS
  115. INNER JOIN TAWC22222 A
  116. ON STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
  117. INNER JOIN DIM_9999 B
  118. ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  119. WHERE A.DIM_CODE = 'DIM_PORT'
  120. AND STATS.BASE_DATE = '2024-04-01'
  121. AND (STATS.LIMIT_LINE > 0
  122. OR STATS.AWC_QUOTA > 0)) A
  123. INNER JOIN T2RRRR C
  124. ON A.CUST_CODE = C.O_CODE
  125. LEFT JOIN (SELECT DISTINCT DIM_CODE,
  126. DIM_CLASS_NAME
  127. FROM T9X2CC
  128. ORDER BY DIM_CODE ASC) D
  129. ON A.P_CLASS = D.DIM_CODE
  130. LEFT JOIN VRG99999 T1
  131. ON C.O_CODE = T1.O_CODE
  132. AND T1.BASE_DATE = '2024-04-01'
  133. LEFT JOIN TAXC45200 T2
  134. ON T1.SET_ID = T2.SET_ID
  135. AND T1.BASE_DATE = '2024-04-01'
  136. WHERE 1 = 1
  137. GROUP BY A.DIM_OBJ_ID,
  138. T2.SET_NAME)
  139. ,
  140. CREDIT9144 AS
  141. (SELECT DECODE(T1.ADJ_TYPE,
  142. '调减', -1,
  143. 1) * T1.ADJ_LINE AS ADJ_LINE,
  144. T2.O_CODE,
  145. T1.INST_ID,
  146. T4.DIM_OBJ_ID,
  147. T5.SET_ID
  148. FROM TAWXV999 T1
  149. INNER JOIN TAWC22222 T2
  150. ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
  151. LEFT JOIN TAWC_RESULT_DETAIL T3
  152. ON T1.INST_ID = T3.INST_ID
  153. AND T3.AWC_TYPE LIKE 'AWC_%'
  154. LEFT JOIN TAWC22222 T4
  155. ON T3.DIM_OBJ_ID = T4.DIM_OBJ_ID
  156. LEFT JOIN VRG99999 T5
  157. ON T2.O_CODE = T5.O_CODE
  158. AND T5.BASE_DATE = '2024-04-01'
  159. WHERE T1.DATA_SOURCE = 'ADJ'
  160. AND T1.TMP_FLAG = '0'
  161. AND T1.AWC_TYPE = 'AWC_CREDIT'
  162. AND (T1.REMARK = '初始化-占用信用债'
  163. OR T1.REMARK = '释放信用债'
  164. OR T1.INST_ID IN (SELECT INST_ID FROM CFLSSX WHERE AWC_CREDIT_FLAG = '1'))
  165. AND T1.ADJ_BEG_DATE <= '2024-04-01'
  166. AND (T1.ADJ_END_DATE IS NULL
  167. OR T1.ADJ_END_DATE > '2024-04-01'))
  168. , X AS (
  169. SELECT * FROM TAWC11111 WHERE BASE_DATE = '2024-04-01' and TO_CHAR(SYSDATE, 'YYYY-MM-DD')
  170. )
  171. SELECT COUNT(1) FROM (
  172. SELECT T1.*,
  173. TO_CHAR(DECODE(T1.DIM_CODE,
  174. 'DIM_PORT', T3.ADJ_LINE,
  175. DECODE(CUS_TYPE,
  176. '集团', T4.ADJ_LINE,
  177. T2.ADJ_LINE)) / 10000, 'fm999999999999999990.00') AS ADJ_LINE
  178. FROM (
  179. SELECT T.O_CODE AS ID,
  180. T.O_NAME AS CORP_NAME,
  181. (SELECT MAX(PARENTID) PARENTID
  182. FROM (SELECT CASE MAX(DIM_OBJ_ID) WHEN NULL THEN '' ELSE MAX(TN.CUST_CODE) END PARENTID
  183. FROM DIM_9999 TN
  184. WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
  185. GROUP BY TN.DIM_OBJ_ID
  186. HAVING COUNT(DISTINCT (CUST_CODE)) <= 1) A) PARENTID,
  187. T.O_CODE,
  188. T.BASE_DATE,
  189. T.DIM_OBJ_ID,
  190. T.CUS_TYPE,
  191. T.O_NAME AS CUS_NAME,
  192. T.DIM_CODE,
  193. T.IS_NEW_DATE,
  194. TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE, 0) ELSE 0 END) / 10000,
  195. 'fm999999999999999990.00') AS LIMIT_LINE,
  196. TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000,
  197. 'fm999999999999999990.00') AS AWC_QUOTA,
  198. TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(OCC_QUOTA, 0) ELSE 0 END) / 10000,
  199. 'fm999999999999999990.00') AS OCC_QUOTA,
  200. TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_RATE, 0) * 100 ELSE 0 END),
  201. 'fm999999999999999990.00') AS LIMIT_LINE_RATE,
  202. TO_CHAR(GREATEST(SUM(CASE
  203. WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_ABLE, 0)
  204. ELSE 0 END) / 10000, 0), 'fm999999999999999990.00') AS LIMIT_LINE_ABLE,
  205. TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'CE_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000,
  206. 'fm999999999999999990.00') AS CE_QUOTA,
  207. TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000,
  208. 'fm999999999999999990.00') AS ELUL_QUOTA,
  209. SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA_ABLE, 0) ELSE 0 END) AS AWC_QUOTA_ABLE,
  210. t.set_id,
  211. t.set_name
  212. FROM (
  213. SELECT A.AWC_TYPE,
  214. C.O_CODE,
  215. C.O_NAME,
  216. A.AWC_QUOTA,
  217. A.OCC_QUOTA,
  218. A.LIMIT_LINE_ABLE,
  219. A.LIMIT_LINE_RATE,
  220. A.LIMIT_LINE,
  221. A.BASE_DATE,
  222. A.DIM_OBJ_ID,
  223. C.CUS_TYPE,
  224. 'DIM_CORP' AS DIM_CODE,
  225. NULL AS AWC_QUOTA_ABLE,
  226. '' AS IS_NEW_DATE,
  227. c.set_id,
  228. c.set_name
  229. FROM (SELECT A.AWC_TYPE,
  230. A.AWC_QUOTA,
  231. A.OCC_QUOTA,
  232. A.LIMIT_LINE_ABLE,
  233. A.LIMIT_LINE_RATE,
  234. A.LIMIT_LINE,
  235. A.BASE_DATE,
  236. A.DIM_OBJ_ID
  237. FROM X A
  238. WHERE A.BASE_DATE = '2024-04-01') A
  239. INNER JOIN CORP11111 C
  240. ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
  241. WHERE A.BASE_DATE = '2024-04-01'
  242. UNION ALL
  243. SELECT A.AWC_TYPE,
  244. B.CUST_CODE AS O_CODE,
  245. B.O_NAME || '(组合)' AS NAME,
  246. A.AWC_QUOTA,
  247. A.OCC_QUOTA,
  248. A.LIMIT_LINE_ABLE,
  249. A.LIMIT_LINE_RATE,
  250. A.LIMIT_LINE,
  251. A.BASE_DATE,
  252. A.DIM_OBJ_ID,
  253. B.CUS_TYPE,
  254. 'DIM_PORT' AS DIM_CODE,
  255. A.AWC_QUOTA_ABLE,
  256. CASE WHEN C.MAX_BASE_DATE = '2024-04-01' THEN '1' ELSE '0' END AS IS_NEW_DATE,
  257. '' AS set_id,
  258. '' AS set_name
  259. FROM (
  260. SELECT A.AWC_TYPE,
  261. A.AWC_QUOTA,
  262. A.OCC_QUOTA,
  263. A.LIMIT_LINE_ABLE,
  264. A.LIMIT_LINE_RATE,
  265. A.LIMIT_LINE,
  266. A.BASE_DATE,
  267. A.DIM_OBJ_ID,
  268. A.AWC_QUOTA_ABLE
  269. FROM X A WHERE A.BASE_DATE = '2024-04-01'
  270. ) A
  271. INNER JOIN PORT_11112 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
  272. LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE FROM X WHERE BASE_DATE <= TO_CHAR(SYSDATE, 'YYYY-MM-DD')
  273. ) C
  274. ON 1 = 1
  275. WHERE A.BASE_DATE = '2024-04-01'
  276. AND B.DIM_CODE = 'DIM_PORT'
  277. ) T
  278. GROUP BY T.O_CODE,
  279. T.O_NAME,
  280. T.CUS_TYPE,
  281. T.BASE_DATE,
  282. T.DIM_OBJ_ID,
  283. T.DIM_CODE,
  284. T.IS_NEW_DATE,
  285. t.set_id,
  286. t.set_name
  287. ) T1
  288. LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, O_CODE FROM CREDIT9144 GROUP BY O_CODE) T2
  289. ON T1.O_CODE = T2.O_CODE
  290. LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE,
  291. DIM_OBJ_ID
  292. FROM CREDIT9144
  293. GROUP BY DIM_OBJ_ID) T3
  294. ON T1.DIM_OBJ_ID = T3.DIM_OBJ_ID
  295. LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, SET_ID FROM CREDIT9144 GROUP BY SET_ID) T4
  296. ON T1.O_CODE = T4.SET_ID) T1
  297. LEFT JOIN (SELECT DIM_OBJ_ID,
  298. WM_CONCAT(DISTINCT (TC.O_NAME)) || '(组合)' AS PORT_NAME,
  299. WM_CONCAT(DISTINCT (TD1.SD_NAME || '-' || TD2.SD_NAME || '-' || TD3.SD_NAME)) PORT_DEPT,
  300. WM_CONCAT(DISTINCT (T3.DIM_CLASS_NAME)) PORT_CLASS
  301. FROM DIM_9999 T1
  302. LEFT JOIN TSYS_DEPT TD1
  303. ON T1.D_CORP = TD1.SD_ID
  304. LEFT JOIN TSYS_DEPT TD2
  305. ON T1.D_DEPT = TD2.SD_ID
  306. LEFT JOIN TSYS_DEPT TD3
  307. ON T1.D_BIZLINE = TD3.SD_ID
  308. LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME FROM T9X2CC) T3
  309. ON T1.P_CLASS = T3.DIM_CODE
  310. LEFT JOIN T2RRRR TC
  311. ON T1.CUST_CODE = TC.O_CODE
  312. GROUP BY DIM_OBJ_ID) T2
  313. ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
  314. LEFT JOIN (SELECT *
  315. FROM (SELECT T.*,
  316. ROW_NUMBER() OVER (
  317. PARTITION BY T.O_CODE
  318. ORDER BY T.BASEDATE DESC,
  319. T.BEG_DATE DESC,
  320. IMP_TIME DESC) AS SN
  321. FROM TCRT_RESULT T
  322. WHERE T.TMP_FLAG = '0' AND T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01') C
  323. WHERE C.SN = 1) T3
  324. ON T1.O_CODE = T3.O_CODE;

改写后执行计划:

  1. 1 #NSET2: [17484, 1, 4470]
  2. 2 #PIPE2: [17484, 1, 4470]
  3. 3 #PIPE2: [17483, 1, 4470]
  4. 4 #PIPE2: [17470, 1, 4470]
  5. 5 #PRJT2: [17461, 1, 4470]; exp_num(1), is_atom(FALSE)
  6. 6 #AAGR2: [17461, 1, 4470]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
  7. 7 #HASH RIGHT JOIN2: [17461, 172479, 4470]; key_num(1), ret_null(0), KEY(T3.O_CODE=T1.O_CODE)
  8. 8 #PRJT2: [24, 331, 528]; exp_num(1), is_atom(FALSE)
  9. 9 #SLCT2: [24, 331, 528]; C.SN = var15
  10. 10 #PRJT2: [24, 13278, 528]; exp_num(2), is_atom(FALSE)
  11. 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. 12 #SORT3: [24, 13278, 528]; key_num(4), is_distinct(FALSE), top_flag(0), is_adaptive(0)
  13. 13 #SLCT2: [24, 13278, 528]; (T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01')
  14. 14 #BLKUP2: [24, 13619, 528]; TCRT_RESULT_TMPFLAG(T)
  15. 15 #SSEK2: [24, 13619, 528]; scan_type(ASC), TCRT_RESULT_TMPFLAG(TCRT_RESULT as T), scan_range['0','0']
  16. 16 #PRJT2: [17340, 172479, 3942]; exp_num(1), is_atom(FALSE)
  17. 17 #HASH RIGHT JOIN2: [17340, 172479, 3942]; key_num(1), ret_null(0), KEY(T4.SET_ID=T1.O_CODE)
  18. 18 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
  19. 19 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.SET_ID)
  20. 20 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
  21. 21 #HASH RIGHT JOIN2: [17260, 172479, 3096]; key_num(1), ret_null(0), KEY(T3.DIM_OBJ_ID=T1.DIM_OBJ_ID)
  22. 22 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
  23. 23 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.DIM_OBJ_ID)
  24. 24 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
  25. 25 #HASH RIGHT JOIN2: [17198, 172479, 2250]; key_num(1), ret_null(0), KEY(T2.O_CODE=T1.O_CODE)
  26. 26 #PRJT2: [2, 1, 846]; exp_num(1), is_atom(FALSE)
  27. 27 #HAGR2: [2, 1, 846]; grp_num(1), sfun_num(0); slave_empty(0) keys(CREDIT9144.O_CODE)
  28. 28 #HEAP TABLE SCAN: [1, 4, 846]; table_no(0)
  29. 29 #PRJT2: [17154, 172479, 1404]; exp_num(2), is_atom(FALSE)
  30. 30 #PRJT2: [17154, 172479, 1404]; exp_num(2), is_atom(FALSE)
  31. 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. 32 #PRJT2: [17060, 172479, 1404]; exp_num(9), is_atom(FALSE)
  33. 33 #PRJT2: [17060, 172479, 1404]; exp_num(9), is_atom(FALSE)
  34. 34 #UNION ALL: [17060, 172479, 1404]
  35. 35 #PRJT2: [2953, 172471, 1404]; exp_num(9), is_atom(FALSE)
  36. 36 #HASH2 INNER JOIN: [2953, 172471, 1404]; KEY_NUM(1); KEY(A.DIM_OBJ_ID=C.DIM_OBJ_ID) KEY_NULL_EQU(0)
  37. 37 #PRJT2: [152, 3480, 822]; exp_num(2), is_atom(FALSE)
  38. 38 #PRJT2: [152, 3480, 822]; exp_num(2), is_atom(FALSE)
  39. 39 #SLCT2: [152, 3480, 822]; TAWC11111.BASE_DATE <= var24
  40. 40 #BLKUP2: [152, 68827, 822]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
  41. 41 #SSEK2: [152, 68827, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01']
  42. 42 #PRJT2: [2774, 172472, 582]; exp_num(6), is_atom(FALSE)
  43. 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. 44 #PRJT2: [2016, 4084117, 582]; exp_num(8), is_atom(FALSE)
  45. 45 #UNION ALL: [2016, 4084117, 582]
  46. 46 #PRJT2: [846, 4084116, 582]; exp_num(8), is_atom(FALSE)
  47. 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. 48 #CSCN2: [23, 172472, 144]; INDEX33559059(TAXC45200 as T2)
  49. 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. 50 #NEST LOOP INDEX JOIN2: [116, 1718, 582]
  51. 51 #SLCT2: [105, 1718, 390]; B.DIM_CODE = 'DIM_CORP'
  52. 52 #NEST LOOP INDEX JOIN2: [105, 1718, 390]
  53. 53 #PRJT2: [91, 1718, 246]; exp_num(2), is_atom(FALSE)
  54. 54 #SLCT2: [91, 1718, 246]; TAWC11111.AWC_QUOTA > var25
  55. 55 #BLKUP2: [91, 68827, 246]; TAWC_STATIC_RESULT_BASEDATE(TAWC11111)
  56. 56 #SSEK2: [91, 68827, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01','2024-04-01']
  57. 57 #BLKUP2: [11, 1, 48]; INDEX33559829(B)
  58. 58 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as B), scan_range[A.DIM_OBJ_ID,A.DIM_OBJ_ID]
  59. 59 #BLKUP2: [11, 1, 48]; INDEX33558827(C)
  60. 60 #SSEK2: [11, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[B.O_CODE,B.O_CODE]
  61. 61 #CSCN2: [75, 562125, 144]; INDEX33559061(VRG99999 as T1)
  62. 62 #PRJT2: [59, 1, 534]; exp_num(8), is_atom(FALSE)
  63. 63 #SLCT2: [59, 1, 534]; (A.BASE_DATE = T2.BASE_DATE AND A.DIM_OBJ_ID = B.DIM_OBJ_ID)
  64. 64 #NEST LOOP INNER JOIN2: [59, 1, 534]; [with var]
  65. 65 #HASH2 INNER JOIN: [40, 119, 288]; KEY_NUM(1); KEY(T2.SET_ID=B.VRG_SET_ID) KEY_NULL_EQU(0)
  66. 66 #SLCT2: [24, 1053, 144]; T2.BASE_DATE = '2024-04-01'
  67. 67 #CSCN2: [24, 172472, 144]; INDEX33559059(TAXC45200 as T2)
  68. 68 #BLKUP2: [13, 11990, 144]; IDX_YHY_24011901_01(B)
  69. 69 #SSEK2: [13, 11990, 144]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as B), scan_range['DIM_VRG','DIM_VRG']
  70. 70 #PRJT2: [1, 1, 246]; exp_num(2), is_atom(FALSE)
  71. 71 #SLCT2: [1, 1, 246]; (TAWC11111.AWC_QUOTA > var26 AND TAWC11111.BASE_DATE = var11)
  72. 72 #BLKUP2: [1, 2, 246]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
  73. 73 #SSEK2: [1, 2, 246]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var12),('2024-04-01',var12)]
  74. 74 #PRJT2: [14024, 8, 2622]; exp_num(9), is_atom(FALSE)
  75. 75 #NEST LOOP LEFT JOIN2: [14024, 8, 2622]; join condition( TRUE ) partition_keys_num(0) ret_null(0)
  76. 76 #SLCT2: [74, 8, 1800]; A.DIM_OBJ_ID = B.DIM_OBJ_ID
  77. 77 #NEST LOOP INNER JOIN2: [74, 8, 1800]; [with var]
  78. 78 #PRJT2: [71, 21, 978]; exp_num(4), is_atom(FALSE)
  79. 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. 80 #SORT3: [71, 21, 978]; key_num(2), is_distinct(FALSE), top_flag(0), is_adaptive(0)
  81. 81 #INDEX JOIN LEFT JOIN2: [70, 96, 978] join condition(T1.BASE_DATE = '2024-04-01') ret_null(0)
  82. 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. 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. 84 #NEST LOOP INDEX JOIN2: [62, 9, 738]
  85. 85 #PRJT2: [61, 9, 642]; exp_num(3), is_atom(FALSE)
  86. 86 #DISTINCT: [61, 9, 642]
  87. 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. 88 #SLCT2: [59, 2, 354]; STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
  89. 89 #NEST LOOP INNER JOIN2: [59, 2, 354]; [with var]
  90. 90 #BLKUP2: [1, 52, 96]; IDX_YHY_24011901_01(A)
  91. 91 #SSEK2: [1, 52, 96]; scan_type(ASC), IDX_YHY_24011901_01(TAWC22222 as A), scan_range['DIM_PORT','DIM_PORT']
  92. 92 #PRJT2: [1, 2, 258]; exp_num(1), is_atom(FALSE)
  93. 93 #UNION FOR OR2: [1, 2, 258]; key_num(1), outer_join(-)
  94. 94 #SLCT2: [1, 1, 258]; TAWC11111.LIMIT_LINE > var28
  95. 95 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
  96. 96 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
  97. 97 #SLCT2: [1, 1, 258]; TAWC11111.AWC_QUOTA > var29
  98. 98 #BLKUP2: [1, 2, 258]; TAWC_STATIC_RESULT_BASEDATE1(TAWC11111)
  99. 99 #SSEK2: [1, 2, 258]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var14),('2024-04-01',var14)]
  100. 100 #CSCN2: [1, 374, 288]; INDEX33559058(DIM_9999 as B)
  101. 101 #BLKUP2: [1, 1, 48]; INDEX33558827(C)
  102. 102 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33558827(T2RRRR as C), scan_range[A.CUST_CODE,A.CUST_CODE]
  103. 103 #PRJT2: [1, 98, 96]; exp_num(1), is_atom(FALSE)
  104. 104 #SORT3: [1, 98, 96]; key_num(2), is_distinct(TRUE), top_flag(0), is_adaptive(0)
  105. 105 #CSCN2: [1, 98, 96]; INDEX33559770(T9X2CC)
  106. 106 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T1)
  107. 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. 108 #BLKUP2: [1, 8, 48]; INDEX33559689(T2)
  109. 109 #SSEK2: [1, 8, 48]; scan_type(ASC), INDEX33559689(TAXC45200 as T2), scan_range[(T1.SET_ID,min),(T1.SET_ID,max))
  110. 110 #PRJT2: [1, 1, 822]; exp_num(2), is_atom(FALSE)
  111. 111 #PRJT2: [1, 1, 822]; exp_num(2), is_atom(FALSE)
  112. 112 #SLCT2: [1, 1, 822]; TAWC11111.BASE_DATE <= var31
  113. 113 #SSEK2: [1, 2, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE1(TAWC11111), scan_range[('2024-04-01',var13),('2024-04-01',var13)]
  114. 114 #PRJT2: [152, 1, 822]; exp_num(1), is_atom(FALSE)
  115. 115 #AAGR2: [152, 1, 822]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
  116. 116 #PRJT2: [152, 69616, 822]; exp_num(1), is_atom(FALSE)
  117. 117 #SLCT2: [152, 69616, 822]; TAWC11111.BASE_DATE <= var33
  118. 118 #SSEK2: [152, 68827, 822]; scan_type(ASC), TAWC_STATIC_RESULT_BASEDATE(TAWC11111), scan_range['2024-04-01',exp11]
  119. 119 #HEAP TABLE: [8, 4, 846]; table_no(0) full(0), mpp_full(0) autoid(0), sites(-)
  120. 120 #PRJT2: [8, 4, 846]; exp_num(3), is_atom(FALSE)
  121. 121 #UNION FOR OR2: [8, 4, 846]; key_num(1), outer_join(-)
  122. 122 #UNION FOR OR2: [5, 3, 846]; key_num(1), outer_join(-)
  123. 123 #UNION FOR OR2: [2, 2, 846]; key_num(1), outer_join(-)
  124. 124 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896943136.colname=DMTEMPVIEW_896943065.TMPCOL5) KEY_NULL_EQU(0)
  125. 125 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
  126. 126 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 IS NULL
  127. 127 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  128. 128 #HASH RIGHT SEMI JOIN2: [1, 1, 846]; n_keys(1) KEY(DMTEMPVIEW_896943137.colname=DMTEMPVIEW_896943065.TMPCOL5) KEY_NULL_EQU(0)
  129. 129 #CONST VALUE LIST: [1, 2, 48]; row_num(2), col_num(1),
  130. 130 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 > '2024-04-01'
  131. 131 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  132. 132 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896943065.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
  133. 133 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 IS NULL
  134. 134 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  135. 135 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1'
  136. 136 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
  137. 137 #HASH LEFT SEMI JOIN2: [1, 1, 846]; KEY_NUM(1); KEY(DMTEMPVIEW_896943065.TMPCOL1=CFLSSX.INST_ID) KEY_NULL_EQU(0)
  138. 138 #SLCT2: [1, 1, 846]; DMTEMPVIEW_896943065.TMPCOL7 > '2024-04-01'
  139. 139 #HEAP TABLE SCAN: [1, 1, 846]; table_no(1)
  140. 140 #SLCT2: [1, 52, 96]; CFLSSX.AWC_CREDIT_FLAG = '1'
  141. 141 #CSCN2: [1, 208, 96]; INDEX33559053(CFLSSX)
  142. 142 #HEAP TABLE: [13, 1, 846]; table_no(1) full(0), mpp_full(0) autoid(1), sites(-)
  143. 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. 144 #INDEX JOIN LEFT JOIN2: [8, 1, 702] ret_null(0)
  145. 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. 146 #NEST LOOP INDEX JOIN2: [7, 1, 558]
  147. 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. 148 #CSCN2: [7, 33528, 462]; INDEX33559064(TAWXV999 as T1)
  149. 149 #BLKUP2: [1, 1, 48]; INDEX33559829(T2)
  150. 150 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T2), scan_range[T1.DIM_OBJ_ID,T1.DIM_OBJ_ID]
  151. 151 #SLCT2: [1, 22, 144]; (T3.AWC_TYPE >= 'AWC' AND T3.AWC_TYPE < 'AWD' AND T3.AWC_TYPE LIKE 'AWC_%')
  152. 152 #CSCN2: [1, 505, 144]; INDEX33559057(TAWC_RESULT_DETAIL as T3)
  153. 153 #SSEK2: [1, 1, 48]; scan_type(ASC), INDEX33559829(TAWC22222 as T4), scan_range[T3.DIM_OBJ_ID,T3.DIM_OBJ_ID]
  154. 154 #BLKUP2: [4, 3429, 144]; TAWC_VRG_SET_ITEM_BASEDATE(T5)
  155. 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. 156 #SPL2: [1, 1, 96]; key_num(1), spool_num(0), is_atom(TRUE), has_var(1), sites(-)
  157. 157 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(TRUE)
  158. 158 #AAGR2: [1, 1, 96]; grp_num(0), sfun_num(1), distinct_flag[0]; slave_empty(0)
  159. 159 #PRJT2: [1, 1, 96]; exp_num(1), is_atom(FALSE)
  160. 160 #SLCT2: [1, 1, 96]; exp_sfun1 <= var36
  161. 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. 162 #SORT3: [1, 7, 96]; key_num(1), is_distinct(FALSE), top_flag(0), is_adaptive(0)
  163. 163 #SLCT2: [1, 7, 96]; TN.DIM_OBJ_ID = var10
  164. 164 #CSCN2: [1, 374, 96]; INDEX33559058(DIM_9999 as TN)

 

优化前后比较:

        可以看到随便搞了一下,原来 11.6秒出结果的,现在 1.6 秒就能跑出结果了。????

         其实还能帮任总老婆优化得更快点,但是任总太小气了,饭都不请我吃一顿,差不多这样行了。??????

 

原文链接:https://www.cnblogs.com/yuzhijian/p/18111707

 友情链接:直通硅谷  点职佳  北美留学生论坛

本站QQ群:前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

W3xue 的所有内容仅供测试,对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接(目前9元/月)请联系QQ:27243702 沸活量
皖ICP备17017327号-2 皖公网安备34020702000426号