SQL中or操作借助改写提升效率案例一则

昨日,朋友发来一则SQL询问有没有什么优化思路,并反馈给我Oracle 的STA给出的SQL”优化”建议是加并行操作提升SQL响应时间,SQL 脱敏后TEXT与执行计划如下:

SELECT A.ID,
       C.ANN AS FILENAME,
       B.CNT AS CONT,
       A.TT,
       A.INFO,
       A.U_TIME AS PUB_DT
FROM A
LEFT JOIN  B ON A.ID = B.O_ID
AND B.CNT IS NOT NULL
LEFT JOIN  C ON C.O_ID = A.ID
WHERE A.U_TIME > NVL(TO_DATE(:1 , 'yyyy-MM-dd HH24:mi:ss'), DATE '2015-01-01')
  OR B.U_TIME > NVL(TO_DATE(:2 , 'yyyy-MM-dd HH24:mi:ss'), DATE '2015-01-01')

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |      1 |        |   337K(100)|          |   6187 |00:00:47.88 |     670K|    636K|
|   1 |  NESTED LOOPS OUTER          |                 |      1 |     48 |   337K  (1)| 01:07:29 |   6187 |00:00:47.88 |     670K|    636K|
|*  2 |   FILTER                     |                 |      1 |        |            |          |   6187 |00:00:47.61 |     641K|    636K|
|*  3 |    HASH JOIN OUTER           |                 |      1 |     48 |   337K  (1)| 01:07:27 |   5594K|00:00:46.87 |     641K|    636K|
|   4 |     TABLE ACCESS FULL        | A               |      1 |   5442K| 52131   (1)| 00:10:26 |   5592K|00:00:06.88 |     200K|    200K|
|*  5 |     TABLE ACCESS FULL        | B               |      1 |   2442K|   142K  (1)| 00:28:36 |   2460K|00:00:24.05 |     441K|    436K|
|   6 |   TABLE ACCESS BY INDEX ROWID| C               |   6187 |      1 |     3   (0)| 00:00:01 |   5657 |00:00:00.25 |   29121 |     0 |
|*  7 |    INDEX RANGE SCAN          | C               |   6187 |      1 |     2   (0)| 00:00:01 |   5657 |00:00:00.18 |   23463 |     0 |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("A"."U_TIME">TO_DATE(' 2021-03-17 13:38:11', 'syyyy-mm-dd hh24:mi:ss') OR "B"."U_TIME">TO_DATE('2021-03-17
              13:38:11', 'syyyy-mm-dd hh24:mi:ss')))
   3 - access("A"."ID"="B"."O_ID")
   5 - filter("B"."CNT" IS NOT NULL)
   7 - access("C"."O_ID"="A"."ID")

从真实的执行计划中看到最终反馈6188行记录,并且资源消耗(buffer gets和reads)高达600K+,这个资源消耗,SQL执行了47s并不奇怪。SQL逻辑并不复杂,并且SQL中存在or的操作,并且朋友反馈如果传值为空可以不用考虑,这种情况基本不会出现,并且经过确认Utime字段上存在索引的,那么我们可以考虑将此SQL进行拆分,同时考虑到or的每一个步骤都返回行数不大的情况下,用到索引,将or改写成union all+lnnvl的形式:

SELECT /*+        gather_plan_statistics  */ A.ID,
                                             C.ANN AS FILENAME,
                                             B.CNT AS CONT,
                                             A.TT,
                                             A.INFO,
                                             A.U_TIME AS PUB_DT
FROM A
LEFT JOIN B ON A.ID = B.O_ID
AND B.CNT IS NOT NULL
LEFT JOIN C ON C.O_ID = A.ID
WHERE A.U_TIME > NVL(TO_DATE('2021-03-17 13:38:11' , 'yyyy-MM-dd HH24:mi:ss'), DATE'2015-01-01')
UNION ALL
SELECT A.ID,
       C.ANN AS FILENAME,
       B.CNT AS CONT,
       A.TT,
       A.INFO,
       A.U_TIME AS PUB_DT
FROM A
LEFT JOIN B ON A.ID = B.O_ID
AND B.CNT IS NOT NULL
LEFT JOIN C ON C.O_ID = A.ID
WHERE B.U_TIME > NVL(TO_DATE('2021-03-17 13:38:11', 'yyyy-MM-dd HH24:mi:ss'), DATE '2015-01-01')
  AND LNNVL(A.U_TIME > NVL(TO_DATE('2021-03-17 13:38:11' , 'yyyy-MM-dd HH24:mi:ss'), DATE'2015-01-01'));

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Starts | E-Rows | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |      1 |        |   170 (100)|          |   6187 |00:00:00.75 | 66639 |      2 |
|   1 |  UNION-ALL                     |                     |      1 |        |            |          |   6187 |00:00:00.75 | 66639 |      2 |
|   2 |   NESTED LOOPS OUTER           |                     |      1 |     17 |    94   (0)| 00:00:02 |   5253 |00:00:00.55 | 45122 |      2 |
|   3 |    NESTED LOOPS OUTER          |                     |      1 |     17 |    48   (0)| 00:00:01 |   5253 |00:00:00.33 | 27337 |      2 |
|   4 |     TABLE ACCESS BY INDEX ROWID| A                   |      1 |     17 |     6   (0)| 00:00:01 |   5253 |00:00:00.09 | 10508 |      0 |
|*  5 |      INDEX RANGE SCAN          | A_UPD               |      1 |     17 |     3   (0)| 00:00:01 |   5253 |00:00:00.05 |  5255 |      0 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| B                   |   5253 |      1 |     3   (0)| 00:00:01 |   4749 |00:00:00.23 | 16829 |      2 |
|*  7 |      INDEX RANGE SCAN          | B_TXT               |   5253 |      1 |     2   (0)| 00:00:01 |   4749 |00:00:00.12 | 12079 |      1 |
|   8 |    TABLE ACCESS BY INDEX ROWID | C                   |   5253 |      1 |     3   (0)| 00:00:01 |   4798 |00:00:00.20 | 17785 |      0 |
|*  9 |     INDEX RANGE SCAN           | C                   |   5253 |      1 |     2   (0)| 00:00:01 |   4798 |00:00:00.13 | 12987 |      0 |
|  10 |   NESTED LOOPS OUTER           |                     |      1 |     14 |    75   (0)| 00:00:01 |    934 |00:00:00.15 | 21517 |      0 |
|  11 |    NESTED LOOPS                |                     |      1 |     14 |    37   (0)| 00:00:01 |    934 |00:00:00.09 | 17148 |      0 |
|* 12 |     TABLE ACCESS BY INDEX ROWID| B                   |      1 |     14 |     9   (0)| 00:00:01 |   5591 |00:00:00.04 |  3535 |      0 |
|* 13 |      INDEX RANGE SCAN          | B_UPD               |      1 |     14 |     3   (0)| 00:00:01 |   5591 |00:00:00.01 |   978 |      0 |
|* 14 |     TABLE ACCESS BY INDEX ROWID| A                   |   5591 |      1 |     2   (0)| 00:00:01 |    934 |00:00:00.05 | 13613 |      0 |
|* 15 |      INDEX UNIQUE SCAN         | PK_A                |   5591 |      1 |     1   (0)| 00:00:01 |   5590 |00:00:00.03 |  8023 |      0 |
|  16 |    TABLE ACCESS BY INDEX ROWID | C                   |    934 |      1 |     3   (0)| 00:00:01 |    859 |00:00:00.05 |  4369 |      0 |
|* 17 |     INDEX RANGE SCAN           | C                   |    934 |      1 |     2   (0)| 00:00:01 |    859 |00:00:00.04 |  3510 |      0 |
-------------------------------------------------------------------------------------------------------------------------------------------------

恰好被驱动的表连接列中均有索引,经过优化后SQL,并且带入多个变量测试,耗时效率不到1秒。