Oracle查询优化：子查询限制函数过滤未生效，如何强制执行

问题背景 #

我有一个包含多表连接与过滤逻辑的Oracle复杂查询，该查询仅返回0行或1行数据，现需对其进行性能优化。经排查，查询性能差的主要原因是WHERE子句中调用了IS_VALID函数。

简化后的原查询 #

select id, col1, col2, IS_VALID(id) as VALIDITY_CHECK
FROM table1,
-- 多表连接逻辑...
WHERE IS_VALID(id) = 'TRUE'
-- AND 其他过滤条件....

注释掉WHERE子句中的IS_VALID调用后，性能大幅提升。我理解其原因：WHERE子句中的函数需对表中每一行调用，而SELECT子句中的函数仅对结果集调用（最多1次）。

为优化性能，我尝试将查询放入FROM子查询中，子查询的WHERE仅保留其他过滤条件，SELECT中包含IS_VALID(id)作为VALIDITY_CHECK，再在父查询中过滤VALIDITY_CHECK='TRUE'，改写后的查询如下：

select * FROM (
  select id, col1, col2, IS_VALID(id) as VALIDITY_CHECK
  FROM table1,
  -- 多表连接逻辑...
  WHERE -- 其他过滤条件...
)
WHERE VALIDITY_CHECK='TRUE'

但该方案未生效，执行计划哈希值与原查询完全相同，说明Oracle优化器将查询合并回了原逻辑。请问我是否存在理解误区？如何让Oracle先执行子查询再进行过滤？

解答 #

你的理解误区 #

你对函数调用时机的理解是对的，但忽略了Oracle优化器的查询重写能力——优化器会自动将子查询中的过滤逻辑与外层查询合并，以追求最优执行计划，这就导致你的改写并没有改变实际执行逻辑，函数依然会在过滤阶段被调用。

让Oracle先执行子查询的方法 #

• 使用/*+ NO_MERGE */提示强制不合并子查询
  在子查询中添加优化器提示，阻止优化器将子查询与外层查询合并，这样就能保证子查询先完成多表连接和其他过滤（得到最多1行结果），再调用IS_VALID并在外层过滤：

  select * FROM (
    select /*+ NO_MERGE */ id, col1, col2, IS_VALID(id) as VALIDITY_CHECK
    FROM table1,
    -- 多表连接逻辑...
    WHERE -- 其他过滤条件...
  )
  WHERE VALIDITY_CHECK='TRUE'
  

• 将子查询结果存入临时表（适合频繁执行的场景）
  如果该查询需要频繁执行，可以先将子查询的结果（最多1行）存入临时表，再对临时表进行函数调用和过滤：

  CREATE GLOBAL TEMPORARY TABLE temp_result (
    id NUMBER,
    col1 VARCHAR2(100),
    col2 VARCHAR2(100),
    VALIDITY_CHECK VARCHAR2(5)
  ) ON COMMIT DELETE ROWS;
  
  INSERT INTO temp_result
  select id, col1, col2, IS_VALID(id) as VALIDITY_CHECK
  FROM table1,
  -- 多表连接逻辑...
  WHERE -- 其他过滤条件...;
  
  select * from temp_result where VALIDITY_CHECK='TRUE';
  

• 改写为WITH子句并添加/*+ MATERIALIZE */提示
  使用WITH子句定义中间结果集，并添加MATERIALIZE提示强制Oracle将中间结果物化（相当于临时表），避免优化器合并逻辑：

  WITH temp_data AS (
    select /*+ MATERIALIZE */ id, col1, col2
    FROM table1,
    -- 多表连接逻辑...
    WHERE -- 其他过滤条件...
  )
  select id, col1, col2, IS_VALID(id) as VALIDITY_CHECK
  from temp_data
  where IS_VALID(id)='TRUE';
  

  这里注意，因为temp_data最多1行，所以即使在WHERE和SELECT中都调用函数，也只会执行2次，性能损失可以忽略。

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