OceanBase金融亿万级别数据量优化案例（转）

 性能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