往期回顾：

左右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

总结

通过这个经典案例，我们深刻理解了WITH子句的优化威力。前后执行计划的对比如下：

 - WITH子句通过"一次计算，多次复用"的策略，将4次全表扫描优化为2次

 - consistent gets从4969次降至2496次，性能提升近50%

 - 系统自动生成的临时表SYS_TEMP_*是WITH子句高效运作的关键机制

当我们面对包含重复子查询的SQL时，WITH子句往往是一个值得尝试的优化方向。它不仅能显著提升查询性能，还能让SQL逻辑更加清晰易懂。正如这个案例所展示的，有时候"看不懂"的执行计划恰恰是最优雅的解决方案。Oracle优化器的智慧，正体现在这些看似复杂实则高效的执行策略中。

未完待续..

公众号：收获不止数据库

微信：ljbyxl1213

• “大白话人工智能” 系列

• “数据库拍案惊奇” 系列

• “世事洞明皆学问” 系列