/*+ MATERIALIZE */ 优化器提示在 WITH 子句中的使用验证

Oracle /*+ MATERIALIZE */ 优化器提示在 WITH 子句中的使用验证

概述

/*+ MATERIALIZE */是 Oracle 数据库的优化器提示（Hint），核心作用是强制将 WITH 子句（公共表表达式，CTE）的查询结果物化到临时表中。当后续查询多次引用该 CTE 时，可直接复用临时表数据，避免重复执行子查询；即使仅引用一次，也能通过该 Hint 强制触发物化行为。

测试场景与验证

场景 1：重复引用子查询（非 WITH 子句）—— 无临时表物化

当相同子查询被多次直接引用（未封装到 WITH 子句）时，Oracle 优化器不会将子查询结果物化到临时表，每次引用都会重新执行子查询。

SELECTmain.cust_id,main.cust_name,main.order_summary,sub1.vip_countFROM(SELECTc1.cust_id,c1.cust_name,SUM(o.order_amount)ASorder_summaryFROM(SELECTcust_id,cust_name,cust_levelFROMcustomersWHEREtotal_consume>50000)c1LEFTJOINorders oONc1.cust_id=o.cust_idGROUPBYc1.cust_id,c1.cust_name)mainCROSSJOIN(SELECTCOUNT(*)ASvip_countFROM(SELECTcust_id,cust_name,cust_levelFROMcustomersWHEREtotal_consume>50000)c2WHEREc2.cust_level='VIP')sub1

执行计划结论：预估执行计划中未使用临时表空间，子查询被重复执行。
在这里插入图片描述

场景 2：重复引用 WITH 子句中的 CTE—— 触发物化

将重复执行的子查询封装到 WITH 子句中，多次引用该 CTE 时，Oracle 会自动将 CTE 结果物化到临时表。

WITHcAS(SELECTcust_id,cust_name,cust_levelFROMcustomersWHEREtotal_consume>50000)SELECTmain.cust_id,main.cust_name,main.order_summary,sub1.vip_countFROM(-- 第一次引用 cSELECTc1.cust_id,c1.cust_name,SUM(o.order_amount)ASorder_summaryFROMc c1LEFTJOINorders oONc1.cust_id=o.cust_idGROUPBYc1.cust_id,c1.cust_name)mainCROSSJOIN(-- 第二次引用 cSELECTCOUNT(*)ASvip_countFROMc c2WHEREc2.cust_level='VIP')sub1

执行计划结论：CTE 的结果集被物化到临时表中，后续引用直接复用临时表数据。

场景 3：单次引用 WITH 子句中的 CTE—— 不触发物化

若 WITH 子句中的 CTE 仅被引用一次，Oracle 优化器默认不会将结果集物化到临时表，而是直接执行子查询。

WITH c AS (SELECT cust_id, cust_name, cust_level FROM customers WHERE total_consume > 50000) SELECT main.cust_id, main.cust_name, main.order_summary FROM ( -- 仅一次引用 c SELECT c1.cust_id, c1.cust_name, SUM(o.order_amount) AS order_summary FROM c c1 LEFT JOIN orders o ON c1.cust_id = o.cust_id GROUP BY c1.cust_id, c1.cust_name) main

执行计划结论：预估执行计划中无临时表物化行为，CTE 子查询直接执行。

场景 4：单次引用 +/*+ MATERIALIZE */ Hint—— 强制物化

在 WITH 子句的 CTE 中添加/*+ MATERIALIZE */Hint，即使 CTE 仅被引用一次，也能强制 Oracle 将结果集物化到临时表。

测试 SQL：

sql

WITH c AS (SELECT /*+ MATERIALIZE */ cust_id, cust_name, cust_level FROM customers WHERE total_consume > 50000) SELECT main.cust_id, main.cust_name, main.order_summary FROM ( -- 仅一次引用 c SELECT c1.cust_id, c1.cust_name, SUM(o.order_amount) AS order_summary FROM c c1 LEFT JOIN orders o ON c1.cust_id = o.cust_id GROUP BY c1.cust_id, c1.cust_name) main

执行计划结论：CTE 结果集被强制物化到临时表中。

场景 5：Hint 直接写在普通子查询中 —— 无效

将/*+ MATERIALIZE */Hint 直接添加到非 WITH 子句的普通子查询中，无法触发物化行为。

执行计划结论：实验验证该方式无效，临时表物化未发生。

三、结论

1. /*+ MATERIALIZE */仅对WITH 子句内的 CTE生效，直接写在普通子查询中无物化效果；
2. WITH 子句中的 CTE 被多次引用时，Oracle 会自动物化结果到临时表；仅被单次引用时，默认不物化;
3. 即使 CTE 仅单次引用，也可通过在 WITH 子句的 CTE 查询中添加/*+ MATERIALIZE */Hint，强制将结果集物化到临时表，适用于优化器判断失误时，未将结果集物化到临时表的情况。
4. 需要复用子查询结果或优化执行效率的场景。