最近优化了不少SQL，简单的SQL顺手搞了不好意思发出来了忽悠人，复杂很考验逻辑思维的，但是又不想分享出来（自己收藏的案例），怕被人抄袭思路?。
今天遇到一条很有意思的SQL案例：
性能SQL（金融行业关键信息已经隐藏）：
SELECT CS.BRANCHCODE, (SELECT DISTINCT UM.UNITCODE FROM IFUNITMAPPING UM WHERE UM.FGS = CS.BRANCHCODE AND UM.DEPTCODE = CS.DEPTCODE AND UM.CURRENCYCODE = CS.PAYCURRENCY AND UM.IFVALID = '1') AS UNITCODE, CS.SECTIONCODE, CS.PRODUCTGRPCODE, CS.HANDLERCODE, (SELECT DISTINCT(NAME) FROM IFEMPLOYEE EE WHERE EE.CODE = CS.HANDLERCODE AND EE.UNIT_CODE = CS.BRANCHCODE) AS HANDLERNAME, CS.POLICYNO, CS.ENDORSENO, CS.SIGNPREMIUM, CS.SIGNDATE, CS.STARTDATE, CS.COMMISSIONTYPE, CS.PAYCURRENCY, CS.AGENTCODE, B.OPERATOR, A.OLDCOMMISSION, A.OLDCOMMISSIONRATE, B.COMMISSION, B.COMMISSIONRATE, B.UPDATEDATE FROM CSSCS CS, (SELECT AA.NUM, AA.OLDCOMMISSIONRATE, AA.OLDCOMMISSION, AA.COMMITSSSSS FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY T2.COMMITSSSSS ORDER BY T1.UPDATEDATE ASC ) AS NUM, T2.OLDCOMMISSIONRATE AS OLDCOMMISSIONRATE, T2.OLDCOMMISSION AS OLDCOMMISSION, T2.COMMITSSSSS AS COMMITSSSSS, T1.OPERFLAG AS OPERFLAG FROM T111 T1, T222 T2 WHERE 1 = 1 AND T2.SUBCOMPANY = '5010100' AND T1.UNIQUECODE LIKE '5010100%' AND T2.COMMISSIONTYPE = '0' AND T1.RATECHANGENO = T2.RATECHANGENO AND T1.STATUS = '1') AA WHERE AA.NUM = 1) A, (SELECT BB.NUM, BB.OPERATOR, BB.UPDATEDATE, BB.COMMISSIONRATE, BB.COMMISSION, BB.COMMITSSSSS, BB.OPERFLAG FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY T2.COMMITSSSSS ORDER BY T1.UPDATEDATE DESC ) AS NUM, T1.OPERATOR AS OPERATOR, T1.UPDATEDATE AS UPDATEDATE, T2.COMMISSIONRATE AS COMMISSIONRATE, T2.COMMISSION AS COMMISSION, T2.COMMITSSSSS AS COMMITSSSSS, T1.OPERFLAG AS OPERFLAG FROM T111 T1, T222 T2 WHERE 1 = 1 AND T2.SUBCOMPANY = '5010100' AND T1.UNIQUECODE LIKE '5010100%' AND T2.UPDATEDATE >= TO_DATE('2021-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND T2.UPDATEDATE < TO_DATE('2021-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND ( T1.OPERFLAG IS NULL OR T1.OPERFLAG = '0' ) AND T2.COMMISSIONTYPE = '0' AND T1.RATECHANGENO = T2.RATECHANGENO AND T1.STATUS = '1') BB WHERE BB.NUM = 1) B WHERE A.COMMITSSSSS = B.COMMITSSSSS AND A.COMMITSSSSS = CS.COMMITSSSSS AND CS.BRANCHCODE = '5010100' AND CS.PAYCURRENCY = '01' AND ROWNUM <= 5000 ORDER BY B.UPDATEDATE;
执行时间：155 s
执行计划：
================================================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------------------------------------- |0 |SUBPLAN FILTER | |1 |1097660| |1 | SORT | |1 |1097485| |2 | SUBPLAN SCAN |VIEW1 |1 |1097485| |3 | LIMIT | |1 |1097485| |4 | NESTED-LOOP JOIN | |1 |1097485| |5 | NESTED-LOOP JOIN | |1 |1097456| |6 | SUBPLAN SCAN |BB |1 |1097317| |7 | WINDOW FUNCTION | |14332 |1096799| |8 | SORT | |14332 |1089188| |9 | HASH JOIN | |14332 |1051984| |10| TABLE SCAN |T2(IND_T222_5) |14332 |650849 | |11| TABLE SCAN |T1(IND_T111_2) |32610 |376637 | |12| SUBPLAN SCAN |AA |1 |139 | |13| WINDOW FUNCTION | |1 |139 | |14| SORT | |1 |138 | |15| NESTED-LOOP JOIN | |1 |138 | |16| TABLE SCAN |T2(IND_ T222_3) |1 |137 | |17| TABLE GET |T1 |1 |28 | |18| TABLE LOOKUP |CS |1 |28 | |19| DISTRIBUTED TABLE SCAN|CS(PK_CSSCS) |1 |5 | |20| MERGE DISTINCT | |1 |92 | |21| SORT | |1 |92 | |22| TABLE SCAN |UM(IND_IFUNITMAPPING) |1 |92 | |23| MERGE DISTINCT | |1 |92 | |24| SORT | |1 |92 | |25| TABLE SCAN |EE(IND_IFEMPLOYEE_5) |1 |92 | ================================================================================ Outputs & filters: ------------------------------------- 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), 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) 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]) 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), 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)]) 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) 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), conds(nil), nl_params_([AA.COMMITSSSSS(0x7e6038e58f20)]), batch_join=true 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), conds(nil), nl_params_([BB.COMMITSSSSS(0x7e6038e5a0c0)]), batch_join=false 6 - output([BB.OPERATOR(0x7e6038e59500)], [BB.UPDATEDATE(0x7e6038e597f0)], [BB.COMMISSIONRATE(0x7e6038e59ae0)], [BB.COMMISSION(0x7e6038e59dd0)], [BB.COMMITSSSSS(0x7e6038e5a0c0)]), filter([BB.NUM(0x7e6038e59210) = 1(0x7e6038e5bf60)]), access([BB.NUM(0x7e6038e59210)], [BB.OPERATOR(0x7e6038e59500)], [BB.UPDATEDATE(0x7e6038e597f0)], [BB.COMMISSIONRATE(0x7e6038e59ae0)], [BB.COMMISSION(0x7e6038e59dd0)], [BB.COMMITSSSSS(0x7e6038e5a0c0)]) 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), 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) 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]) 9 - output([T2.COMMITSSSSS(0x7e8a68c7cf20)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.OPERATOR(0x7e8a68c7d500)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)]), filter(nil), equal_conds([T1.RATECHANGENO(0x7e8a68c7c650) = T2.RATECHANGENO(0x7e8a68c7c940)(0x7e8a68c807b0)]), other_conds(nil) 10 - output([T2.RATECHANGENO(0x7e8a68c7c940)], [T2.COMMITSSSSS(0x7e8a68c7cf20)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)]), filter([T2.SUBCOMPANY(0x7e8a68c7b7a0) = ?(0x7e8a68c7ddd0)], [T2.COMMISSIONTYPE(0x7e8a68c7c360) = ?(0x7e8a68c7e630)]), access([T2.SUBCOMPANY(0x7e8a68c7b7a0)], [T2.COMMISSIONTYPE(0x7e8a68c7c360)], [T2.RATECHANGENO(0x7e8a68c7c940)], [T2.COMMITSSSSS(0x7e8a68c7cf20)], [T2.COMMISSIONRATE(0x7e8a68c7d7f0)], [T2.COMMISSION(0x7e8a68c7dae0)]), partitions(p0), is_index_back=true, filter_before_indexback[false,false], range_key([T2.UPDATEDATE(0x7e8a68c7bd80)], [T2.RATECHANGEDETAILNO(0x7e8a68cc91c0)]), range(2021-08-01 00:00:00,MIN ; 2021-09-01 00:00:00,MIN), range_cond([T2.UPDATEDATE(0x7e8a68c7bd80) >= ?(0x7e8a68c7f6f0)], [T2.UPDATEDATE(0x7e8a68c7bd80) < ?(0x7e8a68c7ff50)]) 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)]), access([T1.OPERFLAG(0x7e8a68c7c070)], [T1.RATECHANGENO(0x7e8a68c7c650)], [T1.STATUS(0x7e8a68c7cc30)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.OPERATOR(0x7e8a68c7d500)]), partitions(p0), is_index_back=true, filter_before_indexback[true,false], range_key([T1.UNIQUECODE(0x7e8a68c7ba90)], [T1.STATUS(0x7e8a68c7cc30)], [T1.UPDATEDATE(0x7e8a68c7d210)], [T1.RATECHANGENO(0x7e8a68c7c650)]), range(5010100,1,MIN,MIN ; 5010100,1,MAX,MAX), range_cond([(T_OP_LIKE, T1.UNIQUECODE(0x7e8a68c7ba90), ?, '\')(0x7e8a68c81010)]) 12 - output([AA.OLDCOMMISSIONRATE(0x7e6038e56930)], [AA.OLDCOMMISSION(0x7e6038e56c20)], [AA.COMMITSSSSS(0x7e6038e58f20)]), filter([AA.NUM(0x7e6038e56640) = 1(0x7ed75e458270)]), access([AA.NUM(0x7e6038e56640)], [AA.OLDCOMMISSIONRATE(0x7e6038e56930)], [AA.OLDCOMMISSION(0x7e6038e56c20)], [AA.COMMITSSSSS(0x7e6038e58f20)]) 13 - output([T_WIN_FUN_ROW_NUMBER()(0x7ed75e4a1850)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)], [T2.COMMITSSSSS(0x7ed75e49e010)]), filter(nil), 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) 14 - output([T2.COMMITSSSSS(0x7ed75e49e010)], [T1.UPDATEDATE(0x7ed75e49e300)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), filter(nil), sort_keys([T1.UPDATEDATE(0x7ed75e49e300), ASC]) 15 - output([T2.COMMITSSSSS(0x7ed75e49e010)], [T1.UPDATEDATE(0x7ed75e49e300)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), filter(nil), conds(nil), nl_params_([T2.RATECHANGENO(0x7ed75e49da30)]), batch_join=true 16 - output([T2.RATECHANGENO(0x7ed75e49da30)], [T2.COMMITSSSSS(0x7ed75e49e010)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), filter([T2.SUBCOMPANY(0x7ed75e49ce70) = ?(0x7ed75e49ebd0)], [T2.COMMISSIONTYPE(0x7ed75e49d450) = ?(0x7ed75e49f430)]), access([T2.SUBCOMPANY(0x7ed75e49ce70)], [T2.COMMISSIONTYPE(0x7ed75e49d450)], [T2.RATECHANGENO(0x7ed75e49da30)], [T2.COMMITSSSSS(0x7ed75e49e010)], [T2.OLDCOMMISSIONRATE(0x7ed75e49e5f0)], [T2.OLDCOMMISSION(0x7ed75e49e8e0)]), partitions(p0), is_index_back=true, filter_before_indexback[false,false], range_key([T2.COMMITSSSSS(0x7ed75e49e010)], [T2.RATECHANGEDETAILNO(0x7edde8d36330)]), range(MIN,MIN ; MAX,MAX)always true, range_cond([T2.COMMITSSSSS(0x7ed75e49e010) = ?(0x7ed75e4a9cd0)]) 17 - output([T1.UPDATEDATE(0x7ed75e49e300)]), filter([(T_OP_LIKE, T1.UNIQUECODE(0x7ed75e49d160), ?, '\')(0x7ed75e4a0d50)], [T1.STATUS(0x7ed75e49dd20) = ?(0x7ed75e49fc90)]), access([T1.UNIQUECODE(0x7ed75e49d160)], [T1.STATUS(0x7ed75e49dd20)], [T1.UPDATEDATE(0x7ed75e49e300)]), partitions(p0), is_index_back=false, filter_before_indexback[false,false], range_key([T1.RATECHANGENO(0x7ed75e49d740)]), range(MIN ; MAX), range_cond([T1.RATECHANGENO(0x7ed75e49d740) = ?(0x7edde8d7c390)]) 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)]), partitions(p27) 19 - output([CS.BRANCHCODE(0x7e6038e53d20)], [CS.__pk_increment(0x7e6038ea5e30)]), filter([CS.BRANCHCODE(0x7e6038e53d20) = ?(0x7e6038e5ca50)]), access([CS.BRANCHCODE(0x7e6038e53d20)], [CS.__pk_increment(0x7e6038ea5e30)]), partitions(p0), is_index_back=false, filter_before_indexback[false], range_key([CS.COMMITSSSSS(0x7e6038e54010)], [CS.shadow_pk_0(0x7e6038ea6f30)], [CS.shadow_pk_1(0x7e6038ea7220)]), range(MIN ; MAX), range_cond([? = CS.COMMITSSSSS(0x7e6038e54010)(0x7ed75e2f8450)]) 20 - output([UM.UNITCODE(0x7ea69cd57e20)]), filter(nil), distinct([UM.UNITCODE(0x7ea69cd57e20)]) 21 - output([UM.UNITCODE(0x7ea69cd57e20)]), filter(nil), sort_keys([UM.UNITCODE(0x7ea69cd57e20), ASC]) 22 - output([UM.UNITCODE(0x7ea69cd57e20)]), filter([UM.IFVALID(0x7ea69cd57710) = ?(0x7ea69cd56ff0)]), access([UM.IFVALID(0x7ea69cd57710)], [UM.UNITCODE(0x7ea69cd57e20)]), partitions(p0), is_index_back=true, filter_before_indexback[false], 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, range_cond([UM.FGS(0x7ea69cd540b0) = ?(0x7ea69cd53990)], [UM.DEPTCODE(0x7ea69cd55200) = ?(0x7ea69cd54ae0)], [UM.CURRENCYCODE(0x7ea69cd56640) = ?(0x7ea69cd55f20)]) 23 - output([EE.NAME(0x7ea69cda34e0)]), filter(nil), distinct([EE.NAME(0x7ea69cda34e0)]) 24 - output([EE.NAME(0x7ea69cda34e0)]), filter(nil), sort_keys([EE.NAME(0x7ea69cda34e0), ASC]) 25 - output([EE.NAME(0x7ea69cda34e0)]), filter(nil), access([EE.NAME(0x7ea69cda34e0)]), partitions(p0), is_index_back=true, range_key([EE.CODE(0x7ea69cda1c80)], [EE.UNIT_CODE(0x7ea69cda2dd0)], [EE.EMP_ID(0x7edde8e184c0)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true, range_cond([EE.CODE(0x7ea69cda1c80) = ?(0x7ea69cda1560)], [EE.UNIT_CODE(0x7ea69cda2dd0) = ?(0x7ea69cda26b0)]) Plan Type: ------------------------------------- LOCAL Optimization Info: -------------------------------------
CSSCS 表是张大表，数据量：27.9亿左右，BRANCHCODE 是分区字段，分区类型是列表分区，COMMITSSSSS 字段是主键。
T111 数据量：288万，T222 数据量：6738万，这两张都是普通表，没有做分区，开窗函数 partition by COMMITSSSSS 字段 。
其实知道数据量以后就好办，CBO无非也是需要通过统计信息来了解每张表大小和数据分布来执行最佳的SQL计划。
如果一条SQL CBO执行缓慢，就需要人工干预进行优化，因为人脑才是最好的CBO。?
其实这条SQL在逻辑上是有问题的，我们可以进行等价改写来优化SQL：
SELECT * FROM ( WITH GG AS ( /* 内连视图 */ SELECT T2.OLDCOMMISSIONRATE AS OLDCOMMISSIONRATE, T2.OLDCOMMISSION AS OLDCOMMISSION, T2.COMMITSSSSS AS COMMITSSSSS, T1.OPERFLAG AS OPERFLAG, T1.OPERATOR AS OPERATOR, T1.UPDATEDATE AS UPDATEDATE, T2.COMMISSIONRATE AS COMMISSIONRATE, T2.COMMISSION AS COMMISSION FROM T111 T1, T222 T2 WHERE 1 = 1 AND T2.SUBCOMPANY = '5010100' AND T1.UNIQUECODE LIKE '5010100%' AND T2.COMMISSIONTYPE = '0' AND T1.RATECHANGENO = T2.RATECHANGENO AND T1.STATUS = '1' ) SELECT CS.BRANCHCODE, (SELECT DISTINCT UM.UNITCODE FROM IFUNITMAPPING UM WHERE UM.FGS = CS.BRANCHCODE AND UM.DEPTCODE = CS.DEPTCODE AND UM.CURRENCYCODE = CS.PAYCURRENCY AND UM.IFVALID = '1') AS UNITCODE, CS.SECTIONCODE, CS.PRODUCTGRPCODE, CS.HANDLERCODE, (SELECT DISTINCT(NAME) FROM IFEMPLOYEE EE WHERE EE.CODE = CS.HANDLERCODE AND EE.UNIT_CODE = CS.BRANCHCODE) AS HANDLERNAME, CS.POLICYNO, CS.ENDORSENO, CS.SIGNPREMIUM, CS.SIGNDATE, CS.STARTDATE, CS.COMMISSIONTYPE, CS.PAYCURRENCY, CS.AGENTCODE, B.OPERATOR, B.OLDCOMMISSION, B.OLDCOMMISSIONRATE, B.COMMISSION, B.COMMISSIONRATE, B.UPDATEDATE FROM CSSCS CS, (SELECT BB.NUM, BB.OPERATOR, BB.UPDATEDATE, BB.COMMISSIONRATE, BB.COMMISSION, BB.COMMITSSSSS, BB.OPERFLAG, BB.OLDCOMMISSIONRATE, BB.OLDCOMMISSION FROM (SELECT ROW_NUMBER() OVER (PARTITION BY GG.COMMITSSSSS ORDER BY GG.UPDATEDATE DESC) AS NUM, GG.* FROM GG WHERE 1 = 1 AND GG.UPDATEDATE >= TO_DATE('2021-08-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND GG.UPDATEDATE < TO_DATE('2021-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND (GG.OPERFLAG IS NULL OR GG.OPERFLAG = '0') ) BB WHERE BB.NUM = 1) B WHERE B.COMMITSSSSS = CS.COMMITSSSSS AND CS.BRANCHCODE = '5010100' AND CS.PAYCURRENCY = '01' AND ROWNUM <= 5000 );
执行时间：29.48 ms
改写后执行计划：
============================================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------------------------ |0 |SUBPLAN FILTER | |1 |25287| |1 | SORT | |1 |25240| |2 | SUBPLAN SCAN |VIEW1 |1 |25240| |3 | LIMIT | |1 |25240| |4 | NESTED-LOOP JOIN | |1 |25240| |5 | SUBPLAN SCAN |BB |1 |25232| |6 | WINDOW FUNCTION | |1 |25232| |7 | SORT | |1 |25232| |8 | NESTED-LOOP JOIN | |1 |25232| |9 | TABLE SCAN |T1(IND_T111_3) |1 |25144| |10| TABLE SCAN |T2(IND_T222_1) |1 |483 | |11| TABLE LOOKUP |CS |1 |28 | |12| DISTRIBUTED TABLE SCAN|CS(PK_CSSCS) |1 |5 | |13| MERGE DISTINCT | |1 |92 | |14| SORT | |1 |92 | |15| TABLE SCAN |UM(IND_IFUNITMAPPING) |1 |92 | |16| MERGE DISTINCT | |1 |92 | |17| SORT | |1 |92 | |18| TABLE SCAN |EE(IND_IFEMPLOYEE_5) |1 |92 | ============================================================================== Outputs & filters: ------------------------------------- 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), 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) 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]) 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), 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)]) 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) 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), conds(nil), nl_params_([BB.COMMITSSSSS(0x7eb4cf6b1f50)]), batch_join=true 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)]), 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)]) 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), 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) 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]) 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), conds(nil), nl_params_([T1.RATECHANGENO(0x7eb4cf758460)]), batch_join=true 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)]), access([T1.UNIQUECODE(0x7eb4cf757e80)], [T1.RATECHANGENO(0x7eb4cf758460)], [T1.STATUS(0x7eb4cf758a40)], [T1.OPERFLAG(0x7eb4cf759600)], [T1.OPERATOR(0x7eb4cf7598f0)], [T1.UPDATEDATE(0x7eb4cf759be0)]), partitions(p0), is_index_back=true, filter_before_indexback[false,false,false], range_key([T1.UPDATEDATE(0x7eb4cf759be0)], [T1.RATECHANGENO(0x7eb4cf758460)]), range(2021-08-01 00:00:00,MIN ; 2021-09-01 00:00:00,MIN), range_cond([T1.UPDATEDATE(0x7eb4cf759be0) >= ?(0x7eb4cf75a4b0)], [T1.UPDATEDATE(0x7eb4cf759be0) < ?(0x7eb4cf75ad10)]) 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)]), access([T2.SUBCOMPANY(0x7eb4cf757b90)], [T2.COMMISSIONTYPE(0x7eb4cf758170)], [T2.OLDCOMMISSIONRATE(0x7eb4cf758d30)], [T2.OLDCOMMISSION(0x7eb4cf759020)], [T2.COMMITSSSSS(0x7eb4cf759310)], [T2.COMMISSIONRATE(0x7eb4cf759ed0)], [T2.COMMISSION(0x7eb4cf75a1c0)]), partitions(p0), is_index_back=true, filter_before_indexback[false,false], range_key([T2.RATECHANGENO(0x7eb4cf758750)], [T2.RATECHANGEDETAILNO(0x7eb4cf7ba560)]), range(MIN ; MAX), range_cond([? = T2.RATECHANGENO(0x7eb4cf758750)(0x7eb4cf7dfdb0)]) 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)]), partitions(p27) 12 - output([CS.BRANCHCODE(0x7eb4cf6acd50)], [CS.__pk_increment(0x7eb4cf6f38b0)]), filter([CS.BRANCHCODE(0x7eb4cf6acd50) = ?(0x7eb4cf6b4150)]), access([CS.BRANCHCODE(0x7eb4cf6acd50)], [CS.__pk_increment(0x7eb4cf6f38b0)]), partitions(p0), is_index_back=false, filter_before_indexback[false], range_key([CS.COMMITSSSSS(0x7eb4cf6ad040)], [CS.shadow_pk_0(0x7eb4cf6f49b0)], [CS.shadow_pk_1(0x7eb4cf6f4ca0)]), range(MIN ; MAX), range_cond([? = CS.COMMITSSSSS(0x7eb4cf6ad040)(0x7e9ffbd12000)]) 13 - output([UM.UNITCODE(0x7e4fbb9b6e20)]), filter(nil), distinct([UM.UNITCODE(0x7e4fbb9b6e20)]) 14 - output([UM.UNITCODE(0x7e4fbb9b6e20)]), filter(nil), sort_keys([UM.UNITCODE(0x7e4fbb9b6e20), ASC]) 15 - output([UM.UNITCODE(0x7e4fbb9b6e20)]), filter([UM.IFVALID(0x7e4fbb9b6710) = ?(0x7e4fbb9b5ff0)]), access([UM.IFVALID(0x7e4fbb9b6710)], [UM.UNITCODE(0x7e4fbb9b6e20)]), partitions(p0), is_index_back=true, filter_before_indexback[false], 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, range_cond([UM.FGS(0x7e4fbb9b30b0) = ?(0x7e4fbb9b2990)], [UM.DEPTCODE(0x7e4fbb9b4200) = ?(0x7e4fbb9b3ae0)], [UM.CURRENCYCODE(0x7e4fbb9b5640) = ?(0x7e4fbb9b4f20)]) 16 - output([EE.NAME(0x7e4fbba024e0)]), filter(nil), distinct([EE.NAME(0x7e4fbba024e0)]) 17 - output([EE.NAME(0x7e4fbba024e0)]), filter(nil), sort_keys([EE.NAME(0x7e4fbba024e0), ASC]) 18 - output([EE.NAME(0x7e4fbba024e0)]), filter(nil), access([EE.NAME(0x7e4fbba024e0)]), partitions(p0), is_index_back=true, range_key([EE.CODE(0x7e4fbba00c80)], [EE.UNIT_CODE(0x7e4fbba01dd0)], [EE.EMP_ID(0x7e9ffbd99940)]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true, range_cond([EE.CODE(0x7e4fbba00c80) = ?(0x7e4fbba00560)], [EE.UNIT_CODE(0x7e4fbba01dd0) = ?(0x7e4fbba016b0)]) Plan Type: ------------------------------------- LOCAL Optimization Info: -------------------------------------
虽然 OcenBase 从图形化工具看到的执行计划是假的，但是通过观察也可以看到改写前SQL的 COST值从 1097485 降到 25287，执行时间也从 155s 降低到 29.48ms 就能出结果。
通过差集比较SQL改写前后是等价的，本次案例已经优化完成。???????
