左右SQL执行计划妙招 ③ ——执行计划SQL写法差异改变之1

本文链接：https://blog.csdn.net/weixin_39534833/article/details/148113211

梁敬彬梁敬弘兄弟出品

往期回顾
左右SQL执**行计划妙招 ①——子查询的应用范围
左右SQL执行计划妙招 ②——Hint无效原因

引言：一个简单改动背后的深层逻辑

在SQL优化的征途中，我们时常会遇到这样的困惑：同样的业务逻辑，不同的写法却带来截然不同的执行效率。今天我们来深入探讨一个经典案例——WITH子句如何通过改变SQL的写法，从根本上优化执行计划。

1. SQL写法的差异

实验环境准备
首先，让我们搭建一个真实的测试环境：

drop table t_with;
CREATE TABLE T_WITH AS SELECT ROWNUM ID, A.* FROM DBA_SOURCE A WHERE ROWNUM < 100001;
SET autotrace traceonly
Set linesize 1000

传统写法：重复计算的代价

语句1（传统写法）：


SELECT ID, NAME FROM T_WITH
WHERE ID IN 
(SELECT MAX(ID) FROM T_WITH 
 UNION ALL
 SELECT MIN(ID) FROM T_WITH
 UNION ALL
 SELECT TRUNC(AVG(ID)) FROM T_WITH);
执行计划
-----------------------------------------------------------------------------------
Plan hash value: 647530712
-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     3 |   129 |  1382   (1)| 00:00:17 |
|*  1 |  HASH JOIN             |          |     3 |   129 |  1382   (1)| 00:00:17 |
|   2 |   VIEW                 | VW_NSO_1 |     3 |    39 |  1035   (1)| 00:00:13 |
|   3 |    HASH UNIQUE         |          |     3 |    39 |  1035  (67)| 00:00:13 |
|   4 |     UNION-ALL          |          |       |       |            |          |
|   5 |      SORT AGGREGATE    |          |     1 |    13 |            |          |
|   6 |       TABLE ACCESS FULL| T_WITH   | 91060 |  1156K|   345   (1)| 00:00:05 |
|   7 |      SORT AGGREGATE    |          |     1 |    13 |            |          |
|   8 |       TABLE ACCESS FULL| T_WITH   | 91060 |  1156K|   345   (1)| 00:00:05 |
|   9 |      SORT AGGREGATE    |          |     1 |    13 |            |          |
|  10 |       TABLE ACCESS FULL| T_WITH   | 91060 |  1156K|   345   (1)| 00:00:05 |
|  11 |   TABLE ACCESS FULL    | T_WITH   | 91060 |  2667K|   345   (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------------------------
   1 - access("ID"="MAX(ID)")
Note
-----
   - dynamic sampling used for this statement (level=2)

关键发现：

四次全表扫描：操作ID 6、8、10、11分别对T_WITH表进行了完整扫描
重复计算：MAX、MIN、AVG三个聚合函数各自独立扫描整个表
资源消耗：consistent gets高达4969次，总成本1382

统计信息详解

统计信息
------------------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
       4969  consistent gets
          0  physical reads
          0  redo size
        558  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

WITH子句优化：一次计算，多次复用

语句2（WITH子句优化）：

WITH AGG AS (SELECT MAX(ID) MAX, MIN(ID) MIN, TRUNC(AVG(ID)) AVG FROM T_WITH)
 SELECT ID, NAME FROM T_WITH 
 WHERE ID IN 
 ( SELECT MAX FROM AGG  UNION ALL  SELECT MIN FROM AGG  UNION ALL  SELECT AVG FROM AGG);
执行计划
-----------------------------------------------------------------------------------------
Plan hash value: 3705751949
-----------------------------------------------------------------------------------------
| Id |Operation                 | Name                       |Rows  | Bytes |Cost (%CPU)|
-----------------------------------------------------------------------------------------
|   0|SELECT STATEMENT          |                            |    3 |   129 |  697   (1)|
|   1| TEMP TABLE TRANSFORMATION|                            |      |       |           |
|   2|  LOAD AS SELECT          | SYS_TEMP_0FD9D6605_3B91BA4 |      |       |           |
|   3|   SORT AGGREGATE         |                            |    1 |    13 |           |
|   4|    TABLE ACCESS FULL     | T_WITH                     |91060 |  1156K|  345   (1)|
|*  5|  HASH JOIN               |                            |    3 |   129 |  352   (1)|
|   6|   VIEW                   | VW_NSO_1                   |    3 |    39 |    6   (0)|
|   7|    HASH UNIQUE           |                            |    3 |    39 |    6  (67)|
|   8|     UNION-ALL            |                            |      |       |           |
|   9|      VIEW                |                            |    1 |    13 |    2   (0)|
|  10|       TABLE ACCESS FULL  | SYS_TEMP_0FD9D6605_3B91BA4 |    1 |    13 |    2   (0)|
|  11|      VIEW                |                            |    1 |    13 |    2   (0)|
|  12|       TABLE ACCESS FULL  | SYS_TEMP_0FD9D6605_3B91BA4 |    1 |    13 |    2   (0)|
|  13|      VIEW                |                            |    1 |    13 |    2   (0)|
|  14|       TABLE ACCESS FULL  | SYS_TEMP_0FD9D6605_3B91BA4 |    1 |    13 |    2   (0)|
|  15|   TABLE ACCESS FULL      | T_WITH                     |91060 |  2667K|  345   (1)|
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-----------------------------------------------------------------------------------------
   5 - access("ID"="MAX")
Note
-----
   - dynamic sampling used for this statement (level=2)

核心变化：

TEMP TABLE TRANSFORMATION：Oracle自动创建临时表转换
SYS_TEMP_0FD9D6605_3B91BA4：系统生成的临时表，存储WITH子句的结果
一次聚合计算：操作ID 3-4只进行一次全表扫描和聚合
多次轻量级访问：操作ID 10、12、14访问的是小巧的临时表

统计信息对比

统计信息
----------------------------------------------------------------------------------------
          2  recursive calls
          8  db block gets
       2496  consistent gets
          1  physical reads
        600  redo size
        558  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed