如何强制Oracle SQL引擎先加载子查询结果到内存再过滤以提速？

问题本质 #

Oracle优化器默认会尝试合并CTE或子查询逻辑，导致原本应先通过SHIPMENT_STATE = 'Preliminary'过滤出100条数据，再调用函数并筛选的流程被打乱——反而可能让函数被大量重复调用，甚至触发全表扫描，最终拖慢执行速度。

可行解决方案 #

1. 强制CTE物化（最简便方法）

给CTE添加/*+ MATERIALIZE */提示，强制Oracle先执行CTE内的查询，生成临时结果集后再进行过滤：

with cte as (
  select /*+ MATERIALIZE */
    shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) Shipment_flow
  from shipment_line_ovw
  where SHIPMENT_STATE = 'Preliminary' 
)
select * from cte
where Shipment_flow = 'Report picking, Print pick list';

2. 使用临时表中转

先将过滤后的小结果集存入临时表，再基于临时表做后续筛选：

-- 创建临时表（字段类型需与原表、函数返回值匹配）
create global temporary table temp_shipments (
  shipment_id varchar2(50), -- 替换为实际字段类型
  shipment_flow varchar2(100) -- 替换为函数返回值类型
) on commit delete rows;

-- 插入基础过滤数据并计算函数值
insert into temp_shipments
select shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID)
from shipment_line_ovw
where SHIPMENT_STATE = 'Preliminary';

-- 从临时表筛选目标结果
select * from temp_shipments
where shipment_flow = 'Report picking, Print pick list';

3. 用ROWNUM强制子查询优先执行

通过ROWNUM的特性，让Oracle优先执行内层子查询生成小结果集，再进行过滤：

select * from (
  select 
    shipment_id, Shipment_Flow_API.Get_Next_Step(SHIPMENT_ID) Shipment_flow
  from shipment_line_ovw
  where SHIPMENT_STATE = 'Preliminary' 
  and rownum <= 999999 -- 设置足够大的数值，覆盖所有符合条件的记录
)
where Shipment_flow = 'Report picking, Print pick list';

4. 优化函数本身（若有权限修改）

如果Shipment_Flow_API.Get_Next_Step是确定性函数（相同输入永远返回相同输出），给函数添加DETERMINISTIC关键字，让Oracle缓存函数结果，避免重复调用：

create or replace function Shipment_Flow_API.Get_Next_Step(p_shipment_id in [输入类型])
return [返回类型] deterministic is
-- 函数原有逻辑
begin
  -- ...
end;

内容的提问来源于stack exchange，提问作者Brandon Frenchak