性能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(0x7ea69ce2d3