经验首页 前端设计 程序设计 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)
  163. --ET
  164. 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     
  165. ---------- ---------- -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- ----------------- --------------- --------------------
  166. 1          HSCN       1                    0%      145                  126         2           0                    0                    0                    0                    NULL              NULL            0
  167. 2          PRJT2      1                    0%      145                  22          4           0                    0                    0                    0                    NULL              NULL            0
  168. 3          SPL2       1                    0%      145                  151         1           0                    0                    0                    0                    NULL              NULL            0
  169. 4          PRJT2      1                    0%      145                  16          6           0                    0                    0                    0                    NULL              NULL            0
  170. 5          PRJT2      1                    0%      145                  29          6           0                    0                    0                    0                    NULL              NULL            0
  171. 6          PRJT2      1                    0%      145                  40          6           0                    0                    0                    0                    NULL              NULL            0
  172. 7          HSCN       1                    0%      145                  122         2           0                    0                    0                    0                    NULL              NULL            0
  173. 8          PRJT2      1                    0%      145                  18          4           0                    0                    0                    0                    NULL              NULL            0
  174. 9          CONSTV     2                    0%      139                  120         2           0                    0                    0                    0                    NULL              NULL            0
  175. 10         PIPE2      2                    0%      139                  4           5           0                    0                    0                    0                    NULL              NULL            0
  176. 11         PRJT2      2                    0%      139                  5           4           0                    0                    0                    0                    NULL              NULL            0
  177. 12         PRJT2      2                    0%      139                  30          6           0                    0                    0                    0                    NULL              NULL            0
  178. 13         PIPE2      2                    0%      139                  2           5           0                    0                    0                    0                    NULL              NULL            0
  179. 14         PRJT2      2                    0%      139                  83          4           0                    0                    0                    0                    NULL              NULL            0
  180. 15         PIPE2      3                    0%      132                  3           5           0                    0                    0                    0                    NULL              NULL            0
  181. 16         HSCN       3                    0%      132                  24          2           0                    0                    0                    0                    NULL              NULL            0
  182. 17         HSCN       3                    0%      132                  20          3           0                    0                    0                    0                    NULL              NULL            0
  183. 18         PRJT2      3                    0%      132                  101         4           0                    0                    0                    0                    NULL              NULL            0
  184. 19         PRJT2      3                    0%      132                  26          4           0                    0                    0                    0                    NULL              NULL            0
  185. 20         HSCN       3                    0%      132                  28          2           0                    0                    0                    0                    NULL              NULL            0
  186. 21         HSCN       3                    0%      132                  129         2           0                    0                    0                    0                    NULL              NULL            0
  187. 22         UNION_OR2  4                    0%      128                  118         5           0                    0                    0                    0                    NULL              NULL            0
  188. 23         CONSTV     4                    0%      128                  124         2           0                    0                    0                    0                    NULL              NULL            0
  189. 24         PRJT2      4                    0%      128                  115         8           0                    0                    0                    0                    NULL              NULL            0
  190. 25         SLCT2      4                    0%      128                  121         4           0                    0                    0                    0                    NULL              NULL            0
  191. 26         AAGR2      5                    0%      126                  6           5           0                    0                    0                    0                    NULL              NULL            0
  192. 27         HSCN       5                    0%      126                  134         3           0                    0                    0                    0                    NULL              NULL            0
  193. 28         DLCK       6                    0%      124                  0           2           0                    0                    0                    0                    NULL              NULL            0
  194. 29         SLCT2      6                    0%      124                  128         4           0                    0                    0                    0                    NULL              NULL            0
  195. 30         PRJT2      7                    0%      122                  44          26          0                    0                    0                    0                    NULL              NULL            0
  196. 31         SLCT2      7                    0%      122                  125         4           0                    0                    0                    0                    NULL              NULL            0
  197. 32         PRJT2      10                   0%      121                  8           90          0                    0                    0                    0                    NULL              NULL            0
  198. 33         SLCT2      11                   0%      119                  135         2           0                    0                    0                    0                    NULL              NULL            0
  199. 34         SLCT2      11                   0%      119                  130         4           0                    0                    0                    0                    NULL              NULL            0
  200. 35         SLCT2      16                   0%      118                  133         4           0                    0                    0                    0                    NULL              NULL            0
  201. 36         UNION_OR2  17                   0%      115                  117         7           0                    0                    0                    0                    NULL              NULL            0
  202. 37         PRJT2      17                   0%      115                  10          90          0                    0                    0                    0                    NULL              NULL            0
  203. 38         SLCT2      17                   0%      115                  86          44          0                    0                    0                    0                    NULL              NULL            0
  204. 39         HRS2       19                   0%      113                  119         6           224                  0                    2                    0                    NULL              NULL            0
  205. 40         HTAB       19                   0%      113                  114         6           0                    0                    0                    0                    NULL              NULL            0
  206. 41         UNION_OR2  20                   0%      112                  116         9           0                    0                    0                    0                    NULL              NULL            0
  207. 42         PRJT2      21                   0%      111                  33          56          0                    0                    0                    0                    NULL              NULL            0
  208. 43         PRJT2      22                   0%      110                  108         88          0                    0                    0                    0                    NULL              NULL            0
  209. 44         PRJT2      23                   0%      109                  32          56          0                    0                    0                    0                    NULL              NULL            0
  210. 45         SSEK2      27                   0%      108                  89          2           0                    0                    0                    0                    NULL              NULL            0
  211. 46         CSCN2      28                   0%      106                  103         2           0                    0                    0                    0                    NULL              NULL            0
  212. 47         PRJT2      28                   0%      106                  90          146         0                    0                    0                    0                    NULL              NULL            0
  213. 48         PRJT2      29                   0%      105                  76          44          0                    0                    0                    0                    NULL              NULL            0
  214. 49         UNION_OR2  33                   0%      104                  91          198         0                    0                    0                    0                    NULL              NULL            0
  215. 50         UNION_ALL2 36                   0%      102                  34          57          0                    0                    0                    0                    NULL              NULL            0
  216. 51         UNION_ALL2 36                   0%      102                  43          269         0                    0                    0                    0                    NULL              NULL            0
  217. 52         HRS2       39                   0%      101                  123         5           224                  0                    2                    0                    NULL              NULL            0
  218. 53         NLI2       43                   0%      100                  87          97          0                    0                    0                    0                    NULL              NULL            0
  219. 54         IJLO2      50                   0%      99                   139         244         0                    0                    0                    0                    NULL              NULL            0
  220. 55         HTAB       52                   0%      98                   137         12          0                    0                    0                    0                    NULL              NULL            0
  221. 56         PRJT2      61                   0%      97                   42          268         0                    0                    0                    0                    NULL              NULL            0
  222. 57         SLCT2      71                   0%      95                   74          46          0                    0                    0                    0                    NULL              NULL            0
  223. 58         SLCT2      71                   0%      95                   92          125         0                    0                    0                    0                    NULL              NULL            0
  224. 59         SLCT2      73                   0%      94                   95          146         0                    0                    0                    0                    NULL              NULL            0
  225. 60         CSCN2      74                   0%      93                   136         1           0                    0                    0                    0                    NULL              NULL            0
  226. 61         PRJT2      75                   0%      92                   110         88          0                    0                    0                    0                    NULL              NULL            0
  227. 62         CSCN2      77                   0%      90                   131         2           0                    0                    0                    0                    NULL              NULL            0
  228. 63         IJI2       77                   0%      90                   141         260         0                    0                    0                    0                    NULL              NULL            0
  229. 64         SLCT2      88                   0%      89                   146         6           0                    0                    0                    0                    NULL              NULL            0
  230. 65         PRJT2      89                   0%      88                   51          400         0                    0                    0                    0                    NULL              NULL            0
  231. 66         PRJT2      96                   0%      87                   37          462         0                    0                    0                    0                    NULL              NULL            0
  232. 67         BLKUP2     104                  0%      86                   93          146         0                    0                    0                    0                    NULL              NULL            0
  233. 68         NLI2       116                  0%      85                   75          89          0                    0                    0                    0                    NULL              NULL            0
  234. 69         SLCT2      121                  0%      84                   9           90          0                    0                    0                    0                    NULL              NULL            0
  235. 70         CSCN2      122                  0%      83                   147         3           0                    0                    0                    0                    NULL              NULL            0
  236. 71         NLLO2      124                  0%      82                   73          90          0                    0                    0                    0                    NULL              NULL            0
  237. 72         SLCT2      134                  0%      81                   61          244         0                    0                    0                    0                    NULL              NULL            0
  238. 73         CSCN2      136                  0%      79                   98          3           0                    0                    0                    0                    NULL              NULL            0
  239. 74         BLKUP2     136                  0%      79                   96          146         0                    0                    0                    0                    NULL              NULL            0
  240. 75         DIST       138                  0%      78                   84          5           17                   0                    242                  34                   NULL              NULL            0
  241. 76         IJI2       144                  0%      77                   82          831         0                    0                    0                    0                    NULL              NULL            0
  242. 77         PRJT2      151                  0%      76                   60          244         0                    0                    0                    0                    NULL              NULL            0
  243. 78         BLKUP2     154                  0%      75                   88          4           0                    0                    0                    0                    NULL              NULL            0
  244. 79         SORT3      226                  0%      74                   102         4           49472                0                    0                    0                    NULL              NULL            0
  245. 80         HAGR2      230                  0%      73                   27          4           1654                 0                    64                   0                    NULL              NULL            0
  246. 81         NSET2      241                  0%      72                   1           3           0                    0                    0                    0                    NULL              NULL            0
  247. 82         HAGR2      254                  0%      71                   19          4           1653                 0                    46                   0                    NULL              NULL            0
  248. 83         HAGR2      259                  0%      70                   23          4           1654                 0                    69                   0                    NULL              NULL            0
  249. 84         SLCT2      261                  0%      69                   142         118         0                    0                    0                    0                    NULL              NULL            0
  250. 85         PRJT2      368                  0%      68                   68          2310        0                    0                    0                    0                    NULL              NULL            0
  251. 86         SSEK2      377                  0%      67                   94          73          0                    0                    0                    0                    NULL              NULL            0
  252. 87         PRJT2      391                  0%      66                   72          46          0                    0                    0                    0                    NULL              NULL            0
  253. 88         IJI2       409                  0%      65                   48          2410        0                    0                    0                    0                    NULL              NULL            0
  254. 89         SSEK2      473                  0%      64                   150         13          0                    0                    0                    0                    NULL              NULL            0
  255. 90         SLCT2      476                  0%      63                   13          96          0                    0                    0                    0                    NULL              NULL            0
  256. 91         BLKUP2     486                  0%      62                   144         340         0                    0                    0                    0                    NULL              NULL            0
  257. 92         SSEK2      491                  0%      61                   105         13          0                    0                    0                    0                    NULL              NULL            0
  258. 93         SSEK2      560                  0.01%   60                   97          73          0                    0                    0                    0                    NULL              NULL            0
  259. 94         PRJT2      603                  0.01%   59                   35          12          0                    0                    0                    0                    NULL              NULL            0
  260. 95         SSEK2      646                  0.01%   58                   148         155         0                    0                    0                    0                    NULL              NULL            0
  261. 96         NLI2       699                  0.01%   57                   62          1291        0                    0                    0                    0                    NULL              NULL            0
  262. 97         SSEK2      774                  0.01%   56                   145         170         0                    0                    0                    0                    NULL              NULL            0
  263. 98         HAGR2      869                  0.01%   55                   41          137         1668                 0                    424                  0                    NULL              NULL            0
  264. 99         IJI2       884                  0.01%   54                   50          5424        0                    0                    0                    0                    NULL              NULL            0
  265. 100        SSEK2      906                  0.01%   53                   109         44          0                    0                    0                    0                    NULL              NULL            0
  266. 101        HRO2       916                  0.01%   52                   25          8           11950                0                    64                   0                    NULL              NULL            0
  267. 102        BLKUP2     929                  0.01%   51                   99          1104        0                    0                    0                    0                    NULL              NULL            0
  268. 103        HRO2       944                  0.01%   50                   17          8           11950                0                    46                   0                    NULL              NULL            0
  269. 104        HI3        957                  0.01%   49                   85          28          16046                0                    21                   0                    NULL              NULL            0
  270. 105        HLS2       971                  0.01%   48                   132         5           16558                0                    8                    0                    NULL              NULL            0
  271. 106        HRO2       982                  0.01%   47                   21          8           11950                0                    69                   0                    NULL              NULL            0
  272. 107        SLCT2      1025                 0.01%   46                   52          431         0                    0                    0                    0                    NULL              NULL            0
  273. 108        HLS2       1088                 0.01%   45                   127         6           16558                0                    73                   0                    NULL              NULL            0
  274. 109        AFUN       1126                 0.01%   44                   11          90          0                    0                    0                    0                    NULL              NULL            0
  275. 110        SLCT2      1326                 0.01%   43                   49          2345        0                    0                    0                    0                    NULL              NULL            0
  276. 111        SSEK2      1540                 0.01%   42                   100         552         0                    0                    0                    0                    NULL              NULL            0
  277. 112        SSEK2      1649                 0.01%   41                   67          41          0                    0                    0                    0                    NULL              NULL            0
  278. 113        HAGR2      1751                 0.02%   40                   31          31          1669                 0                    444                  1                    NULL              NULL            0
  279. 114        SLCT2      1759                 0.02%   39                   69          3223        0                    0                    0                    0                    NULL              NULL            0
  280. 115        SSEK2      1867                 0.02%   38                   15          48          0                    0                    0                    0                    NULL              NULL            0
  281. 116        SLCT2      1901                 0.02%   37                   64          585         0                    0                    0                    0                    NULL              NULL            0
  282. 117        HRO2       2055                 0.02%   36                   7           51          11950                0                    13017                158                  NULL              NULL            0
  283. 118        HI3        2124                 0.02%   35                   63          60          16046                0                    1047                 0                    NULL              NULL            0
  284. 119        SAGR2      2598                 0.02%   34                   77          92          0                    0                    0                    0                    NULL              NULL            0
  285. 120        SSEK2      2658                 0.02%   33                   107         524         0                    0                    0                    0                    NULL              NULL            0
  286. 121        HLO2       3097                 0.03%   32                   81          281         16558                0                    13                   0                    NULL              NULL            275
  287. 122        HLO2       3193                 0.03%   31                   140         92          16558                0                    84                   0                    NULL              NULL            0
  288. 123        HLO2       3272                 0.03%   30                   138         108         12462                0                    65                   0                    NULL              NULL            29
  289. 124        BLKUP2     3375                 0.03%   29                   57          2408        0                    0                    0                    0                    NULL              NULL            0
  290. 125        HLO2       3396                 0.03%   28                   80          21          12462                0                    20                   0                    NULL              NULL            275
  291. 126        SSEK2      4182                 0.04%   27                   58          1204        0                    0                    0                    0                    NULL              NULL            0
  292. 127        IJLO2      4622                 0.04%   26                   79          807         0                    0                    0                    0                    NULL              NULL            0
  293. 128        BLKUP2     7286                 0.07%   25                   70          4136        0                    0                    0                    0                    NULL              NULL            0
  294. 129        SORT3      7495                 0.07%   24                   12          93          59392                0                    0                    0                    NULL              NULL            0
  295. 130        BLKUP2     7522                 0.07%   23                   149         26          0                    0                    0                    0                    NULL              NULL            0
  296. 131        BLKUP2     7943                 0.07%   22                   104         26          0                    0                    0                    0                    NULL              NULL            0
  297. 132        BLKUP2     8173                 0.07%   21                   55          6964        0                    0                    0                    0                    NULL              NULL            0
  298. 133        SSEK2      9143                 0.08%   20                   54          231         0                    0                    0                    0                    NULL              NULL            0
  299. 134        SSEK2      9206                 0.08%   19                   39          231         0                    0                    0                    0                    NULL              NULL            0
  300. 135        SSEK2      10375                0.09%   18                   71          2068        0                    0                    0                    0                    NULL              NULL            0
  301. 136        SORT3      10605                0.1%    17                   78          347         116736               0                    0                    0                    NULL              NULL            0
  302. 137        CSCN2      11154                0.1%    16                   143         113         0                    0                    0                    0                    NULL              NULL            0
  303. 138        HI3        12422                0.11%   15                   36          240         40622                0                    27630                698                  NULL              NULL            62610
  304. 139        SSEK2      14431                0.13%   14                   56          3482        0                    0                    0                    0                    NULL              NULL            0
  305. 140        BLKUP2     26941                0.24%   13                   66          82          0                    0                    0                    0                    NULL              NULL            0
  306. 141        BLKUP2     33447                0.3%    12                   14          96          0                    0                    0                    0                    NULL              NULL            0
  307. 142        CSCN2      36753                0.33%   11                   46          580         0                    0                    0                    0                    NULL              NULL            0
  308. 143        CSCN2      37268                0.34%   10                   65          580         0                    0                    0                    0                    NULL              NULL            0
  309. 144        HRO2       37567                0.34%   9                    45          606         73971                0                    161179               12340                NULL              NULL            0
  310. 145        HLO2       41093                0.37%   8                    47          2503        12718                0                    303                  0                    NULL              NULL            597
  311. 146        BLKUP2     44604                0.4%    7                    106         1048        0                    0                    0                    0                    NULL              NULL            0
  312. 147        CSCN2      124217               1.12%   6                    59          1887        0                    0                    0                    0                    NULL              NULL            0
  313. 148        BLKUP2     156910               1.41%   5                    38          462         0                    0                    0                    0                    NULL              NULL            0
  314. 149        BLKUP2     166844               1.5%    4                    53          462         0                    0                    0                    0                    NULL              NULL            0
  315. 150        AAGR2      1555430              14%     3                    111         473352      0                    0                    0                    0                    NULL              NULL            0
  316. 151        SLCT2      1986956              17.89%  2                    112         946616      0                    0                    0                    0                    NULL              NULL            0
  317. 152        SSCN       6673379              60.09%  1                    113         473308      0                    0                    0                    0                    NULL              NULL            0
  318.  
  319. 152 rows got

　　 只返回一条数据 Success, cost 11 second(s)663 millsecond(s).  执行时间 11秒左右。

　　平时简单的SQL基本不用看执行计划大概都能知道哪里慢的，但是像这种看着是挺复杂的，也不知道慢在哪，DM执行计划就算难看也要分析了。??

　　从 DM 的 ET 工具可以看到本条SQL最慢的在 152行，SSCN  占了 整条SQL 60%的时间，对应执行计划中  #SSCN:  [793,  6385120,  48];  TAWC_STATIC_RESULT_BASEDATE(TAWC11111)

       SSCN 是什么意思各位读者同学感兴趣的可以自己去 DM 官网查查，反正我不知道，但是我知道哪里慢，下面这段很慢，要 11秒。

          SELECT T.O_CODE AS ID,
                       T.O_NAME AS CORP_NAME,
                       (SELECT MAX(PARENTID) PARENTID
                          FROM (SELECT CASE MAX(DIM_OBJ_ID)
                                         WHEN NULL THEN
                                          ''
                                         ELSE
                                          MAX(TN.CUST_CODE)
                                       END PARENTID
                                  FROM DIM_9999 TN
                                 WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
                                 GROUP BY TN.DIM_OBJ_ID
                                HAVING COUNT(DISTINCT(CUST_CODE)) <= 1) A) PARENTID,
                       T.O_CODE,
                       T.BASE_DATE,
                       T.DIM_OBJ_ID,
                       T.CUS_TYPE,
                       T.O_NAME AS CUS_NAME,
                       T.DIM_CODE,
                       T.IS_NEW_DATE,
                       TO_CHAR(SUM(CASE
                                     WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
                                      NVL(LIMIT_LINE, 0)
                                     ELSE
                                      0
                                   END) / 10000,
                               'fm999999999999999990.00') AS LIMIT_LINE,
                       TO_CHAR(SUM(CASE
                                     WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
                                      NVL(AWC_QUOTA, 0)
                                     ELSE
                                      0
                                   END) / 10000,
                               'fm999999999999999990.00') AS AWC_QUOTA,
                       TO_CHAR(SUM(CASE
                                     WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
                                      NVL(OCC_QUOTA, 0)
                                     ELSE
                                      0
                                   END) / 10000,
                               'fm999999999999999990.00') AS OCC_QUOTA,
                       TO_CHAR(SUM(CASE
                                     WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
                                      NVL(LIMIT_LINE_RATE, 0) * 100
                                     ELSE
                                      0
                                   END),
                               'fm999999999999999990.00') AS LIMIT_LINE_RATE,
                       TO_CHAR(GREATEST(SUM(CASE
                                              WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
                                               NVL(LIMIT_LINE_ABLE, 0)
                                              ELSE
                                               0
                                            END) / 10000,
                                        0),
                               'fm999999999999999990.00') AS LIMIT_LINE_ABLE,
                       TO_CHAR(SUM(CASE
                                     WHEN T.AWC_TYPE = 'CE_GENERAL' THEN
                                      NVL(AWC_QUOTA, 0)
                                     ELSE
                                      0
                                   END) / 10000,
                               'fm999999999999999990.00') AS CE_QUOTA,
                       TO_CHAR(SUM(CASE
                                     WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN
                                      NVL(AWC_QUOTA, 0)
                                     ELSE
                                      0
                                   END) / 10000,
                               'fm999999999999999990.00') AS ELUL_QUOTA,
                       SUM(CASE
                             WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN
                              NVL(AWC_QUOTA_ABLE, 0)
                             ELSE
                              0
                           END) AS AWC_QUOTA_ABLE,
                       t.set_id,
                       t.set_name
                  FROM (SELECT A.AWC_TYPE,
                               C.O_CODE,
                               C.O_NAME,
                               A.AWC_QUOTA,
                               A.OCC_QUOTA,
                               A.LIMIT_LINE_ABLE,
                               A.LIMIT_LINE_RATE,
                               A.LIMIT_LINE,
                               A.BASE_DATE,
                               A.DIM_OBJ_ID,
                               C.CUS_TYPE,
                               'DIM_CORP' AS DIM_CODE,
                               NULL AS AWC_QUOTA_ABLE,
                               '' AS IS_NEW_DATE,
                               c.set_id,
                               c.set_name
                          FROM (SELECT A.AWC_TYPE,
                                       A.AWC_QUOTA,
                                       A.OCC_QUOTA,
                                       A.LIMIT_LINE_ABLE,
                                       A.LIMIT_LINE_RATE,
                                       A.LIMIT_LINE,
                                       A.BASE_DATE,
                                       A.DIM_OBJ_ID
                                  FROM TAWC11111 A
                                 WHERE A.BASE_DATE = '2024-04-01') A
                         INNER JOIN CORP11111 C
                            ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
                         WHERE A.BASE_DATE = '2024-04-01'
                        UNION ALL
                        SELECT A.AWC_TYPE,
                               B.CUST_CODE AS O_CODE,
                               B.O_NAME || '(组合)' AS NAME,
                               A.AWC_QUOTA,
                               A.OCC_QUOTA,
                               A.LIMIT_LINE_ABLE,
                               A.LIMIT_LINE_RATE,
                               A.LIMIT_LINE,
                               A.BASE_DATE,
                               A.DIM_OBJ_ID,
                               B.CUS_TYPE,
                               'DIM_PORT' AS DIM_CODE,
                               A.AWC_QUOTA_ABLE,
                               CASE
                                 WHEN C.MAX_BASE_DATE = '2024-04-01' THEN
                                  '1'
                                 ELSE
                                  '0'
                               END AS IS_NEW_DATE,
                               '' AS set_id,
                               '' AS set_name
                          FROM (SELECT A.AWC_TYPE,
                                       A.AWC_QUOTA,
                                       A.OCC_QUOTA,
                                       A.LIMIT_LINE_ABLE,
                                       A.LIMIT_LINE_RATE,
                                       A.LIMIT_LINE,
                                       A.BASE_DATE,
                                       A.DIM_OBJ_ID,
                                       A.AWC_QUOTA_ABLE
                                  FROM TAWC11111 A
                                 WHERE A.BASE_DATE = '2024-04-01') A
                         INNER JOIN PORT_11112 B
                            ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
                          LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE
                                      FROM TAWC11111
                                     WHERE BASE_DATE <=
                                           TO_CHAR(SYSDATE, 'YYYY-MM-DD')) C
                            ON 1 = 1
                         WHERE A.BASE_DATE = '2024-04-01'
                           AND B.DIM_CODE = 'DIM_PORT') T
                 GROUP BY T.O_CODE,
                          T.O_NAME,
                          T.CUS_TYPE,
                          T.BASE_DATE,
                          T.DIM_OBJ_ID,
                          T.DIM_CODE,
                          T.IS_NEW_DATE,
                          t.set_id,
                          t.set_name

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

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

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

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

 

SQL改写优化：

WITH CORP11111 AS
         (SELECT T.O_CODE,
                 T.O_NAME,
                 T.CUS_TYPE,
                 T.O_TYPE,
                 T.SET_NAME,
                 T.SET_ID,
                 T.DIM_CODE,
                 T.DIM_OBJ_ID
          FROM (SELECT C.O_CODE,
                       C.CUS_TYPE,
                       C.O_TYPE,
                       C.O_NAME,
                       T2.SET_NAME,
                       T2.SET_ID,
                       B.DIM_CODE,
                       B.DIM_OBJ_ID,
                       '' AS D_DEPT,
                       '' AS D_NAME,
                       '' AS D_BIZLINE,
                       '' AS P_CLASS,
                       '' AS P_CLASS_NAME
                FROM (SELECT DIM_OBJ_ID,
                             BASE_DATE,
                             LIMIT_LINE,
                             AWC_QUOTA,
                             OCC_QUOTA,
                             LIMIT_LINE_ABLE,
                             LIMIT_LINE_RATE
                      FROM TAWC11111
                      WHERE BASE_DATE = '2024-04-01') A
                         INNER JOIN TAWC22222 B
                                    ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
                         INNER JOIN T2RRRR C
                                    ON B.O_CODE = C.O_CODE
                         LEFT JOIN VRG99999 T1
                                   ON C.O_CODE = T1.O_CODE
                                       AND T1.BASE_DATE = A.BASE_DATE
                         LEFT JOIN TAXC45200 T2
                                   ON T1.SET_ID = T2.SET_ID
                                       AND T2.BASE_DATE = A.BASE_DATE
                WHERE A.BASE_DATE = '2024-04-01'
                  AND B.DIM_CODE = 'DIM_CORP'
                  AND A.AWC_QUOTA > 0
                UNION ALL
                SELECT T2.SET_ID AS O_CODE,
                       '集团'    AS CUS_TYPE,
                       'group'   AS O_TYPE,
                       ''        AS O_NAME,
                       T2.SET_NAME,
                       T2.SET_ID,
                       B.DIM_CODE,
                       B.DIM_OBJ_ID,
                       ''        AS D_DEPT,
                       ''        AS D_NAME,
                       ''        AS D_BIZLINE,
                       ''        AS P_CLASS,
                       ''        AS P_CLASS_NAME
                FROM (SELECT DIM_OBJ_ID,
                             BASE_DATE,
                             LIMIT_LINE,
                             AWC_QUOTA,
                             OCC_QUOTA,
                             LIMIT_LINE_ABLE,
                             LIMIT_LINE_RATE
                      FROM TAWC11111
                      WHERE BASE_DATE = '2024-04-01') A
                         INNER JOIN TAWC22222 B
                                    ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
                         INNER JOIN TAXC45200 T2
                                    ON B.VRG_SET_ID = T2.SET_ID
                                        AND A.BASE_DATE = T2.BASE_DATE
                WHERE A.BASE_DATE = '2024-04-01'
                  AND B.DIM_CODE = 'DIM_VRG'
                  AND A.AWC_QUOTA > 0) T
          WHERE 1 = 1
          GROUP BY O_CODE,
                   O_NAME,
                   CUS_TYPE,
                   O_TYPE,
                   SET_NAME,
                   SET_ID,
                   DIM_CODE,
                   DIM_OBJ_ID)
        ,
     PORT_11112 AS
         (SELECT A.DIM_OBJ_ID AS                      CUST_CODE,
                 ''                                   CUS_TYPE,
                 '2'                                  O_TYPE,
                 WM_CONCAT(DISTINCT (C.O_NAME))       O_NAME,
                 WM_CONCAT(DISTINCT (A.D_DEPT))       D_DEPT,
                 WM_CONCAT(DISTINCT (A.D_NAME))       D_NAME,
                 WM_CONCAT(DISTINCT (A.D_BIZLINE))    D_BIZLINE,
                 WM_CONCAT(DISTINCT (A.P_CLASS))      P_CLASS,
                 WM_CONCAT(DISTINCT (DIM_CLASS_NAME)) P_CLASS_NAME,
                 'DIM_PORT'                           DIM_CODE,
                 A.DIM_OBJ_ID,
                 T2.SET_NAME
          FROM (SELECT DISTINCT A.DIM_OBJ_ID,
                                A.DIM_CODE,
                                B.CUST_CODE,
                                B.D_DEPT,
                                B.D_NAME,
                                B.D_BIZLINE,
                                B.P_CLASS
                FROM (SELECT AWC_QUOTA,
                             LIMIT_LINE,
                             BASE_DATE,
                             DIM_OBJ_ID,
                             OCC_QUOTA,
                             LIMIT_LINE_ABLE,
                             LIMIT_LINE_RATE
                      FROM TAWC11111
                      WHERE BASE_DATE = '2024-04-01') STATS
                         INNER JOIN TAWC22222 A
                                    ON STATS.DIM_OBJ_ID = A.DIM_OBJ_ID
                         INNER JOIN DIM_9999 B
                                    ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
                WHERE A.DIM_CODE = 'DIM_PORT'
                  AND STATS.BASE_DATE = '2024-04-01'
                  AND (STATS.LIMIT_LINE > 0
                    OR STATS.AWC_QUOTA > 0)) A
                   INNER JOIN T2RRRR C
                              ON A.CUST_CODE = C.O_CODE
                   LEFT JOIN (SELECT DISTINCT DIM_CODE,
                                              DIM_CLASS_NAME
                              FROM T9X2CC
                              ORDER BY DIM_CODE ASC) D
                             ON A.P_CLASS = D.DIM_CODE
                   LEFT JOIN VRG99999 T1
                             ON C.O_CODE = T1.O_CODE
                                 AND T1.BASE_DATE = '2024-04-01'
                   LEFT JOIN TAXC45200 T2
                             ON T1.SET_ID = T2.SET_ID
                                 AND T1.BASE_DATE = '2024-04-01'
          WHERE 1 = 1
          GROUP BY A.DIM_OBJ_ID,
                   T2.SET_NAME)
        ,
     CREDIT9144 AS
         (SELECT DECODE(T1.ADJ_TYPE,
                        '调减', -1,
                        1) * T1.ADJ_LINE AS ADJ_LINE,
                 T2.O_CODE,
                 T1.INST_ID,
                 T4.DIM_OBJ_ID,
                 T5.SET_ID
          FROM TAWXV999 T1
                   INNER JOIN TAWC22222 T2
                              ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
                   LEFT JOIN TAWC_RESULT_DETAIL T3
                             ON T1.INST_ID = T3.INST_ID
                                 AND T3.AWC_TYPE LIKE 'AWC_%'
                   LEFT JOIN TAWC22222 T4
                             ON T3.DIM_OBJ_ID = T4.DIM_OBJ_ID
                   LEFT JOIN VRG99999 T5
                             ON T2.O_CODE = T5.O_CODE
                                 AND T5.BASE_DATE = '2024-04-01'
          WHERE T1.DATA_SOURCE = 'ADJ'
            AND T1.TMP_FLAG = '0'
            AND T1.AWC_TYPE = 'AWC_CREDIT'
            AND (T1.REMARK = '初始化-占用信用债'
              OR T1.REMARK = '释放信用债'
              OR T1.INST_ID IN (SELECT INST_ID FROM CFLSSX WHERE AWC_CREDIT_FLAG = '1'))
            AND T1.ADJ_BEG_DATE <= '2024-04-01'
            AND (T1.ADJ_END_DATE IS NULL
              OR T1.ADJ_END_DATE > '2024-04-01'))
      , X AS (
      
        SELECT * FROM TAWC11111 WHERE BASE_DATE = '2024-04-01' and TO_CHAR(SYSDATE, 'YYYY-MM-DD')
      )

  SELECT COUNT(1) FROM (
     SELECT T1.*, 
                    TO_CHAR(DECODE(T1.DIM_CODE, 
                                             'DIM_PORT', T3.ADJ_LINE,
                                             DECODE(CUS_TYPE, 
                                                              '集团', T4.ADJ_LINE, 
                                                              T2.ADJ_LINE)) / 10000, 'fm999999999999999990.00') AS ADJ_LINE
               FROM (



SELECT T.O_CODE                                                                         AS ID,
             T.O_NAME                                                                         AS CORP_NAME,
             (SELECT MAX(PARENTID) PARENTID
              FROM (SELECT CASE MAX(DIM_OBJ_ID) WHEN NULL THEN '' ELSE MAX(TN.CUST_CODE) END PARENTID
                    FROM DIM_9999 TN
                    WHERE TN.DIM_OBJ_ID = T.DIM_OBJ_ID
                    GROUP BY TN.DIM_OBJ_ID
                    HAVING COUNT(DISTINCT (CUST_CODE)) <= 1) A)                                  PARENTID,
             T.O_CODE,
             T.BASE_DATE,
             T.DIM_OBJ_ID,
             T.CUS_TYPE,
             T.O_NAME                                                                         AS CUS_NAME,
             T.DIM_CODE,
             T.IS_NEW_DATE,
             TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE, 0) ELSE 0 END) / 10000,
                     'fm999999999999999990.00')                                               AS LIMIT_LINE,
             TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000,
                     'fm999999999999999990.00')                                               AS AWC_QUOTA,
             TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(OCC_QUOTA, 0) ELSE 0 END) / 10000,
                     'fm999999999999999990.00')                                               AS OCC_QUOTA,
             TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_RATE, 0) * 100 ELSE 0 END),
                     'fm999999999999999990.00')                                               AS LIMIT_LINE_RATE,
             TO_CHAR(GREATEST(SUM(CASE
                                      WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(LIMIT_LINE_ABLE, 0)
                                      ELSE 0 END) / 10000, 0), 'fm999999999999999990.00')     AS LIMIT_LINE_ABLE,
             TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'CE_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000,
                     'fm999999999999999990.00')                                               AS CE_QUOTA,
             TO_CHAR(SUM(CASE WHEN T.AWC_TYPE = 'ELUL_GENERAL' THEN NVL(AWC_QUOTA, 0) ELSE 0 END) / 10000,
                     'fm999999999999999990.00')                                               AS ELUL_QUOTA,
             SUM(CASE WHEN T.AWC_TYPE = 'AWC_GENERAL' THEN NVL(AWC_QUOTA_ABLE, 0) ELSE 0 END) AS AWC_QUOTA_ABLE,
             t.set_id,
             t.set_name
      FROM (
      SELECT A.AWC_TYPE,
                   C.O_CODE,
                   C.O_NAME,
                   A.AWC_QUOTA,
                   A.OCC_QUOTA,
                   A.LIMIT_LINE_ABLE,
                   A.LIMIT_LINE_RATE,
                   A.LIMIT_LINE,
                   A.BASE_DATE,
                   A.DIM_OBJ_ID,
                   C.CUS_TYPE,
                   'DIM_CORP' AS DIM_CODE,
                   NULL       AS AWC_QUOTA_ABLE,
                   ''         AS IS_NEW_DATE,
                   c.set_id,
                   c.set_name
            FROM (SELECT A.AWC_TYPE,
                         A.AWC_QUOTA,
                         A.OCC_QUOTA,
                         A.LIMIT_LINE_ABLE,
                         A.LIMIT_LINE_RATE,
                         A.LIMIT_LINE,
                         A.BASE_DATE,
                         A.DIM_OBJ_ID
                  FROM X A
                  WHERE A.BASE_DATE = '2024-04-01') A
                     INNER JOIN CORP11111 C
                                ON A.DIM_OBJ_ID = C.DIM_OBJ_ID
            WHERE A.BASE_DATE = '2024-04-01'
            UNION ALL
        
            SELECT A.AWC_TYPE,
                   B.CUST_CODE                                                    AS O_CODE,
                   B.O_NAME || '(组合)'                                           AS NAME,
                   A.AWC_QUOTA,
                   A.OCC_QUOTA,
                   A.LIMIT_LINE_ABLE,
                   A.LIMIT_LINE_RATE,
                   A.LIMIT_LINE,
                   A.BASE_DATE,
                   A.DIM_OBJ_ID,
                   B.CUS_TYPE,
                   'DIM_PORT'                                                     AS DIM_CODE,
                   A.AWC_QUOTA_ABLE,
                   CASE WHEN C.MAX_BASE_DATE = '2024-04-01' THEN '1' ELSE '0' END AS IS_NEW_DATE,
                   ''                                                             AS set_id,
                   ''                                                             AS set_name
            FROM (
            
            SELECT A.AWC_TYPE,
                         A.AWC_QUOTA,
                         A.OCC_QUOTA,
                         A.LIMIT_LINE_ABLE,
                         A.LIMIT_LINE_RATE,
                         A.LIMIT_LINE,
                         A.BASE_DATE,
                         A.DIM_OBJ_ID,
                         A.AWC_QUOTA_ABLE
                  FROM X A WHERE A.BASE_DATE = '2024-04-01'
                  
                  ) A
                     INNER JOIN PORT_11112 B ON A.DIM_OBJ_ID = B.DIM_OBJ_ID
                     LEFT JOIN (SELECT MAX(BASE_DATE) MAX_BASE_DATE FROM X WHERE BASE_DATE <= TO_CHAR(SYSDATE, 'YYYY-MM-DD')
                     ) C
                               ON 1 = 1
            WHERE A.BASE_DATE = '2024-04-01'
              AND B.DIM_CODE = 'DIM_PORT'
              
              
              ) T
      GROUP BY T.O_CODE,
               T.O_NAME,
               T.CUS_TYPE,
               T.BASE_DATE,
               T.DIM_OBJ_ID,
               T.DIM_CODE,
               T.IS_NEW_DATE,
               t.set_id,
               t.set_name
               )  T1
          LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, O_CODE FROM CREDIT9144 GROUP BY O_CODE) T2 
                 ON T1.O_CODE = T2.O_CODE
          LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, 
                             DIM_OBJ_ID 
                        FROM CREDIT9144 
                    GROUP BY DIM_OBJ_ID) T3 
                 ON T1.DIM_OBJ_ID = T3.DIM_OBJ_ID
          LEFT JOIN (SELECT SUM(ADJ_LINE) AS ADJ_LINE, SET_ID FROM CREDIT9144 GROUP BY SET_ID) T4 
                 ON T1.O_CODE = T4.SET_ID) T1
LEFT JOIN (SELECT DIM_OBJ_ID, 
                    WM_CONCAT(DISTINCT (TC.O_NAME)) || '(组合)' AS PORT_NAME,
                    WM_CONCAT(DISTINCT (TD1.SD_NAME || '-' || TD2.SD_NAME || '-' || TD3.SD_NAME)) PORT_DEPT,
                    WM_CONCAT(DISTINCT (T3.DIM_CLASS_NAME)) PORT_CLASS
               FROM DIM_9999 T1
          LEFT JOIN TSYS_DEPT TD1 
                 ON T1.D_CORP = TD1.SD_ID
          LEFT JOIN TSYS_DEPT TD2 
                 ON T1.D_DEPT = TD2.SD_ID
          LEFT JOIN TSYS_DEPT TD3 
                 ON T1.D_BIZLINE = TD3.SD_ID
          LEFT JOIN (SELECT DISTINCT DIM_CODE, DIM_CLASS_NAME FROM T9X2CC) T3 
                 ON T1.P_CLASS = T3.DIM_CODE
          LEFT JOIN T2RRRR TC 
                 ON T1.CUST_CODE = TC.O_CODE 
           GROUP BY DIM_OBJ_ID) T2 
       ON T1.DIM_OBJ_ID = T2.DIM_OBJ_ID
LEFT JOIN (SELECT * 
             FROM (SELECT T.*, 
                               ROW_NUMBER() OVER ( 
                                     PARTITION BY T.O_CODE 
                                         ORDER BY T.BASEDATE DESC, 
                                                  T.BEG_DATE DESC, 
                                                  IMP_TIME DESC) AS SN
                          FROM TCRT_RESULT T 
                         WHERE T.TMP_FLAG = '0' AND T.BEG_DATE <= '2024-04-01' AND T.END_DATE >= '2024-04-01') C 
            WHERE C.SN = 1) T3 
       ON T1.O_CODE = T3.O_CODE;

改写后执行计划：

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

 

优化前后比较：

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

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

 

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

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

SQL语言热门文章

  • 为什么不推荐使用 select * ？是性能问题吗 ？
  • sql查询返回值使用map封装多个key和value实例
  • 掌握把“烂”SQL牢牢关进笼子里的密钥
  • 029.[转] SSO单点登录的通用架构实现
  • 千万级用户系统SQL调优实战分享
  • 图解SQL语句里的各种JOIN查询 瞬间秒懂
  • pgsql查询优化之模糊查询
  • 关系型数据库范式 你了解吗？
  • 神奇的 SQL 之谓词 → 难理解的 EXISTS
  • insert和select结合实现"插入某字段在数据库中的最大值＋1"的方法

    • SQL语言推荐文章

  • EF Core性能优化技巧
  • 【SQL】Lag/Rank/Over窗口函数揭秘，数据分析之旅
  • SQL Thinking
  • 【SQL】晨光咖啡馆，过滤聚合的微妙碰撞
  • SHOW PROCESSLIST 最多能显示多长的 SQL？
  • monaco-editor 的 Language Services
  • OceanBase 金融项目优化案例
  • SQL KEEP 窗口函数等价改写案例
  • DM数据库金融行业案例（水贴一波）
  • MindSpore自定义算子中的张量维度问题

    • 本站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号