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_consume50000)c1LEFTJOINorders oONc1.cust_ido.cust_idGROUPBYc1.cust_id,c1.cust_name)mainCROSSJOIN(SELECTCOUNT(*)ASvip_countFROM(SELECTcust_id,cust_name,cust_levelFROMcustomersWHEREtotal_consume50000)c2WHEREc2.cust_levelVIP)sub1执行计划结论预估执行计划中未使用临时表空间子查询被重复执行。在这里插入图片描述场景 2重复引用 WITH 子句中的 CTE—— 触发物化将重复执行的子查询封装到 WITH 子句中多次引用该 CTE 时Oracle 会自动将 CTE 结果物化到临时表。WITHcAS(SELECTcust_id,cust_name,cust_levelFROMcustomersWHEREtotal_consume50000)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_ido.cust_idGROUPBYc1.cust_id,c1.cust_name)mainCROSSJOIN(-- 第二次引用 cSELECTCOUNT(*)ASvip_countFROMc c2WHEREc2.cust_levelVIP)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 将结果集物化到临时表。测试 SQLsqlWITH 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 结果集被强制物化到临时表中。场景 5Hint 直接写在普通子查询中 —— 无效将/* MATERIALIZE */Hint 直接添加到非 WITH 子句的普通子查询中无法触发物化行为。执行计划结论实验验证该方式无效临时表物化未发生。三、结论/* MATERIALIZE */仅对WITH 子句内的 CTE生效直接写在普通子查询中无物化效果WITH 子句中的 CTE 被多次引用时Oracle 会自动物化结果到临时表仅被单次引用时默认不物化;即使 CTE 仅单次引用也可通过在 WITH 子句的 CTE 查询中添加/* MATERIALIZE */Hint强制将结果集物化到临时表适用于优化器判断失误时未将结果集物化到临时表的情况。需要复用子查询结果或优化执行效率的场景。