标子查询优化和改写SQL案例
京华开发一哥们找我优化条报表SQL，反馈执行时间很慢需要 31s 才能出结果，安排。
原SQL(31s):
SELECT * FROM (SELECT A.YEAR AS YEAR, A.MONTH AS MONTH, B.APP_ID AS MAIN_ID, B.MERCHANT_NAME AS MAIN_NAME, COUNT(1) AS TOTAL_COUNT, SUM(A.CONTENT_SPLIT_COUNT) AS SPLIT_TOTAL_COUNT, (SELECT COALESCE(COUNT(X1.ID), 0) FROM ABBA X1 INNER JOIN YBBY Y1 ON X1.ID = Y1.TICKET_ID WHERE TO_CHAR(X1.CREATE_TIME, 'yyyy-mm') = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X1.USER_ID) = TO_CHAR(B.APP_ID) AND Y1.STATE = 0) AS SUCCESS_COUNT, (SELECT COALESCE(SUM(X2.CONTENT_SPLIT_COUNT), 0) FROM ABBA X2 INNER JOIN YBBY Y2 ON X2.ID = Y2.TICKET_ID WHERE TO_CHAR(X2.CREATE_TIME, 'yyyy-mm') = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X2.USER_ID) = TO_CHAR(B.APP_ID) AND Y2.STATE = 0) AS SUCCESS_SPLIT_COUNT, (SELECT COALESCE(COUNT(X3.ID), 0) FROM ABBA X3 INNER JOIN YBBY Y3 ON X3.ID = Y3.TICKET_ID WHERE TO_CHAR(X3.CREATE_TIME, 'yyyy-mm') = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X3.USER_ID) = TO_CHAR(B.APP_ID) AND Y3.STATE = 1) AS FAIL_COUNT, (SELECT COALESCE(SUM(X4.CONTENT_SPLIT_COUNT), 0) FROM ABBA X4 INNER JOIN YBBY Y4 ON X4.ID = Y4.TICKET_ID WHERE TO_CHAR(X4.CREATE_TIME, 'yyyy-mm') = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X4.USER_ID) = TO_CHAR(B.APP_ID) AND Y4.STATE = 1) AS FAIL_SPLIT_COUNT, (SELECT COALESCE(COUNT(1), 0) FROM ABBA X5 WHERE TO_CHAR(X5.CREATE_TIME, 'yyyy-mm') = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X5.USER_ID) = TO_CHAR(B.APP_ID) AND NOT EXISTS (SELECT 1 FROM YBBY Y5 WHERE Y5.TICKET_ID = X5.ID)) AS NOT_STATEREPORT_COUNT, (SELECT COALESCE(SUM(X6.CONTENT_SPLIT_COUNT), 0) FROM ABBA X6 WHERE TO_CHAR(X6.CREATE_TIME, 'yyyy-mm') = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X6.USER_ID) = TO_CHAR(B.APP_ID) AND NOT EXISTS (SELECT 1 FROM YBBY Y6 WHERE Y6.TICKET_ID = X6.ID)) AS SPLIT_NOT_STATEREPORT_COUNT, TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AS SEND_DATE FROM ABBA A LEFT JOIN BCCB B ON TO_CHAR(A.USER_ID) = TO_CHAR(B.APP_ID) WHERE B.APP_ID IS NOT NULL AND TO_CHAR(A.CREATE_TIME, 'yyyy-mm') = '2022-07' GROUP BY A.YEAR, A.MONTH, B.APP_ID, B.MERCHANT_NAME, TO_CHAR(A.CREATE_TIME, 'yyyy-mm'));
原SQL执行计划:
------------------------------------- Plan hash value: 4219287564 -------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 647 |00:00:00.10 | 5335 | 5322 | | | | | 1 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:05.25 | 325K| 325K| | | | |* 2 | HASH JOIN | | 62 | 1 | 0 |00:00:05.25 | 325K| 325K| 2293K| 2293K| 690K (0)| |* 3 | TABLE ACCESS FULL| ABBA | 62 | 1 | 62 |00:00:05.20 | 310K| 310K| | | | |* 4 | TABLE ACCESS FULL| YBBY | 62 | 8889 | 0 |00:00:00.04 | 15252 | 15128 | | | | | 5 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:05.30 | 325K| 325K| | | | |* 6 | HASH JOIN | | 62 | 1 | 0 |00:00:05.30 | 325K| 325K| 1888K| 1888K| 692K (0)| |* 7 | TABLE ACCESS FULL| ABBA | 62 | 1 | 62 |00:00:05.25 | 310K| 310K| | | | |* 8 | TABLE ACCESS FULL| YBBY | 62 | 8889 | 0 |00:00:00.04 | 15252 | 15128 | | | | | 9 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:05.31 | 325K| 325K| | | | |* 10 | HASH JOIN | | 62 | 1 | 54 |00:00:05.30 | 325K| 325K| 2293K| 2293K| 690K (0)| |* 11 | TABLE ACCESS FULL| ABBA | 62 | 1 | 62 |00:00:05.22 | 310K| 310K| | | | |* 12 | TABLE ACCESS FULL| YBBY | 62 | 10000 | 622K|00:00:00.08 | 15252 | 15128 | | | | | 13 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:05.33 | 325K| 325K| | | | |* 14 | HASH JOIN | | 62 | 1 | 54 |00:00:05.32 | 325K| 325K| 1888K| 1888K| 694K (0)| |* 15 | TABLE ACCESS FULL| ABBA | 62 | 1 | 62 |00:00:05.24 | 310K| 310K| | | | |* 16 | TABLE ACCESS FULL| YBBY | 62 | 10000 | 622K|00:00:00.08 | 15252 | 15128 | | | | | 17 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:05.51 | 325K| 325K| | | | |* 18 | HASH JOIN ANTI | | 62 | 1 | 0 |00:00:05.51 | 325K| 325K| 2293K| 2293K| 690K (0)| |* 19 | TABLE ACCESS FULL| ABBA | 62 | 1 | 62 |00:00:05.24 | 310K| 310K| | | | | 20 | TABLE ACCESS FULL| YBBY | 62 | 100K| 6200K|00:00:00.14 | 15252 | 15128 | | | | | 21 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:05.53 | 325K| 325K| | | | |* 22 | HASH JOIN ANTI | | 62 | 1 | 0 |00:00:05.53 | 325K| 325K| 1888K| 1888K| 718K (0)| |* 23 | TABLE ACCESS FULL| ABBA | 62 | 1 | 62 |00:00:05.26 | 310K| 310K| | | | | 24 | TABLE ACCESS FULL| YBBY | 62 | 100K| 6200K|00:00:00.14 | 15252 | 15128 | | | | | 25 | HASH GROUP BY | | 1 | 100K| 647 |00:00:00.10 | 5335 | 5322 | 948K| 948K| 4643K (0)| |* 26 | HASH JOIN | | 1 | 100K| 647 |00:00:00.10 | 5335 | 5322 | 1313K| 1313K| 1520K (0)| |* 27 | TABLE ACCESS FULL| ABBA | 1 | 10000 | 6147 |00:00:00.10 | 5007 | 5000 | | | | |* 28 | TABLE ACCESS FULL| BCCB | 1 | 100K| 100K|00:00:00.01 | 328 | 322 | | | | -------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 2 - access(""X1"".""ID""=""Y1"".""TICKET_ID"")" " 3 - filter((TO_CHAR(""X1"".""USER_ID"")=TO_CHAR(:B1) AND TO_CHAR(INTERNAL_FUNCTION(""X1"".""CREATE_TIME""),'yyyy-mm')=:" B2)) " 4 - filter(""Y1"".""STATE""=0)" " 6 - access(""X2"".""ID""=""Y2"".""TICKET_ID"")" " 7 - filter((TO_CHAR(""X2"".""USER_ID"")=TO_CHAR(:B1) AND TO_CHAR(INTERNAL_FUNCTION(""X2"".""CREATE_TIME""),'yyyy-mm')=:" B2)) " 8 - filter(""Y2"".""STATE""=0)" " 10 - access(""X3"".""ID""=""Y3"".""TICKET_ID"")" " 11 - filter((TO_CHAR(""X3"".""USER_ID"")=TO_CHAR(:B1) AND TO_CHAR(INTERNAL_FUNCTION(""X3"".""CREATE_TIME""),'yyyy-mm')=:" B2)) " 12 - filter(""Y3"".""STATE""=1)" " 14 - access(""X4"".""ID""=""Y4"".""TICKET_ID"")" " 15 - filter((TO_CHAR(""X4"".""USER_ID"")=TO_CHAR(:B1) AND TO_CHAR(INTERNAL_FUNCTION(""X4"".""CREATE_TIME""),'yyyy-mm')=:" B2)) " 16 - filter(""Y4"".""STATE""=1)" " 18 - access(""Y5"".""TICKET_ID""=""X5"".""ID"")" " 19 - filter((TO_CHAR(""X5"".""USER_ID"")=TO_CHAR(:B1) AND TO_CHAR(INTERNAL_FUNCTION(""X5"".""CREATE_TIME""),'yyyy-mm')=:" B2)) " 22 - access(""Y6"".""TICKET_ID""=""X6"".""ID"")" " 23 - filter((TO_CHAR(""X6"".""USER_ID"")=TO_CHAR(:B1) AND TO_CHAR(INTERNAL_FUNCTION(""X6"".""CREATE_TIME""),'yyyy-mm')=:" B2)) " 26 - access(TO_CHAR(""A"".""USER_ID"")=TO_CHAR(""B"".""APP_ID""))" " 27 - filter(TO_CHAR(INTERNAL_FUNCTION(""A"".""CREATE_TIME""),'yyyy-mm')='2022-07')" " 28 - filter(""B"".""APP_ID"" IS NOT NULL)"
执行时间：
可以看到执行计划中 Predicate Information 谓词过滤关联列都是 filter(没有索引) ，ABBA、BCCB、YBBY 三张表都是通过全秒扫描进行关联。
像这种情况其实我们加两条索引就能优化这条SQL。
对 ABBA 创建两条索引：
CREATE INDEX idx_ABBA_1 ON ABBA (to_char(create_time,'yyyy-mm')); CREATE INDEX idx_ABBA_2 ON ABBA (to_char(user_id));
添加索引后的执行计划：
"SQL_ID 99yy3574g4kcs, child number 0" ------------------------------------- Plan hash value: 1760442330 ----------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 647 |00:00:00.04 | 3900 | 5047 | | | | | 1 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:00.05 | 16564 | 15220 | | | | |* 2 | HASH JOIN | | 62 | 147 | 0 |00:00:00.05 | 16564 | 15220 | 2293K| 2293K| 483K (0)| | 3 | TABLE ACCESS BY INDEX ROWID | ABBA | 62 | 100 | 62 |00:00:00.02 | 1312 | 92 | | | | | 4 | BITMAP CONVERSION TO ROWIDS | | 62 | | 62 |00:00:00.02 | 1250 | 92 | | | | | 5 | BITMAP AND | | 62 | | 62 |00:00:00.02 | 1250 | 92 | | | | | 6 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.01 | 186 | 92 | | | | |* 7 | INDEX RANGE SCAN | IDX_ABBA_2 | 62 | 4000 | 62 |00:00:00.01 | 186 | 92 | | | | | 8 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.02 | 1064 | 0 | | | | |* 9 | INDEX RANGE SCAN | IDX_ABBA_1 | 62 | 4000 | 381K|00:00:00.01 | 1064 | 0 | | | | |* 10 | TABLE ACCESS FULL | YBBY | 62 | 8889 | 0 |00:00:00.03 | 15252 | 15128 | | | | | 11 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:00.05 | 16564 | 15128 | | | | |* 12 | HASH JOIN | | 62 | 147 | 0 |00:00:00.05 | 16564 | 15128 | 1888K| 1888K| 485K (0)| | 13 | TABLE ACCESS BY INDEX ROWID | ABBA | 62 | 100 | 62 |00:00:00.02 | 1312 | 0 | | | | | 14 | BITMAP CONVERSION TO ROWIDS | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 15 | BITMAP AND | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 16 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.01 | 186 | 0 | | | | |* 17 | INDEX RANGE SCAN | IDX_ABBA_2 | 62 | 4000 | 62 |00:00:00.01 | 186 | 0 | | | | | 18 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.02 | 1064 | 0 | | | | |* 19 | INDEX RANGE SCAN | IDX_ABBA_1 | 62 | 4000 | 381K|00:00:00.01 | 1064 | 0 | | | | |* 20 | TABLE ACCESS FULL | YBBY | 62 | 8889 | 0 |00:00:00.03 | 15252 | 15128 | | | | | 21 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:00.09 | 16564 | 15128 | | | | |* 22 | HASH JOIN | | 62 | 154 | 54 |00:00:00.09 | 16564 | 15128 | 2293K| 2293K| 483K (0)| | 23 | TABLE ACCESS BY INDEX ROWID | ABBA | 62 | 100 | 62 |00:00:00.02 | 1312 | 0 | | | | | 24 | BITMAP CONVERSION TO ROWIDS | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 25 | BITMAP AND | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 26 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.01 | 186 | 0 | | | | |* 27 | INDEX RANGE SCAN | IDX_ABBA_2 | 62 | 4000 | 62 |00:00:00.01 | 186 | 0 | | | | | 28 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.02 | 1064 | 0 | | | | |* 29 | INDEX RANGE SCAN | IDX_ABBA_1 | 62 | 4000 | 381K|00:00:00.02 | 1064 | 0 | | | | |* 30 | TABLE ACCESS FULL | YBBY | 62 | 10000 | 622K|00:00:00.07 | 15252 | 15128 | | | | | 31 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:00.09 | 16564 | 15128 | | | | |* 32 | HASH JOIN | | 62 | 154 | 54 |00:00:00.09 | 16564 | 15128 | 1888K| 1888K| 481K (0)| | 33 | TABLE ACCESS BY INDEX ROWID | ABBA | 62 | 100 | 62 |00:00:00.02 | 1312 | 0 | | | | | 34 | BITMAP CONVERSION TO ROWIDS | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 35 | BITMAP AND | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 36 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.01 | 186 | 0 | | | | |* 37 | INDEX RANGE SCAN | IDX_ABBA_2 | 62 | 4000 | 62 |00:00:00.01 | 186 | 0 | | | | | 38 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.02 | 1064 | 0 | | | | |* 39 | INDEX RANGE SCAN | IDX_ABBA_1 | 62 | 4000 | 381K|00:00:00.01 | 1064 | 0 | | | | |* 40 | TABLE ACCESS FULL | YBBY | 62 | 10000 | 622K|00:00:00.07 | 15252 | 15128 | | | | | 41 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:00.28 | 16564 | 15128 | | | | |* 42 | HASH JOIN ANTI | | 62 | 1 | 0 |00:00:00.28 | 16564 | 15128 | 2293K| 2293K| 681K (0)| | 43 | TABLE ACCESS BY INDEX ROWID | ABBA | 62 | 100 | 62 |00:00:00.02 | 1312 | 0 | | | | | 44 | BITMAP CONVERSION TO ROWIDS | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 45 | BITMAP AND | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 46 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.01 | 186 | 0 | | | | |* 47 | INDEX RANGE SCAN | IDX_ABBA_2 | 62 | 4000 | 62 |00:00:00.01 | 186 | 0 | | | | | 48 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.02 | 1064 | 0 | | | | |* 49 | INDEX RANGE SCAN | IDX_ABBA_1 | 62 | 4000 | 381K|00:00:00.01 | 1064 | 0 | | | | | 50 | TABLE ACCESS FULL | YBBY | 62 | 100K| 6200K|00:00:00.14 | 15252 | 15128 | | | | | 51 | SORT AGGREGATE | | 62 | 1 | 62 |00:00:00.28 | 16564 | 15128 | | | | |* 52 | HASH JOIN ANTI | | 62 | 1 | 0 |00:00:00.28 | 16564 | 15128 | 1888K| 1888K| 458K (0)| | 53 | TABLE ACCESS BY INDEX ROWID | ABBA | 62 | 100 | 62 |00:00:00.02 | 1312 | 0 | | | | | 54 | BITMAP CONVERSION TO ROWIDS | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 55 | BITMAP AND | | 62 | | 62 |00:00:00.02 | 1250 | 0 | | | | | 56 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.01 | 186 | 0 | | | | |* 57 | INDEX RANGE SCAN | IDX_ABBA_2 | 62 | 4000 | 62 |00:00:00.01 | 186 | 0 | | | | | 58 | BITMAP CONVERSION FROM ROWIDS| | 62 | | 62 |00:00:00.02 | 1064 | 0 | | | | |* 59 | INDEX RANGE SCAN | IDX_ABBA_1 | 62 | 4000 | 381K|00:00:00.01 | 1064 | 0 | | | | | 60 | TABLE ACCESS FULL | YBBY | 62 | 100K| 6200K|00:00:00.14 | 15252 | 15128 | | | | | 61 | HASH GROUP BY | | 1 | 10000 | 647 |00:00:00.04 | 3900 | 5047 | 948K| 948K| 1434K (0)| |* 62 | HASH JOIN | | 1 | 10000 | 647 |00:00:00.04 | 3900 | 5047 | 1313K| 1313K| 1614K (0)| | 63 | TABLE ACCESS BY INDEX ROWID | ABBA | 1 | 10000 | 6147 |00:00:00.05 | 3572 | 4725 | | | | |* 64 | INDEX RANGE SCAN | IDX_ABBA_1 | 1 | 4000 | 6147 |00:00:00.01 | 19 | 20 | | | | |* 65 | TABLE ACCESS FULL | BCCB | 1 | 100K| 100K|00:00:00.01 | 328 | 322 | | | | ----------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 2 - access(""X1"".""ID""=""Y1"".""TICKET_ID"")" " 7 - access(""X1"".""SYS_NC00009$""=TO_CHAR(:B1))" " 9 - access(""X1"".""SYS_NC00008$""=:B1)" " 10 - filter(""Y1"".""STATE""=0)" " 12 - access(""X2"".""ID""=""Y2"".""TICKET_ID"")" " 17 - access(""X2"".""SYS_NC00009$""=TO_CHAR(:B1))" " 19 - access(""X2"".""SYS_NC00008$""=:B1)" " 20 - filter(""Y2"".""STATE""=0)" " 22 - access(""X3"".""ID""=""Y3"".""TICKET_ID"")" " 27 - access(""X3"".""SYS_NC00009$""=TO_CHAR(:B1))" " 29 - access(""X3"".""SYS_NC00008$""=:B1)" " 30 - filter(""Y3"".""STATE""=1)" " 32 - access(""X4"".""ID""=""Y4"".""TICKET_ID"")" " 37 - access(""X4"".""SYS_NC00009$""=TO_CHAR(:B1))" " 39 - access(""X4"".""SYS_NC00008$""=:B1)" " 40 - filter(""Y4"".""STATE""=1)" " 42 - access(""Y5"".""TICKET_ID""=""X5"".""ID"")" " 47 - access(""X5"".""SYS_NC00009$""=TO_CHAR(:B1))" " 49 - access(""X5"".""SYS_NC00008$""=:B1)" " 52 - access(""Y6"".""TICKET_ID""=""X6"".""ID"")" " 57 - access(""X6"".""SYS_NC00009$""=TO_CHAR(:B1))" " 59 - access(""X6"".""SYS_NC00008$""=:B1)" " 62 - access(TO_CHAR(""USER_ID"")=TO_CHAR(""APP_ID""))" " 64 - access(""A"".""SYS_NC00008$""='2022-07')" " 65 - filter(""B"".""APP_ID"" IS NOT NULL)"
添加索引后的执行时间：
添加两条索引以后，SQL整体执行时间从 31s 降至 620ms 就能抛出结果，本条SQL到此已经优化完毕，也能满足开发的需求。
最后再提供一个等价改写的最佳优化方案（除非是适配项目可以改SQL，一般已经在生产环境的话要改写SQL进行优化代价非常高），这里仅供学习。
等价改写优化方案：
SELECT * FROM (WITH A AS (SELECT * FROM ABBA A WHERE TO_CHAR(A.CREATE_TIME, 'yyyy-mm') = '2022-07') SELECT * FROM (SELECT A.YEAR AS YEAR, A.MONTH AS MONTH, B.APP_ID AS MAIN_ID, B.MERCHANT_NAME AS MAIN_NAME, COUNT(1) AS TOTAL_COUNT, SUM(A.CONTENT_SPLIT_COUNT) AS SPLIT_TOTAL_COUNT, COALESCE(X1.CNT, 0) AS SUCCESS_COUNT, COALESCE(SUM_X2, 0) AS SUCCESS_SPLIT_COUNT, COALESCE(X3.CNT, 0) AS FAIL_COUNT, COALESCE(X4.SUM_X4, 0) AS FAIL_SPLIT_COUNT, COALESCE(X5.CNT, 0) AS NOT_STATEREPORT_COUNT, COALESCE(X6.SUM_X6, 0) AS SPLIT_NOT_STATEREPORT_COUNT, TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AS SEND_DATE FROM A LEFT JOIN BCCB B ON TO_CHAR(A.USER_ID) = TO_CHAR(B.APP_ID) LEFT JOIN (SELECT TO_CHAR(X1.CREATE_TIME, 'yyyy-mm') CREATE_TIME, TO_CHAR(X1.USER_ID) USER_ID, COUNT(X1.ID) CNT FROM A X1 INNER JOIN YBBY Y1 ON (X1.ID = Y1.TICKET_ID AND Y1.STATE = 0) GROUP BY TO_CHAR(X1.CREATE_TIME, 'yyyy-mm'), TO_CHAR(X1.USER_ID)) X1 ON X1.CREATE_TIME = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X1.USER_ID) = TO_CHAR(B.APP_ID) LEFT JOIN (SELECT TO_CHAR(X2.CREATE_TIME, 'yyyy-mm') CREATE_TIME, TO_CHAR(X2.USER_ID) USER_ID, SUM(X2.CONTENT_SPLIT_COUNT) SUM_X2 FROM A X2 INNER JOIN YBBY Y2 ON (X2.ID = Y2.TICKET_ID AND Y2.STATE = 0) GROUP BY TO_CHAR(X2.CREATE_TIME, 'yyyy-mm'), TO_CHAR(X2.USER_ID)) X2 ON X2.CREATE_TIME = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X2.USER_ID) = TO_CHAR(B.APP_ID) LEFT JOIN (SELECT TO_CHAR(X3.CREATE_TIME, 'yyyy-mm') CREATE_TIME, TO_CHAR(X3.USER_ID) USER_ID, COUNT(X3.ID) CNT FROM A X3 INNER JOIN YBBY Y3 ON (X3.ID = Y3.TICKET_ID AND Y3.STATE = 1) GROUP BY TO_CHAR(X3.CREATE_TIME, 'yyyy-mm'), TO_CHAR(X3.USER_ID)) X3 ON X3.CREATE_TIME = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X3.USER_ID) = TO_CHAR(B.APP_ID) LEFT JOIN (SELECT TO_CHAR(X4.CREATE_TIME, 'yyyy-mm') CREATE_TIME, TO_CHAR(X4.USER_ID) USER_ID, SUM(X4.CONTENT_SPLIT_COUNT) SUM_X4 FROM A X4 INNER JOIN YBBY Y4 ON (X4.ID = Y4.TICKET_ID AND Y4.STATE = 1) GROUP BY TO_CHAR(X4.CREATE_TIME, 'yyyy-mm'), TO_CHAR(X4.USER_ID)) X4 ON X4.CREATE_TIME = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X4.USER_ID) = TO_CHAR(B.APP_ID) LEFT JOIN (SELECT TO_CHAR(X5.CREATE_TIME, 'yyyy-mm') CREATE_TIME, TO_CHAR(X5.USER_ID) USER_ID, COUNT(1) CNT FROM A X5 LEFT JOIN YBBY Y5 ON Y5.TICKET_ID = X5.ID WHERE Y5.TICKET_ID IS NULL GROUP BY TO_CHAR(X5.CREATE_TIME, 'yyyy-mm'), TO_CHAR(X5.USER_ID)) X5 ON X5.CREATE_TIME = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X5.USER_ID) = TO_CHAR(B.APP_ID) LEFT JOIN (SELECT TO_CHAR(X6.CREATE_TIME, 'yyyy-mm') CREATE_TIME, TO_CHAR(X6.USER_ID) USER_ID, SUM(X6.CONTENT_SPLIT_COUNT) SUM_X6 FROM A X6 LEFT JOIN YBBY Y6 ON Y6.TICKET_ID = X6.ID WHERE Y6.TICKET_ID IS NULL GROUP BY TO_CHAR(X6.CREATE_TIME, 'yyyy-mm'), TO_CHAR(X6.USER_ID)) X6 ON X6.CREATE_TIME = TO_CHAR(A.CREATE_TIME, 'yyyy-mm') AND TO_CHAR(X6.USER_ID) = TO_CHAR(B.APP_ID) WHERE B.APP_ID IS NOT NULL GROUP BY A.YEAR, A.MONTH, B.APP_ID, B.MERCHANT_NAME, COALESCE(X1.CNT, 0), COALESCE(SUM_X2, 0), COALESCE(X3.CNT, 0), COALESCE(X4.SUM_X4, 0), COALESCE(X5.CNT, 0), COALESCE(X6.SUM_X6, 0), TO_CHAR(A.CREATE_TIME, 'yyyy-mm')));
改写后的执行计划：
"SQL_ID 4xyamhtsh7qqk, child number 0" ------------------------------------- Plan hash value: 2035834640 -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 647 |00:00:00.04 | 5578 | 1945 | 31 | | | | | 1 | VIEW | | 1 | 1000 | 647 |00:00:00.04 | 5578 | 1945 | 31 | | | | | 2 | TEMP TABLE TRANSFORMATION | | 1 | | 647 |00:00:00.04 | 5578 | 1945 | 31 | | | | | 3 | LOAD AS SELECT | | 1 | | 0 |00:00:00.01 | 3606 | 0 | 31 | 270K| 270K| 270K (0)| | 4 | TABLE ACCESS BY INDEX ROWID | ABBA | 1 | 10000 | 6147 |00:00:00.01 | 3572 | 0 | 0 | | | | |* 5 | INDEX RANGE SCAN | IDX_ABBA_1 | 1 | 4000 | 6147 |00:00:00.01 | 19 | 0 | 0 | | | | | 6 | HASH GROUP BY | | 1 | 1000 | 647 |00:00:00.03 | 1967 | 1945 | 0 | 888K| 888K| 1369K (0)| |* 7 | HASH JOIN OUTER | | 1 | 1000 | 647 |00:00:00.03 | 1967 | 1945 | 0 | 785K| 785K| 1341K (0)| |* 8 | HASH JOIN OUTER | | 1 | 1000 | 647 |00:00:00.02 | 1689 | 1669 | 0 | 800K| 800K| 1318K (0)| |* 9 | HASH JOIN OUTER | | 1 | 1000 | 647 |00:00:00.02 | 1411 | 1393 | 0 | 828K| 828K| 1401K (0)| |* 10 | HASH JOIN OUTER | | 1 | 1000 | 647 |00:00:00.01 | 1133 | 1117 | 0 | 874K| 874K| 1374K (0)| |* 11 | HASH JOIN OUTER | | 1 | 1000 | 647 |00:00:00.01 | 855 | 841 | 0 | 930K| 930K| 1350K (0)| |* 12 | HASH JOIN OUTER | | 1 | 1000 | 647 |00:00:00.01 | 609 | 597 | 0 | 1027K| 1027K| 1329K (0)| | 13 | VIEW | | 1 | 1000 | 647 |00:00:00.01 | 363 | 353 | 0 | | | | |* 14 | HASH JOIN | | 1 | 1000 | 647 |00:00:00.01 | 363 | 353 | 0 | 1313K| 1313K| 1527K (0)| | 15 | VIEW | | 1 | 10000 | 6147 |00:00:00.01 | 35 | 31 | 0 | | | | | 16 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_66209B | 1 | 10000 | 6147 |00:00:00.01 | 35 | 31 | 0 | | | | |* 17 | TABLE ACCESS FULL | BCCB | 1 | 100K| 100K|00:00:00.01 | 328 | 322 | 0 | | | | | 18 | VIEW | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | | | | | 19 | HASH GROUP BY | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | 1106K| 1106K| | | 20 | VIEW | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | | | | |* 21 | HASH JOIN | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | 1519K| 1519K| 523K (0)| |* 22 | TABLE ACCESS FULL | YBBY | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | | | | | 23 | VIEW | | 0 | 10000 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | | 24 | TABLE ACCESS FULL| SYS_TEMP_0FD9D660C_66209B | 0 | 10000 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | | 25 | VIEW | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | | | | | 26 | HASH GROUP BY | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | 1041K| 1041K| | | 27 | VIEW | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | | | | |* 28 | HASH JOIN | | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | 1519K| 1519K| 525K (0)| |* 29 | TABLE ACCESS FULL | YBBY | 1 | 8889 | 0 |00:00:00.01 | 246 | 244 | 0 | | | | | 30 | VIEW | | 0 | 10000 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | | 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_66209B | 0 | 10000 | 0 |00:00:00.01 | 0 | 0 | 0 | | | | | 32 | VIEW | | 1 | 10000 | 37 |00:00:00.01 | 278 | 276 | 0 | | | | | 33 | HASH GROUP BY | | 1 | 10000 | 37 |00:00:00.01 | 278 | 276 | 0 | 1282K| 1282K| 1433K (0)| | 34 | VIEW | | 1 | 10000 | 54 |00:00:00.01 | 278 | 276 | 0 | | | | |* 35 | HASH JOIN | | 1 | 10000 | 54 |00:00:00.01 | 278 | 276 | 0 | 1969K| 1969K| 1971K (0)| |* 36 | TABLE ACCESS FULL | YBBY | 1 | 10000 | 10036 |00:00:00.01 | 246 | 244 | 0 | | | | | 37 | VIEW | | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_66209B | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 39 | VIEW | | 1 | 10000 | 37 |00:00:00.01 | 278 | 276 | 0 | | | | | 40 | HASH GROUP BY | | 1 | 10000 | 37 |00:00:00.01 | 278 | 276 | 0 | 1088K| 1088K| 1434K (0)| | 41 | VIEW | | 1 | 10000 | 54 |00:00:00.01 | 278 | 276 | 0 | | | | |* 42 | HASH JOIN | | 1 | 10000 | 54 |00:00:00.01 | 278 | 276 | 0 | 1969K| 1969K| 1950K (0)| |* 43 | TABLE ACCESS FULL | YBBY | 1 | 10000 | 10036 |00:00:00.01 | 246 | 244 | 0 | | | | | 44 | VIEW | | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_66209B | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 46 | VIEW | | 1 | 10000 | 6085 |00:00:00.01 | 278 | 276 | 0 | | | | | 47 | HASH GROUP BY | | 1 | 10000 | 6085 |00:00:00.01 | 278 | 276 | 0 | 1350K| 1350K| 1455K (0)| | 48 | VIEW | | 1 | 10000 | 6085 |00:00:00.01 | 278 | 276 | 0 | | | | |* 49 | FILTER | | 1 | | 6085 |00:00:00.01 | 278 | 276 | 0 | | | | |* 50 | HASH JOIN OUTER | | 1 | 10000 | 6696 |00:00:00.01 | 278 | 276 | 0 | 1393K| 1393K| 1559K (0)| | 51 | VIEW | | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 52 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_66209B | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 53 | TABLE ACCESS FULL | YBBY | 1 | 100K| 100K|00:00:00.01 | 246 | 244 | 0 | | | | | 54 | VIEW | | 1 | 10000 | 6085 |00:00:00.01 | 278 | 276 | 0 | | | | | 55 | HASH GROUP BY | | 1 | 10000 | 6085 |00:00:00.01 | 278 | 276 | 0 | 1338K| 1338K| 1457K (0)| | 56 | VIEW | | 1 | 10000 | 6085 |00:00:00.01 | 278 | 276 | 0 | | | | |* 57 | FILTER | | 1 | | 6085 |00:00:00.01 | 278 | 276 | 0 | | | | |* 58 | HASH JOIN OUTER | | 1 | 10000 | 6696 |00:00:00.01 | 278 | 276 | 0 | 1409K| 1409K| 1498K (0)| | 59 | VIEW | | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 60 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660C_66209B | 1 | 10000 | 6147 |00:00:00.01 | 32 | 32 | 0 | | | | | 61 | TABLE ACCESS FULL | YBBY | 1 | 100K| 100K|00:00:00.01 | 246 | 244 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- " 5 - access(""A"".""SYS_NC00008$""='2022-07')" " 7 - access(""X6"".""USER_ID""=TO_CHAR(""from$_subquery$_006"".""APP_ID"") AND ""X6"".""CREATE_TIME""=TO_CHAR(INTERNAL_FUNCTION(""from$_subquery$_006"".""CREATE_TIME""),'" yyyy-mm')) " 8 - access(""X5"".""USER_ID""=TO_CHAR(""from$_subquery$_006"".""APP_ID"") AND ""X5"".""CREATE_TIME""=TO_CHAR(INTERNAL_FUNCTION(""from$_subquery$_006"".""CREATE_TIME""),'" yyyy-mm')) " 9 - access(""X4"".""USER_ID""=TO_CHAR(""from$_subquery$_006"".""APP_ID"") AND ""X4"".""CREATE_TIME""=TO_CHAR(INTERNAL_FUNCTION(""from$_subquery$_006"".""CREATE_TIME""),'" yyyy-mm')) " 10 - access(""X3"".""USER_ID""=TO_CHAR(""from$_subquery$_006"".""APP_ID"") AND ""X3"".""CREATE_TIME""=TO_CHAR(INTERNAL_FUNCTION(""from$_subquery$_006"".""CREATE_TIME""),'" yyyy-mm')) " 11 - access(""X2"".""USER_ID""=TO_CHAR(""from$_subquery$_006"".""APP_ID"") AND ""X2"".""CREATE_TIME""=TO_CHAR(INTERNAL_FUNCTION(""from$_subquery$_006"".""CREATE_TIME""),'" yyyy-mm')) " 12 - access(""X1"".""USER_ID""=TO_CHAR(""B"".""APP_ID"") AND ""X1"".""CREATE_TIME""=TO_CHAR(INTERNAL_FUNCTION(""A"".""CREATE_TIME""),'yyyy-mm'))" " 14 - access(TO_CHAR(""APP_ID"")=TO_CHAR(""A"".""USER_ID""))" " 17 - filter(""B"".""APP_ID"" IS NOT NULL)" " 21 - access(""X1"".""ID""=""Y1"".""TICKET_ID"")" " 22 - filter(""Y1"".""STATE""=0)" " 28 - access(""X2"".""ID""=""Y2"".""TICKET_ID"")" " 29 - filter(""Y2"".""STATE""=0)" " 35 - access(""X3"".""ID""=""Y3"".""TICKET_ID"")" " 36 - filter(""Y3"".""STATE""=1)" " 42 - access(""X4"".""ID""=""Y4"".""TICKET_ID"")" " 43 - filter(""Y4"".""STATE""=1)" " 49 - filter(""Y5"".""TICKET_ID"" IS NULL)" " 50 - access(""Y5"".""TICKET_ID""=""X5"".""ID"")" " 57 - filter(""Y6"".""TICKET_ID"" IS NULL)" " 58 - access(""Y6"".""TICKET_ID""=""X6"".""ID"")"
改写后的执行时间：
可以看到改写以后，SQL只需要200ms就能跑出结果了，和原SQL比对过是完全等价。
以上SQL只是以 left join 方式进行等价改写，，过熟悉SQL的同学能看懂笔者的改写思路即可。
提示：SQL等价改写不是一下就能改写成功，需要静下心来思考SQL的逻辑和表与表之间的比对关系，一步一步改来调试最终才能改写成功。
