经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » 程序设计 » SQL语言 » 查看文章
OceanBase金融SQL、亿万级别据量优化案例（Row_number 开窗 + 分页SQL）
来源：cnblogs　　作者：小至尖尖　　时间：2023/11/8 9:07:32　　对本文有异议

最近优化了不少SQL，简单的SQL顺手搞了不好意思发出来了忽悠人，复杂很考验逻辑思维的，但是又不想分享出来（自己收藏的案例），怕被人抄袭思路??。

今天遇到一条很有意思的SQL案例：

 性能SQL（金融行业关键信息已经隐藏）：

  1. SELECT CS.BRANCHCODE,
  2.        (SELECT DISTINCT UM.UNITCODE
  3.         FROM IFUNITMAPPING UM
  4.         WHERE UM.FGS = CS.BRANCHCODE
  5.           AND UM.DEPTCODE = CS.DEPTCODE
  6.           AND UM.CURRENCYCODE = CS.PAYCURRENCY
  7.           AND UM.IFVALID = '1')             AS UNITCODE,
  8.        CS.SECTIONCODE,
  9.        CS.PRODUCTGRPCODE,
  10.        CS.HANDLERCODE,
  11.        (SELECT DISTINCT(NAME)
  12.         FROM IFEMPLOYEE EE
  13.         WHERE EE.CODE = CS.HANDLERCODE
  14.           AND EE.UNIT_CODE = CS.BRANCHCODE) AS HANDLERNAME,
  15.        CS.POLICYNO,
  16.        CS.ENDORSENO,
  17.        CS.SIGNPREMIUM,
  18.        CS.SIGNDATE,
  19.        CS.STARTDATE,
  20.        CS.COMMISSIONTYPE,
  21.        CS.PAYCURRENCY,
  22.        CS.AGENTCODE,
  23.        B.OPERATOR,
  24.        A.OLDCOMMISSION,
  25.        A.OLDCOMMISSIONRATE,
  26.        B.COMMISSION,
  27.        B.COMMISSIONRATE,
  28.        B.UPDATEDATE
  29. FROM CSSCS CS,
  30.      (SELECT AA.NUM,
  31.              AA.OLDCOMMISSIONRATE,
  32.              AA.OLDCOMMISSION,
  33.              AA.COMMITSSSSS
  34.       FROM (SELECT ROW_NUMBER() OVER (
  35.           PARTITION BY T2.COMMITSSSSS
  36.           ORDER BY
  37.               T1.UPDATEDATE ASC
  38.           )                               AS NUM,
  39.                    T2.OLDCOMMISSIONRATE   AS OLDCOMMISSIONRATE,
  40.                    T2.OLDCOMMISSION       AS OLDCOMMISSION,
  41.                    T2.COMMITSSSSS AS COMMITSSSSS,
  42.                    T1.OPERFLAG            AS OPERFLAG
  43.             FROM  T111 T1,
  44.                    T222 T2
  45.             WHERE 1 = 1
  46.               AND T2.SUBCOMPANY = '5010100'
  47.               AND T1.UNIQUECODE LIKE '5010100%'
  48.               AND T2.COMMISSIONTYPE = '0'
  49.               AND T1.RATECHANGENO = T2.RATECHANGENO
  50.               AND T1.STATUS = '1') AA
  51.       WHERE AA.NUM = 1) A,
  52.      (SELECT BB.NUM,
  53.              BB.OPERATOR,
  54.              BB.UPDATEDATE,
  55.              BB.COMMISSIONRATE,
  56.              BB.COMMISSION,
  57.              BB.COMMITSSSSS,
  58.              BB.OPERFLAG
  59.       FROM (SELECT ROW_NUMBER() OVER (
  60.           PARTITION BY T2.COMMITSSSSS
  61.           ORDER BY
  62.               T1.UPDATEDATE DESC
  63.           )                               AS NUM,
  64.                    T1.OPERATOR            AS OPERATOR,
  65.                    T1.UPDATEDATE          AS UPDATEDATE,
  66.                    T2.COMMISSIONRATE      AS COMMISSIONRATE,
  67.                    T2.COMMISSION          AS COMMISSION,
  68.                    T2.COMMITSSSSS AS COMMITSSSSS,
  69.                    T1.OPERFLAG            AS OPERFLAG
  70.             FROM  T111 T1,
  71.                    T222 T2
  72.             WHERE 1 = 1
  73.               AND T2.SUBCOMPANY = '5010100'
  74.               AND T1.UNIQUECODE LIKE '5010100%'
  75.               AND T2.UPDATEDATE >= TO_DATE('2021-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  76.               AND T2.UPDATEDATE < TO_DATE('2021-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  77.               AND (
  78.                     T1.OPERFLAG IS NULL
  79.                     OR T1.OPERFLAG = '0'
  80.                 )
  81.               AND T2.COMMISSIONTYPE = '0'
  82.               AND T1.RATECHANGENO = T2.RATECHANGENO
  83.               AND T1.STATUS = '1') BB
  84.       WHERE BB.NUM = 1) B
  85. WHERE A.COMMITSSSSS = B.COMMITSSSSS
  86.   AND A.COMMITSSSSS = CS.COMMITSSSSS
  87.   AND CS.BRANCHCODE = '5010100'
  88.   AND CS.PAYCURRENCY = '01'
  89.   AND ROWNUM <= 5000
  90. ORDER BY B.UPDATEDATE;
  1. 执行时间：155 s

执行计划：

  1. ================================================================================
  2. |ID|OPERATOR                    |NAME                        |EST. ROWS|COST   |
  3. --------------------------------------------------------------------------------
  4. |0 |SUBPLAN FILTER              |                            |1        |1097660|
  5. |1 | SORT                       |                            |1        |1097485|
  6. |2 |  SUBPLAN SCAN              |VIEW1                       |1        |1097485|
  7. |3 |   LIMIT                    |                            |1        |1097485|
  8. |4 |    NESTED-LOOP JOIN        |                            |1        |1097485|
  9. |5 |     NESTED-LOOP JOIN       |                            |1        |1097456|
  10. |6 |      SUBPLAN SCAN          |BB                          |1        |1097317|
  11. |7 |       WINDOW FUNCTION      |                            |14332    |1096799|
  12. |8 |        SORT                |                            |14332    |1089188|
  13. |9 |         HASH JOIN          |                            |14332    |1051984|
  14. |10|          TABLE SCAN        |T2(IND_T222_5)              |14332    |650849 |
  15. |11|          TABLE SCAN        |T1(IND_T111_2)              |32610    |376637 |
  16. |12|      SUBPLAN SCAN          |AA                          |1        |139    |
  17. |13|       WINDOW FUNCTION      |                            |1        |139    |
  18. |14|        SORT                |                            |1        |138    |
  19. |15|         NESTED-LOOP JOIN   |                            |1        |138    |
  20. |16|          TABLE SCAN        |T2(IND_  T222_3)            |1        |137    |
  21. |17|          TABLE GET         |T1                          |1        |28     |
  22. |18|     TABLE LOOKUP           |CS                          |1        |28     |
  23. |19|      DISTRIBUTED TABLE SCAN|CS(PK_CSSCS)                |1        |5      |
  24. |20| MERGE DISTINCT             |                            |1        |92     |
  25. |21|  SORT                      |                            |1        |92     |
  26. |22|   TABLE SCAN               |UM(IND_IFUNITMAPPING)       |1        |92     |
  27. |23| MERGE DISTINCT             |                            |1        |92     |
  28. |24|  SORT                      |                            |1        |92     |
  29. |25|   TABLE SCAN               |EE(IND_IFEMPLOYEE_5)        |1        |92     |
  30. ================================================================================
  32. Outputs & filters: 
  33. -------------------------------------
  34.   0 - output([VIEW1.CS.BRANCHCODE(0x7ea69ce2d3b0)], [subquery(1)(0x7f1a39329e20)], [VIEW1.CS.SECTIONCODE(0x7ea69ce2e550)], [VIEW1.CS.PRODUCTGRPCODE(0x7ea69ce2e840)], [VIEW1.CS.HANDLERCODE(0x7ea69ce2eb30)], [subquery(2)(0x7ea69cd5a570)], [VIEW1.CS.POLICYNO(0x7ea69ce2ee20)], [VIEW1.CS.ENDORSENO(0x7ea69ce2f110)], [VIEW1.CS.SIGNPREMIUM(0x7ea69ce2f400)], [VIEW1.CS.SIGNDATE(0x7ea69ce2f6f0)], [VIEW1.CS.STARTDATE(0x7ea69ce2f9e0)], [VIEW1.CS.COMMISSIONTYPE(0x7ea69ce2fcd0)], [VIEW1.CS.PAYCURRENCY(0x7ea69ce2df70)], [VIEW1.CS.AGENTCODE(0x7ea69ce2ffc0)], [VIEW1.B.OPERATOR(0x7ea69ce302b0)], [VIEW1.A.OLDCOMMISSION(0x7ea69ce305a0)], [VIEW1.A.OLDCOMMISSIONRATE(0x7ea69ce30890)], [VIEW1.B.COMMISSION(0x7ea69ce30b80)], [VIEW1.B.COMMISSIONRATE(0x7ea69ce30e70)], [VIEW1.B.UPDATEDATE(0x7ea69ce31160)]), filter(nil), 
  35.       exec_params_([VIEW1.CS.BRANCHCODE(0x7ea69ce2d3b0)], [VIEW1.CS.DEPTCODE(0x7ea69ce2e260)], [VIEW1.CS.PAYCURRENCY(0x7ea69ce2df70)], [VIEW1.CS.HANDLERCODE(0x7ea69ce2eb30)], [VIEW1.CS.BRANCHCODE(0x7ea69ce2d3b0)]), onetime_exprs_(nil), init_plan_idxs_(nil)
  36.   1 - output([VIEW1.CS.BRANCHCODE(0x7ea69ce2d3b0)], [VIEW1.CS.SECTIONCODE(0x7ea69ce2e550)], [VIEW1.CS.PRODUCTGRPCODE(0x7ea69ce2e840)], [VIEW1.CS.HANDLERCODE(0x7ea69ce2eb30)], [VIEW1.CS.POLICYNO(0x7ea69ce2ee20)], [VIEW1.CS.ENDORSENO(0x7ea69ce2f110)], [VIEW1.CS.SIGNPREMIUM(0x7ea69ce2f400)], [VIEW1.CS.SIGNDATE(0x7ea69ce2f6f0)], [VIEW1.CS.STARTDATE(0x7ea69ce2f9e0)], [VIEW1.CS.COMMISSIONTYPE(0x7ea69ce2fcd0)], [VIEW1.CS.PAYCURRENCY(0x7ea69ce2df70)], [VIEW1.CS.AGENTCODE(0x7ea69ce2ffc0)], [VIEW1.B.OPERATOR(0x7ea69ce302b0)], [VIEW1.A.OLDCOMMISSION(0x7ea69ce305a0)], [VIEW1.A.OLDCOMMISSIONRATE(0x7ea69ce30890)], [VIEW1.B.COMMISSION(0x7ea69ce30b80)], [VIEW1.B.COMMISSIONRATE(0x7ea69ce30e70)], [VIEW1.B.UPDATEDATE(0x7ea69ce31160)], [VIEW1.CS.DEPTCODE(0x7ea69ce2e260)]), filter(nil), sort_keys([VIEW1.B.UPDATEDATE(0x7ea69ce31160), ASC])
  37.   2 - output([VIEW1.CS.BRANCHCODE(0x7ea69ce2d3b0)], [VIEW1.CS.PAYCURRENCY(0x7ea69ce2df70)], [VIEW1.CS.DEPTCODE(0x7ea69ce2e260)], [VIEW1.CS.SECTIONCODE(0x7ea69ce2e550)], [VIEW1.CS.PRODUCTGRPCODE(0x7ea69ce2e840)], [VIEW1.CS.HANDLERCODE(0x7ea69ce2eb30)], [VIEW1.CS.POLICYNO(0x7ea69ce2ee20)], [VIEW1.CS.ENDORSENO(0x7ea69ce2f110)], [VIEW1.CS.SIGNPREMIUM(0x7ea69ce2f400)], [VIEW1.CS.SIGNDATE(0x7ea69ce2f6f0)], [VIEW1.CS.STARTDATE(0x7ea69ce2f9e0)], [VIEW1.CS.COMMISSIONTYPE(0x7ea69ce2fcd0)], [VIEW1.CS.AGENTCODE(0x7ea69ce2ffc0)], [VIEW1.B.OPERATOR(0x7ea69ce302b0)], [VIEW1.A.OLDCOMMISSION(0x7ea69ce305a0)], [VIEW1.A.OLDCOMMISSIONRATE(0x7ea69ce30890)], [VIEW1.B.COMMISSION(0x7ea69ce30b80)], [VIEW1.B.COMMISSIONRATE(0x7ea69ce30e70)], [VIEW1.B.UPDATEDATE(0x7ea69ce31160)]), filter(nil), 
  38.       access([VIEW1.CS.BRANCHCODE(0x7ea69ce2d3b0)], [VIEW1.CS.PAYCURRENCY(0x7ea69ce2df70)], [VIEW1.CS.DEPTCODE(0x7ea69ce2e260)], [VIEW1.CS.SECTIONCODE(0x7ea69ce2e550)], [VIEW1.CS.PRODUCTGRPCODE(0x7ea69ce2e840)], [VIEW1.CS.HANDLERCODE(0x7ea69ce2eb30)], [VIEW1.CS.POLICYNO(0x7ea69ce2ee20)], [VIEW1.CS.ENDORSENO(0x7ea69ce2f110)], [VIEW1.CS.SIGNPREMIUM(0x7ea69ce2f400)], [VIEW1.CS.SIGNDATE(0x7ea69ce2f6f0)], [VIEW1.CS.STARTDATE(0x7ea69ce2f9e0)], [VIEW1.CS.COMMISSIONTYPE(0x7ea69ce2fcd0)], [VIEW1.CS.AGENTCODE(0x7ea69ce2ffc0)], [VIEW1.B.OPERATOR(0x7ea69ce302b0)], [VIEW1.A.OLDCOMMISSION(0x7ea69ce305a0)], [VIEW1.A.OLDCOMMISSIONRATE(0x7ea69ce30890)], [VIEW1.B.COMMISSION(0x7ea69ce30b80)], [VIEW1.B.COMMISSIONRATE(0x7ea69ce30e70)], [VIEW1.B.UPDATEDATE(0x7ea69ce31160)])
  39.   3 - output([CS.BRANCHCODE(0x7e6038e53d20)], [CS.PAYCURRENCY(0x7e6038e54300)], [CS.DEPTCODE(0x7e6038e545f0)], [CS.SECTIONCODE(0x7e6038e548e0)], [CS.PRODUCTGRPCODE(0x7e6038e54bd0)], [CS.HANDLERCODE(0x7e6038e54ec0)], [CS.POLICYNO(0x7e6038e551b0)], [CS.ENDORSENO(0x7e6038e554a0)], [CS.SIGNPREMIUM(0x7e6038e55790)], [CS.SIGNDATE(0x7e6038e55a80)], [CS.STARTDATE(0x7e6038e55d70)], [CS.COMMISSIONTYPE(0x7e6038e56060)], [CS.AGENTCODE(0x7e6038e56350)], [BB.OPERATOR(0x7e6038e59500)], [AA.OLDCOMMISSION(0x7e6038e56c20)], [AA.OLDCOMMISSIONRATE(0x7e6038e56930)], [BB.COMMISSION(0x7e6038e59dd0)], [BB.COMMISSIONRATE(0x7e6038e59ae0)], [BB.UPDATEDATE(0x7e6038e597f0)]), filter(nil), limit(?), offset(nil)
  40.   4 - output([CS.BRANCHCODE(0x7e6038e53d20)], [CS.PAYCURRENCY(0x7e6038e54300)], [CS.DEPTCODE(0x7e6038e545f0)], [CS.SECTIONCODE(0x7e6038e548e0)], [CS.PRODUCTGRPCODE(0x7e6038e54bd0)], [CS.HANDLERCODE(0x7e6038e54ec0)], [CS.POLICYNO(0x7e6038e551b0)], [CS.ENDORSENO(0x7e6038e554a0)], [CS.SIGNPREMIUM(0x7e6038e55790)], [CS.SIGNDATE(0x7e6038e55a80)], [CS.STARTDATE(0x7e6038e55d70)], [CS.COMMISSIONTYPE(0x7e6038e56060)], [CS.AGENTCODE(0x7e6038e56350)], [BB.OPERATOR(0x7e6038e59500)], [AA.OLDCOMMISSION(0x7e6038e56c20)], [AA.OLDCOMMISSIONRATE(0x7e6038e56930)], [BB.COMMISSION(0x7e6038e59dd0)], [BB.COMMISSIONRATE(0x7e6038e59ae0)], [BB.UPDATEDATE(0x7e6038e597f0)]), filter(nil), 
  41.       conds(nil), nl_params_([AA.COMMITSSSSS(0x7e6038e58f20)]), batch_join=true
  42.   5 - output([BB.OPERATOR(0x7e6038e59500)], [AA.OLDCOMMISSION(0x7e6038e56c20)], [AA.OLDCOMMISSIONRATE(0x7e6038e56930)], [BB.COMMISSION(0x7e6038e59dd0)], [BB.COMMISSIONRATE(0x7e6038e59ae0)], [BB.UPDATEDATE(0x7e6038e597f0)], [AA.COMMITSSSSS(0x7e6038e58f20)]), filter(nil), 
  43.       conds(nil), nl_params_([BB.COMMITSSSSS(0x7e6038e5a0c0)]), batch_join=false
  44.   6 - output([BB.OPERATOR(0x7e6038e59500)], [BB.UPDATEDATE(0x7e6038e597f0)], [BB.COMMISSIONRATE(0x7e6038e59ae0)], [BB.COMMISSION(0x7e6038e59dd0)], [BB.COMMITSSSSS(0x7e6038e5a0c0)]), filter([BB.NUM(0x7e6038e59210) = 1(0x7e6038e5bf60)]), 
  45.       access([BB.NUM(0x7e6038e59210)], [BB.OPERATOR(0x7e6038e59500)], [BB.UPDATEDATE(0x7e6038e597f0)], [BB.COMMISSIONRATE(0x7e6038e59ae0)], [BB.COMMISSION(0x7e6038e59dd0)], [BB.COMMITSSSSS(0x7e6038e5a0c0)])
  46.   7 - output([T_WIN_FUN_ROW_NUMBER()(0x7e8a68c83950)], [T1.OPERATOR(0x7e8a68c7d500)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)], [T2.COMMITSSSSS(0x7e8a68c7cf20)]), filter(nil), 
  47.       win_expr(T_WIN_FUN_ROW_NUMBER()(0x7e8a68c83950)), partition_by([T2.COMMITSSSSS(0x7e8a68c7cf20)]), order_by([T1.UPDATEDATE(0x7e8a68c7d210), DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
  48.   8 - output([T2.COMMITSSSSS(0x7e8a68c7cf20)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.OPERATOR(0x7e8a68c7d500)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)]), filter(nil), sort_keys([T2.COMMITSSSSS(0x7e8a68c7cf20), ASC], [T1.UPDATEDATE(0x7e8a68c7d210), DESC])
  49.   9 - output([T2.COMMITSSSSS(0x7e8a68c7cf20)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.OPERATOR(0x7e8a68c7d500)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)]), filter(nil), 
  50.       equal_conds([T1.RATECHANGENO(0x7e8a68c7c650) = T2.RATECHANGENO(0x7e8a68c7c940)(0x7e8a68c807b0)]), other_conds(nil)
  51.   10 - output([T2.RATECHANGENO(0x7e8a68c7c940)], [T2.COMMITSSSSS(0x7e8a68c7cf20)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)]), filter([T2.SUBCOMPANY(0x7e8a68c7b7a0) = ?(0x7e8a68c7ddd0)], [T2.COMMISSIONTYPE(0x7e8a68c7c360) = ?(0x7e8a68c7e630)]), 
  52.       access([T2.SUBCOMPANY(0x7e8a68c7b7a0)], [T2.COMMISSIONTYPE(0x7e8a68c7c360)], [T2.RATECHANGENO(0x7e8a68c7c940)], [T2.COMMITSSSSS(0x7e8a68c7cf20)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)]), partitions(p0), 
  53.       is_index_back=true, filter_before_indexback[false,false], 
  54.       range_key([T2.UPDATEDATE(0x7e8a68c7bd80)], [T2.RATECHANGEDETAILNO(0x7e8a68cc91c0)]), range(2021-08-01 00:00:00,MIN ; 2021-09-01 00:00:00,MIN), 
  55.       range_cond([T2.UPDATEDATE(0x7e8a68c7bd80) >= ?(0x7e8a68c7f6f0)], [T2.UPDATEDATE(0x7e8a68c7bd80) < ?(0x7e8a68c7ff50)])
  56.   11 - output([T1.RATECHANGENO(0x7e8a68c7c650)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.OPERATOR(0x7e8a68c7d500)]), filter([T1.STATUS(0x7e8a68c7cc30) = ?(0x7e8a68c7ee90)], [(T_OP_IS, T1.OPERFLAG(0x7e8a68c7c070), NULL, 0)(0x7e8a68c82360) OR T1.OPERFLAG(0x7e8a68c7c070) = ?(0x7e8a68c830f0)(0x7e8a68c81b10)]), 
  57.       access([T1.OPERFLAG(0x7e8a68c7c070)], [T1.RATECHANGENO(0x7e8a68c7c650)], [T1.STATUS(0x7e8a68c7cc30)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.OPERATOR(0x7e8a68c7d500)]), partitions(p0), 
  58.       is_index_back=true, filter_before_indexback[true,false], 
  59.       range_key([T1.UNIQUECODE(0x7e8a68c7ba90)], [T1.STATUS(0x7e8a68c7cc30)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.RATECHANGENO(0x7e8a68c7c650)]), range(5010100,1,MIN,MIN ; 5010100,1,MAX,MAX), 
  60.       range_cond([(T_OP_LIKE, T1.UNIQUECODE(0x7e8a68c7ba90), ?, '\')(0x7e8a68c81010)])
  61.   12 - output([AA.OLDCOMMISSIONRATE(0x7e6038e56930)], [AA.OLDCOMMISSION(0x7e6038e56c20)], [AA.COMMITSSSSS(0x7e6038e58f20)]), filter([AA.NUM(0x7e6038e56640) = 1(0x7ed75e458270)]), 
  62.       access([AA.NUM(0x7e6038e56640)], [AA.OLDCOMMISSIONRATE(0x7e6038e56930)], [AA.OLDCOMMISSION(0x7e6038e56c20)], [AA.COMMITSSSSS(0x7e6038e58f20)])
  63.   13 - output([T_WIN_FUN_ROW_NUMBER()(0x7ed75e4a1850)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)], [T2.COMMITSSSSS(0x7ed75e49e010)]), filter(nil), 
  64.       win_expr(T_WIN_FUN_ROW_NUMBER()(0x7ed75e4a1850)), partition_by([T2.COMMITSSSSS(0x7ed75e49e010)]), order_by([T1.UPDATEDATE(0x7ed75e49e300), ASC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
  65.   14 - output([T2.COMMITSSSSS(0x7ed75e49e010)], [T1.UPDATEDATE(0x7ed75e49e300)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), filter(nil), sort_keys([T1.UPDATEDATE(0x7ed75e49e300), ASC])
  66.   15 - output([T2.COMMITSSSSS(0x7ed75e49e010)], [T1.UPDATEDATE(0x7ed75e49e300)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), filter(nil), 
  67.       conds(nil), nl_params_([T2.RATECHANGENO(0x7ed75e49da30)]), batch_join=true
  68.   16 - output([T2.RATECHANGENO(0x7ed75e49da30)], [T2.COMMITSSSSS(0x7ed75e49e010)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), filter([T2.SUBCOMPANY(0x7ed75e49ce70) = ?(0x7ed75e49ebd0)], [T2.COMMISSIONTYPE(0x7ed75e49d450) = ?(0x7ed75e49f430)]), 
  69.       access([T2.SUBCOMPANY(0x7ed75e49ce70)], [T2.COMMISSIONTYPE(0x7ed75e49d450)], [T2.RATECHANGENO(0x7ed75e49da30)], [T2.COMMITSSSSS(0x7ed75e49e010)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), partitions(p0), 
  70.       is_index_back=true, filter_before_indexback[false,false], 
  71.       range_key([T2.COMMITSSSSS(0x7ed75e49e010)], [T2.RATECHANGEDETAILNO(0x7edde8d36330)]), range(MIN,MIN ; MAX,MAX)always true, 
  72.       range_cond([T2.COMMITSSSSS(0x7ed75e49e010) = ?(0x7ed75e4a9cd0)])
  73.   17 - output([T1.UPDATEDATE(0x7ed75e49e300)]), filter([(T_OP_LIKE, T1.UNIQUECODE(0x7ed75e49d160), ?, '\')(0x7ed75e4a0d50)], [T1.STATUS(0x7ed75e49dd20) = ?(0x7ed75e49fc90)]), 
  74.       access([T1.UNIQUECODE(0x7ed75e49d160)], [T1.STATUS(0x7ed75e49dd20)], [T1.UPDATEDATE(0x7ed75e49e300)]), partitions(p0), 
  75.       is_index_back=false, filter_before_indexback[false,false], 
  76.       range_key([T1.RATECHANGENO(0x7ed75e49d740)]), range(MIN ; MAX), 
  77.       range_cond([T1.RATECHANGENO(0x7ed75e49d740) = ?(0x7edde8d7c390)])
  78.   18 - output([CS.BRANCHCODE(0x7e6038e53d20)], [CS.PAYCURRENCY(0x7e6038e54300)], [CS.DEPTCODE(0x7e6038e545f0)], [CS.SECTIONCODE(0x7e6038e548e0)], [CS.PRODUCTGRPCODE(0x7e6038e54bd0)], [CS.HANDLERCODE(0x7e6038e54ec0)], [CS.POLICYNO(0x7e6038e551b0)], [CS.ENDORSENO(0x7e6038e554a0)], [CS.SIGNPREMIUM(0x7e6038e55790)], [CS.SIGNDATE(0x7e6038e55a80)], [CS.STARTDATE(0x7e6038e55d70)], [CS.COMMISSIONTYPE(0x7e6038e56060)], [CS.AGENTCODE(0x7e6038e56350)]), filter([CS.PAYCURRENCY(0x7e6038e54300) = ?(0x7e6038e5d2b0)]), 
  79.       partitions(p27)
  80.   19 - output([CS.BRANCHCODE(0x7e6038e53d20)], [CS.__pk_increment(0x7e6038ea5e30)]), filter([CS.BRANCHCODE(0x7e6038e53d20) = ?(0x7e6038e5ca50)]), 
  81.       access([CS.BRANCHCODE(0x7e6038e53d20)], [CS.__pk_increment(0x7e6038ea5e30)]), partitions(p0), 
  82.       is_index_back=false, filter_before_indexback[false], 
  83.       range_key([CS.COMMITSSSSS(0x7e6038e54010)], [CS.shadow_pk_0(0x7e6038ea6f30)], [CS.shadow_pk_1(0x7e6038ea7220)]), range(MIN ; MAX), 
  84.       range_cond([? = CS.COMMITSSSSS(0x7e6038e54010)(0x7ed75e2f8450)])
  85.   20 - output([UM.UNITCODE(0x7ea69cd57e20)]), filter(nil), 
  86.       distinct([UM.UNITCODE(0x7ea69cd57e20)])
  87.   21 - output([UM.UNITCODE(0x7ea69cd57e20)]), filter(nil), sort_keys([UM.UNITCODE(0x7ea69cd57e20), ASC])
  88.   22 - output([UM.UNITCODE(0x7ea69cd57e20)]), filter([UM.IFVALID(0x7ea69cd57710) = ?(0x7ea69cd56ff0)]), 
  89.       access([UM.IFVALID(0x7ea69cd57710)], [UM.UNITCODE(0x7ea69cd57e20)]), partitions(p0), 
  90.       is_index_back=true, filter_before_indexback[false], 
  91.       range_key([UM.FGS(0x7ea69cd540b0)], [UM.DEPTCODE(0x7ea69cd55200)], [UM.CURRENCYCODE(0x7ea69cd56640)], [UM.UNITMAPPINGID(0x7edde8de2080)]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true, 
  92.       range_cond([UM.FGS(0x7ea69cd540b0) = ?(0x7ea69cd53990)], [UM.DEPTCODE(0x7ea69cd55200) = ?(0x7ea69cd54ae0)], [UM.CURRENCYCODE(0x7ea69cd56640) = ?(0x7ea69cd55f20)])
  93.   23 - output([EE.NAME(0x7ea69cda34e0)]), filter(nil), 
  94.       distinct([EE.NAME(0x7ea69cda34e0)])
  95.   24 - output([EE.NAME(0x7ea69cda34e0)]), filter(nil), sort_keys([EE.NAME(0x7ea69cda34e0), ASC])
  96.   25 - output([EE.NAME(0x7ea69cda34e0)]), filter(nil), 
  97.       access([EE.NAME(0x7ea69cda34e0)]), partitions(p0), 
  98.       is_index_back=true, 
  99.       range_key([EE.CODE(0x7ea69cda1c80)], [EE.UNIT_CODE(0x7ea69cda2dd0)], [EE.EMP_ID(0x7edde8e184c0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true, 
  100.       range_cond([EE.CODE(0x7ea69cda1c80) = ?(0x7ea69cda1560)], [EE.UNIT_CODE(0x7ea69cda2dd0) = ?(0x7ea69cda26b0)])
  102. Plan Type:
  103. -------------------------------------
  104. LOCAL
  106. Optimization Info:
  107. -------------------------------------

 

CSSCS 表是张超级大表，数据量：27.9亿左右，BRANCHCODE 是分区字段，分区类型是列表分区，COMMITSSSSS 字段是主键。

T111 数据量：288万，T222 数据量：6738万，这两张都是普通大表，没有做分区，开窗函数 partition by COMMITSSSSS 字段 。

其实知道数据量以后就好办，CBO无非也是需要通过统计信息来了解每张表大小和数据分布来执行最佳的SQL计划。

如果一条SQL CBO执行缓慢，就需要人工干预进行优化，因为人脑才是最好的CBO。??

 

其实这条SQL在逻辑上是有问题的，我们可以进行等价改写来优化SQL：

  1. SELECT * FROM (
  2.       WITH GG AS  (
  3.             /* 内连视图 */
  4.             SELECT 
  5.                 T2.OLDCOMMISSIONRATE               AS OLDCOMMISSIONRATE,
  6.                 T2.OLDCOMMISSION                   AS OLDCOMMISSION,
  7.                 T2.COMMITSSSSS             AS COMMITSSSSS,
  8.                 T1.OPERFLAG                        AS OPERFLAG,
  9.                 T1.OPERATOR                        AS OPERATOR,
  10.                 T1.UPDATEDATE                      AS UPDATEDATE,
  11.                 T2.COMMISSIONRATE                  AS COMMISSIONRATE,
  12.                 T2.COMMISSION                      AS COMMISSION
  13.             FROM  T111 T1,  T222 T2
  14.             WHERE 1 = 1
  15.             AND T2.SUBCOMPANY = '5010100'
  16.             AND T1.UNIQUECODE LIKE '5010100%'
  17.             AND T2.COMMISSIONTYPE = '0'
  18.             AND T1.RATECHANGENO = T2.RATECHANGENO
  19.             AND T1.STATUS = '1'
  20.       )
  22. SELECT CS.BRANCHCODE,
  23.              (SELECT DISTINCT UM.UNITCODE
  24.               FROM IFUNITMAPPING UM
  25.               WHERE UM.FGS = CS.BRANCHCODE
  26.                 AND UM.DEPTCODE = CS.DEPTCODE
  27.                 AND UM.CURRENCYCODE = CS.PAYCURRENCY
  28.                 AND UM.IFVALID = '1')             AS UNITCODE,
  29.              CS.SECTIONCODE,
  30.              CS.PRODUCTGRPCODE,
  31.              CS.HANDLERCODE,
  32.              (SELECT DISTINCT(NAME)
  33.               FROM IFEMPLOYEE EE
  34.               WHERE EE.CODE = CS.HANDLERCODE
  35.                 AND EE.UNIT_CODE = CS.BRANCHCODE) AS HANDLERNAME,
  36.              CS.POLICYNO,
  37.              CS.ENDORSENO,
  38.              CS.SIGNPREMIUM,
  39.              CS.SIGNDATE,
  40.              CS.STARTDATE,
  41.              CS.COMMISSIONTYPE,
  42.              CS.PAYCURRENCY,
  43.              CS.AGENTCODE,
  44.              B.OPERATOR,
  45.              B.OLDCOMMISSION,
  46.              B.OLDCOMMISSIONRATE,
  47.              B.COMMISSION,
  48.              B.COMMISSIONRATE,
  49.              B.UPDATEDATE
  50.       FROM CSSCS CS,
  51.            (SELECT BB.NUM,
  52.                    BB.OPERATOR,
  53.                    BB.UPDATEDATE,
  54.                    BB.COMMISSIONRATE,
  55.                    BB.COMMISSION,
  56.                    BB.COMMITSSSSS,
  57.                    BB.OPERFLAG,
  58.                    BB.OLDCOMMISSIONRATE,
  59.                    BB.OLDCOMMISSION
  60.             FROM (SELECT ROW_NUMBER() OVER (PARTITION BY GG.COMMITSSSSS ORDER BY GG.UPDATEDATE DESC) AS NUM,
  61.                          GG.*
  62.                   FROM GG
  63.                   WHERE 1 = 1
  64.                     AND GG.UPDATEDATE >= TO_DATE('2021-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  65.                     AND GG.UPDATEDATE < TO_DATE('2021-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  66.                     AND (GG.OPERFLAG IS NULL OR GG.OPERFLAG = '0')
  67.                     ) BB
  68.             WHERE BB.NUM = 1) B
  69.       WHERE 
  70.             B.COMMITSSSSS = CS.COMMITSSSSS
  71.         AND CS.BRANCHCODE = '5010100'
  72.         AND CS.PAYCURRENCY = '01'
  73.         AND ROWNUM <= 5000
  74. );
  1. 执行时间：29.48 ms

改写后执行计划：

  1. ==============================================================================
  2. |ID|OPERATOR                    |NAME                        |EST. ROWS|COST |
  3. ------------------------------------------------------------------------------
  4. |0 |SUBPLAN FILTER              |                            |1        |25287|
  5. |1 | SORT                       |                            |1        |25240|
  6. |2 |  SUBPLAN SCAN              |VIEW1                       |1        |25240|
  7. |3 |   LIMIT                    |                            |1        |25240|
  8. |4 |    NESTED-LOOP JOIN        |                            |1        |25240|
  9. |5 |     SUBPLAN SCAN           |BB                          |1        |25232|
  10. |6 |      WINDOW FUNCTION       |                            |1        |25232|
  11. |7 |       SORT                 |                            |1        |25232|
  12. |8 |        NESTED-LOOP JOIN    |                            |1        |25232|
  13. |9 |         TABLE SCAN         |T1(IND_T111_3)              |1        |25144|
  14. |10|         TABLE SCAN         |T2(IND_T222_1)              |1        |483  |
  15. |11|     TABLE LOOKUP           |CS                          |1        |28   |
  16. |12|      DISTRIBUTED TABLE SCAN|CS(PK_CSSCS)                |1        |5    |
  17. |13| MERGE DISTINCT             |                            |1        |92   |
  18. |14|  SORT                      |                            |1        |92   |
  19. |15|   TABLE SCAN               |UM(IND_IFUNITMAPPING)       |1        |92   |
  20. |16| MERGE DISTINCT             |                            |1        |92   |
  21. |17|  SORT                      |                            |1        |92   |
  22. |18|   TABLE SCAN               |EE(IND_IFEMPLOYEE_5)        |1        |92   |
  23. ==============================================================================
  25. Outputs & filters: 
  26. -------------------------------------
  27.   0 - output([VIEW1.CS.BRANCHCODE(0x7e4fbba992a0)], [subquery(1)(0x7e30f992cbc0)], [VIEW1.CS.SECTIONCODE(0x7e4fbba9a150)], [VIEW1.CS.PRODUCTGRPCODE(0x7e4fbba9a440)], [VIEW1.CS.HANDLERCODE(0x7e4fbba9a730)], [subquery(2)(0x7e4fbb9b9570)], [VIEW1.CS.POLICYNO(0x7e4fbba9aa20)], [VIEW1.CS.ENDORSENO(0x7e4fbba9ad10)], [VIEW1.CS.SIGNPREMIUM(0x7e4fbba9b000)], [VIEW1.CS.SIGNDATE(0x7e4fbba9b2f0)], [VIEW1.CS.STARTDATE(0x7e4fbba9b5e0)], [VIEW1.CS.COMMISSIONTYPE(0x7e4fbba9b8d0)], [VIEW1.CS.PAYCURRENCY(0x7e4fbba99b70)], [VIEW1.CS.AGENTCODE(0x7e4fbba9bbc0)], [VIEW1.B.OPERATOR(0x7e4fbba9beb0)], [VIEW1.B.OLDCOMMISSION(0x7e4fbba9c1a0)], [VIEW1.B.OLDCOMMISSIONRATE(0x7e4fbba9c490)], [VIEW1.B.COMMISSION(0x7e4fbba9c780)], [VIEW1.B.COMMISSIONRATE(0x7e4fbba9ca70)], [VIEW1.B.UPDATEDATE(0x7e4fbba9cd60)]), filter(nil), 
  28.       exec_params_([VIEW1.CS.BRANCHCODE(0x7e4fbba992a0)], [VIEW1.CS.DEPTCODE(0x7e4fbba99e60)], [VIEW1.CS.PAYCURRENCY(0x7e4fbba99b70)], [VIEW1.CS.HANDLERCODE(0x7e4fbba9a730)], [VIEW1.CS.BRANCHCODE(0x7e4fbba992a0)]), onetime_exprs_(nil), init_plan_idxs_(nil)
  29.   1 - output([VIEW1.CS.BRANCHCODE(0x7e4fbba992a0)], [VIEW1.CS.SECTIONCODE(0x7e4fbba9a150)], [VIEW1.CS.PRODUCTGRPCODE(0x7e4fbba9a440)], [VIEW1.CS.HANDLERCODE(0x7e4fbba9a730)], [VIEW1.CS.POLICYNO(0x7e4fbba9aa20)], [VIEW1.CS.ENDORSENO(0x7e4fbba9ad10)], [VIEW1.CS.SIGNPREMIUM(0x7e4fbba9b000)], [VIEW1.CS.SIGNDATE(0x7e4fbba9b2f0)], [VIEW1.CS.STARTDATE(0x7e4fbba9b5e0)], [VIEW1.CS.COMMISSIONTYPE(0x7e4fbba9b8d0)], [VIEW1.CS.PAYCURRENCY(0x7e4fbba99b70)], [VIEW1.CS.AGENTCODE(0x7e4fbba9bbc0)], [VIEW1.B.OPERATOR(0x7e4fbba9beb0)], [VIEW1.B.OLDCOMMISSION(0x7e4fbba9c1a0)], [VIEW1.B.OLDCOMMISSIONRATE(0x7e4fbba9c490)], [VIEW1.B.COMMISSION(0x7e4fbba9c780)], [VIEW1.B.COMMISSIONRATE(0x7e4fbba9ca70)], [VIEW1.B.UPDATEDATE(0x7e4fbba9cd60)], [VIEW1.CS.DEPTCODE(0x7e4fbba99e60)]), filter(nil), sort_keys([VIEW1.B.UPDATEDATE(0x7e4fbba9cd60), ASC])
  30.   2 - output([VIEW1.CS.BRANCHCODE(0x7e4fbba992a0)], [VIEW1.CS.PAYCURRENCY(0x7e4fbba99b70)], [VIEW1.CS.DEPTCODE(0x7e4fbba99e60)], [VIEW1.CS.SECTIONCODE(0x7e4fbba9a150)], [VIEW1.CS.PRODUCTGRPCODE(0x7e4fbba9a440)], [VIEW1.CS.HANDLERCODE(0x7e4fbba9a730)], [VIEW1.CS.POLICYNO(0x7e4fbba9aa20)], [VIEW1.CS.ENDORSENO(0x7e4fbba9ad10)], [VIEW1.CS.SIGNPREMIUM(0x7e4fbba9b000)], [VIEW1.CS.SIGNDATE(0x7e4fbba9b2f0)], [VIEW1.CS.STARTDATE(0x7e4fbba9b5e0)], [VIEW1.CS.COMMISSIONTYPE(0x7e4fbba9b8d0)], [VIEW1.CS.AGENTCODE(0x7e4fbba9bbc0)], [VIEW1.B.OPERATOR(0x7e4fbba9beb0)], [VIEW1.B.OLDCOMMISSION(0x7e4fbba9c1a0)], [VIEW1.B.OLDCOMMISSIONRATE(0x7e4fbba9c490)], [VIEW1.B.COMMISSION(0x7e4fbba9c780)], [VIEW1.B.COMMISSIONRATE(0x7e4fbba9ca70)], [VIEW1.B.UPDATEDATE(0x7e4fbba9cd60)]), filter(nil), 
  31.       access([VIEW1.CS.BRANCHCODE(0x7e4fbba992a0)], [VIEW1.CS.PAYCURRENCY(0x7e4fbba99b70)], [VIEW1.CS.DEPTCODE(0x7e4fbba99e60)], [VIEW1.CS.SECTIONCODE(0x7e4fbba9a150)], [VIEW1.CS.PRODUCTGRPCODE(0x7e4fbba9a440)], [VIEW1.CS.HANDLERCODE(0x7e4fbba9a730)], [VIEW1.CS.POLICYNO(0x7e4fbba9aa20)], [VIEW1.CS.ENDORSENO(0x7e4fbba9ad10)], [VIEW1.CS.SIGNPREMIUM(0x7e4fbba9b000)], [VIEW1.CS.SIGNDATE(0x7e4fbba9b2f0)], [VIEW1.CS.STARTDATE(0x7e4fbba9b5e0)], [VIEW1.CS.COMMISSIONTYPE(0x7e4fbba9b8d0)], [VIEW1.CS.AGENTCODE(0x7e4fbba9bbc0)], [VIEW1.B.OPERATOR(0x7e4fbba9beb0)], [VIEW1.B.OLDCOMMISSION(0x7e4fbba9c1a0)], [VIEW1.B.OLDCOMMISSIONRATE(0x7e4fbba9c490)], [VIEW1.B.COMMISSION(0x7e4fbba9c780)], [VIEW1.B.COMMISSIONRATE(0x7e4fbba9ca70)], [VIEW1.B.UPDATEDATE(0x7e4fbba9cd60)])
  32.   3 - output([CS.BRANCHCODE(0x7eb4cf6acd50)], [CS.PAYCURRENCY(0x7eb4cf6ad330)], [CS.DEPTCODE(0x7eb4cf6ad620)], [CS.SECTIONCODE(0x7eb4cf6ad910)], [CS.PRODUCTGRPCODE(0x7eb4cf6adc00)], [CS.HANDLERCODE(0x7eb4cf6adef0)], [CS.POLICYNO(0x7eb4cf6ae1e0)], [CS.ENDORSENO(0x7eb4cf6ae4d0)], [CS.SIGNPREMIUM(0x7eb4cf6ae7c0)], [CS.SIGNDATE(0x7eb4cf6aeab0)], [CS.STARTDATE(0x7eb4cf6aeda0)], [CS.COMMISSIONTYPE(0x7eb4cf6af090)], [CS.AGENTCODE(0x7eb4cf6af380)], [BB.OPERATOR(0x7eb4cf6b2240)], [BB.OLDCOMMISSION(0x7eb4cf6afc50)], [BB.OLDCOMMISSIONRATE(0x7eb4cf6af960)], [BB.COMMISSION(0x7eb4cf6b2b10)], [BB.COMMISSIONRATE(0x7eb4cf6b2820)], [BB.UPDATEDATE(0x7eb4cf6b2530)]), filter(nil), limit(?), offset(nil)
  33.   4 - output([CS.BRANCHCODE(0x7eb4cf6acd50)], [CS.PAYCURRENCY(0x7eb4cf6ad330)], [CS.DEPTCODE(0x7eb4cf6ad620)], [CS.SECTIONCODE(0x7eb4cf6ad910)], [CS.PRODUCTGRPCODE(0x7eb4cf6adc00)], [CS.HANDLERCODE(0x7eb4cf6adef0)], [CS.POLICYNO(0x7eb4cf6ae1e0)], [CS.ENDORSENO(0x7eb4cf6ae4d0)], [CS.SIGNPREMIUM(0x7eb4cf6ae7c0)], [CS.SIGNDATE(0x7eb4cf6aeab0)], [CS.STARTDATE(0x7eb4cf6aeda0)], [CS.COMMISSIONTYPE(0x7eb4cf6af090)], [CS.AGENTCODE(0x7eb4cf6af380)], [BB.OPERATOR(0x7eb4cf6b2240)], [BB.OLDCOMMISSION(0x7eb4cf6afc50)], [BB.OLDCOMMISSIONRATE(0x7eb4cf6af960)], [BB.COMMISSION(0x7eb4cf6b2b10)], [BB.COMMISSIONRATE(0x7eb4cf6b2820)], [BB.UPDATEDATE(0x7eb4cf6b2530)]), filter(nil), 
  34.       conds(nil), nl_params_([BB.COMMITSSSSS(0x7eb4cf6b1f50)]), batch_join=true
  35.   5 - output([BB.OLDCOMMISSIONRATE(0x7eb4cf6af960)], [BB.OLDCOMMISSION(0x7eb4cf6afc50)], [BB.COMMITSSSSS(0x7eb4cf6b1f50)], [BB.OPERATOR(0x7eb4cf6b2240)], [BB.UPDATEDATE(0x7eb4cf6b2530)], [BB.COMMISSIONRATE(0x7eb4cf6b2820)], [BB.COMMISSION(0x7eb4cf6b2b10)]), filter([BB.NUM(0x7eb4cf6af670) = 1(0x7eb4cf6b3660)]), 
  36.       access([BB.NUM(0x7eb4cf6af670)], [BB.OLDCOMMISSIONRATE(0x7eb4cf6af960)], [BB.OLDCOMMISSION(0x7eb4cf6afc50)], [BB.COMMITSSSSS(0x7eb4cf6b1f50)], [BB.OPERATOR(0x7eb4cf6b2240)], [BB.UPDATEDATE(0x7eb4cf6b2530)], [BB.COMMISSIONRATE(0x7eb4cf6b2820)], [BB.COMMISSION(0x7eb4cf6b2b10)])
  37.   6 - output([T_WIN_FUN_ROW_NUMBER()(0x7eb4cf760030)], [T2.OLDCOMMISSIONRATE(0x7eb4cf758d30)], [T2.OLDCOMMISSION(0x7eb4cf759020)], [T2.COMMITSSSSS(0x7eb4cf759310)], [T1.OPERATOR(0x7eb4cf7598f0)], [T1.UPDATEDATE(0x7eb4cf759be0)], [T2.COMMISSIONRATE(0x7eb4cf759ed0)], [T2.COMMISSION(0x7eb4cf75a1c0)]), filter(nil), 
  38.       win_expr(T_WIN_FUN_ROW_NUMBER()(0x7eb4cf760030)), partition_by([T2.COMMITSSSSS(0x7eb4cf759310)]), order_by([T1.UPDATEDATE(0x7eb4cf759be0), DESC]), window_type(RANGE), upper(UNBOUNDED PRECEDING), lower(UNBOUNDED FOLLOWING)
  39.   7 - output([T2.COMMITSSSSS(0x7eb4cf759310)], [T1.UPDATEDATE(0x7eb4cf759be0)], [T2.OLDCOMMISSIONRATE(0x7eb4cf758d30)], [T2.OLDCOMMISSION(0x7eb4cf759020)], [T1.OPERATOR(0x7eb4cf7598f0)], [T2.COMMISSIONRATE(0x7eb4cf759ed0)], [T2.COMMISSION(0x7eb4cf75a1c0)]), filter(nil), sort_keys([T2.COMMITSSSSS(0x7eb4cf759310), ASC], [T1.UPDATEDATE(0x7eb4cf759be0), DESC])
  40.   8 - output([T2.COMMITSSSSS(0x7eb4cf759310)], [T1.UPDATEDATE(0x7eb4cf759be0)], [T2.OLDCOMMISSIONRATE(0x7eb4cf758d30)], [T2.OLDCOMMISSION(0x7eb4cf759020)], [T1.OPERATOR(0x7eb4cf7598f0)], [T2.COMMISSIONRATE(0x7eb4cf759ed0)], [T2.COMMISSION(0x7eb4cf75a1c0)]), filter(nil), 
  41.       conds(nil), nl_params_([T1.RATECHANGENO(0x7eb4cf758460)]), batch_join=true
  42.   9 - output([T1.RATECHANGENO(0x7eb4cf758460)], [T1.OPERATOR(0x7eb4cf7598f0)], [T1.UPDATEDATE(0x7eb4cf759be0)]), filter([(T_OP_LIKE, T1.UNIQUECODE(0x7eb4cf757e80), ?, '\')(0x7eb4cf75f530)], [T1.STATUS(0x7eb4cf758a40) = ?(0x7eb4cf75c630)], [(T_OP_IS, T1.OPERFLAG(0x7eb4cf759600), NULL, 0)(0x7eb4cf75df40) OR T1.OPERFLAG(0x7eb4cf759600) = ?(0x7eb4cf75ecd0)(0x7eb4cf75d6f0)]), 
  43.       access([T1.UNIQUECODE(0x7eb4cf757e80)], [T1.RATECHANGENO(0x7eb4cf758460)], [T1.STATUS(0x7eb4cf758a40)], [T1.OPERFLAG(0x7eb4cf759600)], [T1.OPERATOR(0x7eb4cf7598f0)], [T1.UPDATEDATE(0x7eb4cf759be0)]), partitions(p0), 
  44.       is_index_back=true, filter_before_indexback[false,false,false], 
  45.       range_key([T1.UPDATEDATE(0x7eb4cf759be0)], [T1.RATECHANGENO(0x7eb4cf758460)]), range(2021-08-01 00:00:00,MIN ; 2021-09-01 00:00:00,MIN), 
  46.       range_cond([T1.UPDATEDATE(0x7eb4cf759be0) >= ?(0x7eb4cf75a4b0)], [T1.UPDATEDATE(0x7eb4cf759be0) < ?(0x7eb4cf75ad10)])
  47.   10 - output([T2.OLDCOMMISSIONRATE(0x7eb4cf758d30)], [T2.OLDCOMMISSION(0x7eb4cf759020)], [T2.COMMITSSSSS(0x7eb4cf759310)], [T2.COMMISSIONRATE(0x7eb4cf759ed0)], [T2.COMMISSION(0x7eb4cf75a1c0)]), filter([T2.SUBCOMPANY(0x7eb4cf757b90) = ?(0x7eb4cf75b570)], [T2.COMMISSIONTYPE(0x7eb4cf758170) = ?(0x7eb4cf75bdd0)]), 
  48.       access([T2.SUBCOMPANY(0x7eb4cf757b90)], [T2.COMMISSIONTYPE(0x7eb4cf758170)], [T2.OLDCOMMISSIONRATE(0x7eb4cf758d30)], [T2.OLDCOMMISSION(0x7eb4cf759020)], [T2.COMMITSSSSS(0x7eb4cf759310)], [T2.COMMISSIONRATE(0x7eb4cf759ed0)], [T2.COMMISSION(0x7eb4cf75a1c0)]), partitions(p0), 
  49.       is_index_back=true, filter_before_indexback[false,false], 
  50.       range_key([T2.RATECHANGENO(0x7eb4cf758750)], [T2.RATECHANGEDETAILNO(0x7eb4cf7ba560)]), range(MIN ; MAX), 
  51.       range_cond([? = T2.RATECHANGENO(0x7eb4cf758750)(0x7eb4cf7dfdb0)])
  52.   11 - output([CS.BRANCHCODE(0x7eb4cf6acd50)], [CS.PAYCURRENCY(0x7eb4cf6ad330)], [CS.DEPTCODE(0x7eb4cf6ad620)], [CS.SECTIONCODE(0x7eb4cf6ad910)], [CS.PRODUCTGRPCODE(0x7eb4cf6adc00)], [CS.HANDLERCODE(0x7eb4cf6adef0)], [CS.POLICYNO(0x7eb4cf6ae1e0)], [CS.ENDORSENO(0x7eb4cf6ae4d0)], [CS.SIGNPREMIUM(0x7eb4cf6ae7c0)], [CS.SIGNDATE(0x7eb4cf6aeab0)], [CS.STARTDATE(0x7eb4cf6aeda0)], [CS.COMMISSIONTYPE(0x7eb4cf6af090)], [CS.AGENTCODE(0x7eb4cf6af380)]), filter([CS.PAYCURRENCY(0x7eb4cf6ad330) = ?(0x7eb4cf6b49b0)]), 
  53.       partitions(p27)
  54.   12 - output([CS.BRANCHCODE(0x7eb4cf6acd50)], [CS.__pk_increment(0x7eb4cf6f38b0)]), filter([CS.BRANCHCODE(0x7eb4cf6acd50) = ?(0x7eb4cf6b4150)]), 
  55.       access([CS.BRANCHCODE(0x7eb4cf6acd50)], [CS.__pk_increment(0x7eb4cf6f38b0)]), partitions(p0), 
  56.       is_index_back=false, filter_before_indexback[false], 
  57.       range_key([CS.COMMITSSSSS(0x7eb4cf6ad040)], [CS.shadow_pk_0(0x7eb4cf6f49b0)], [CS.shadow_pk_1(0x7eb4cf6f4ca0)]), range(MIN ; MAX), 
  58.       range_cond([? = CS.COMMITSSSSS(0x7eb4cf6ad040)(0x7e9ffbd12000)])
  59.   13 - output([UM.UNITCODE(0x7e4fbb9b6e20)]), filter(nil), 
  60.       distinct([UM.UNITCODE(0x7e4fbb9b6e20)])
  61.   14 - output([UM.UNITCODE(0x7e4fbb9b6e20)]), filter(nil), sort_keys([UM.UNITCODE(0x7e4fbb9b6e20), ASC])
  62.   15 - output([UM.UNITCODE(0x7e4fbb9b6e20)]), filter([UM.IFVALID(0x7e4fbb9b6710) = ?(0x7e4fbb9b5ff0)]), 
  63.       access([UM.IFVALID(0x7e4fbb9b6710)], [UM.UNITCODE(0x7e4fbb9b6e20)]), partitions(p0), 
  64.       is_index_back=true, filter_before_indexback[false], 
  65.       range_key([UM.FGS(0x7e4fbb9b30b0)], [UM.DEPTCODE(0x7e4fbb9b4200)], [UM.CURRENCYCODE(0x7e4fbb9b5640)], [UM.UNITMAPPINGID(0x7e9ffbd614f0)]), range(MIN,MIN,MIN,MIN ; MAX,MAX,MAX,MAX)always true, 
  66.       range_cond([UM.FGS(0x7e4fbb9b30b0) = ?(0x7e4fbb9b2990)], [UM.DEPTCODE(0x7e4fbb9b4200) = ?(0x7e4fbb9b3ae0)], [UM.CURRENCYCODE(0x7e4fbb9b5640) = ?(0x7e4fbb9b4f20)])
  67.   16 - output([EE.NAME(0x7e4fbba024e0)]), filter(nil), 
  68.       distinct([EE.NAME(0x7e4fbba024e0)])
  69.   17 - output([EE.NAME(0x7e4fbba024e0)]), filter(nil), sort_keys([EE.NAME(0x7e4fbba024e0), ASC])
  70.   18 - output([EE.NAME(0x7e4fbba024e0)]), filter(nil), 
  71.       access([EE.NAME(0x7e4fbba024e0)]), partitions(p0), 
  72.       is_index_back=true, 
  73.       range_key([EE.CODE(0x7e4fbba00c80)], [EE.UNIT_CODE(0x7e4fbba01dd0)], [EE.EMP_ID(0x7e9ffbd99940)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true, 
  74.       range_cond([EE.CODE(0x7e4fbba00c80) = ?(0x7e4fbba00560)], [EE.UNIT_CODE(0x7e4fbba01dd0) = ?(0x7e4fbba016b0)])
  75. Plan Type:
  76. -------------------------------------
  77. LOCAL
  79. Optimization Info:
  80. -------------------------------------

虽然 OcenBase 从图形化工具看到的执行计划是假的，但是通过观察也可以看到改写前SQL的 COST值从 1097485 降到 25287，执行时间也从 155s 降低到 29.48ms 就能出结果。

通过差集比较SQL改写前后是等价的，本次案例已经优化完成。??????????????

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

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

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号