Oracle数据库中含自定义函数的子查询优化问询

问题背景 #

我在Oracle数据库中写查询时，调用了一个资源消耗很高的自定义函数add_bus_days(sysdate,1)——这个函数会基于内部日历计算未来1个工作日的日期，导致查询跑得特别慢。因为这个函数的返回值对表中所有行都一样（sysdate是固定值），我试着把函数放到子查询里做CROSS JOIN，代码如下：

SELECT a.id, a.some_date, b.next_business_day 
FROM a
CROSS JOIN (SELECT add_bus_days(sysdate,1) as next_business_day FROM dual) b

但性能一点没变好。后来改成LEFT JOIN加ON a.some_date >= sysdate的条件，查询速度立刻上去了，但这个条件不符合业务要求，必须换回CROSS JOIN的写法。

我想知道：有没有办法强制这个子查询只执行一次，而不是对每一行都跑一遍函数？

补充信息：查询语句与执行计划 #

当前查询语句 #

with nbd as (select ccijc.add_bus_days(sysdate,1) as next_bus_day from dual)

select a.status_date, b.next_bus_day
from ccijc.episodes a
cross join nbd b
order by 2 desc

执行计划结果 #

"STATEMENT_ID","PLAN_ID","TIMESTAMP","REMARKS","OPERATION","OPTIONS","OBJECT_NODE","OBJECT_OWNER","OBJECT_NAME","OBJECT_ALIAS","OBJECT_INSTANCE","OBJECT_TYPE","OPTIMIZER","SEARCH_COLUMNS","ID","PARENT_ID","DEPTH","POSITION","COST","CARDINALITY","BYTES","OTHER_TAG","PARTITION_START","PARTITION_STOP","PARTITION_ID","OTHER","OTHER_XML","DISTRIBUTION","CPU_COST","IO_COST","TEMP_SPACE","ACCESS_PREDICATES","FILTER_PREDICATES","PROJECTION","TIME","QBLOCK_NAME"
"",2134,26-APR-23,"","SELECT STATEMENT","","","","","","","","ALL_ROWS",,0,,0,3409,3409,828767,6630136,"","","","","","","",913862273,3187,,,"","",1,""
"",2134,26-APR-23,"","SORT","ORDER BY","","","","","","","",,1,0,1,1,3409,828767,6630136,"","","","","","<other_xml><info type=""has_user_tab"">yes</info><info type=""db_version"">19.0.0.0</info><info type=""parse_schema""><![CDATA[""CCI_RESEARCH""]]></info><info type=""plan_hash_full"">2549094711</info><info type=""plan_hash"">2575214890</info><info type=""plan_hash_2"">2549094711</info><stats type=""compilation""><stat name=""bg"">0</stat></stats><qb_registry><q o=""18"" h=""y""><n><![CDATA[SEL$F5BB74E1]]></n><p><![CDATA[SEL$1]]></p><i><o><t>VW</t><v><![CDATA[SEL$2]]></v></o></i><f><h><t><![CDATA[A]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$2]]></s></h></f></q><q o=""2""><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[A]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[B]]></t><s><![CDATA[SEL$1]]></s></h></f></q><q o=""2""><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$2]]></s></h></f></q><q o=""2""><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[from$_subquery$_004]]></t><s><![CDATA[SEL$3]]></s></h></f></q><q o=""18"" f=""y"" h=""y""><n><![CDATA[SEL$117FC0EF]]></n><p><![CDATA[SEL$3]]></p><i><o><t>VW</t><v><![CDATA[SEL$F5BB74E1]]></v></o></i><f><h><t><![CDATA[A]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[DUAL]]></t><s><![CDATA[SEL$2]]></s></h></f></q></qb_registry><outline_data><hint><![CDATA[USE_NL(@"SEL$117FC0EF" "A"@"SEL$1")]]></hint><hint><![CDATA[LEADING(@"SEL$117FC0EF" "DUAL"@"SEL$2" "A"@"SEL$1")]]></hint><hint><![CDATA[INDEX_FFS(@"SEL$117FC0EF" "A"@"SEL$1" ("EPISODES"."STATUS_DATE" "EPISODES"."DELETE_STATUS" "EPISODES"."EPISODE_ID" "EPISODES"."CREATE_DATE"))]]></hint><hint><![CDATA[OUTLINE(@"SEL$2")]]></hint><hint><![CDATA[OUTLINE(@"SEL$1")]]></hint><hint><![CDATA[MERGE(@"SEL$2" >"SEL$1")]]></hint><hint><![CDATA[OUTLINE(@"SEL$F5BB74E1")]]></hint><hint><![CDATA[OUTLINE(@"SEL$3")]]></hint><hint><![CDATA[MERGE(@"SEL$F5BB74E1" >"SEL$3")]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$117FC0EF")]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDATA[OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')]]></hint><hint><![CDATA[OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')]]></hint><hint><![CDATA[OPT_PARAM('_px_adaptive_dist_method' 'off')]]></hint><hint><![CDATA[DB_VERSION('19.1.0')]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('19.1.0')]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint></outline_data></other_xml>"","",913862273,3187,13329000,"","","(#keys=1) ""CCIJC"".""ADD_BUS_DAYS""(SYSDATE@!,1)[7], ""A"".""STATUS_DATE""[DATE,7]",1,"SEL$117FC0EF"
"",2134,26-APR-23,"","NESTED LOOPS","","","","","","","","",,2,1,2,1,676,828767,6630136,"","","","","","","",128144472,645,,,"","","(#keys=0) ""A"".""STATUS_DATE""[DATE,7]",1,""
"",2134,26-APR-23,"","FAST DUAL","","","","","""DUAL""@""SEL$2""","","","",,3,2,3,1,2,1,,"","","","","","","",7271,2,,,"","","",1,"SEL$117FC0EF"
"",2134,26-APR-23,"","INDEX","FAST FULL SCAN","","CCIJC","EPISODES_IDX_006","""A""@""SEL$1""","","INDEX","ANALYZED",,4,2,3,2,674,828767,6630136,"","","","","","","",128137200,643,,,"","","""A"".""STATUS_DATE""[DATE,7]",1,"SEL$117FC0EF"

可行解决方案 #

从执行计划看，优化器已经用了NESTED LOOPS，先执行FAST DUAL获取函数结果（理论上只执行一次）再关联主表，但性能还是不行。可以试试下面几种方法：

1. 用/*+ MATERIALIZE */提示强制子查询物化 #

给CTAS子查询加物化提示，让Oracle提前计算并缓存函数结果，避免重复执行：

with nbd as (select /*+ MATERIALIZE */ ccijc.add_bus_days(sysdate,1) as next_bus_day from dual)
select a.status_date, b.next_bus_day
from ccijc.episodes a
cross join nbd b
order by 2 desc

2. 提前计算函数结果（PL/SQL方式） #

如果是在PL/SQL块里执行查询，可以先算出函数结果，再带入主查询：

DECLARE
    v_next_bus_day DATE;
BEGIN
    SELECT ccijc.add_bus_days(sysdate,1) INTO v_next_bus_day FROM dual;
    FOR rec IN (
        SELECT a.status_date, v_next_bus_day AS next_bus_day
        FROM ccijc.episodes a
        ORDER BY 2 desc
    ) LOOP
        -- 这里可以处理查询结果，比如输出或插入其他表
        DBMS_OUTPUT.PUT_LINE(rec.status_date || ' | ' || rec.next_bus_day);
    END LOOP;
END;
/

3. 用LEFT JOIN加恒真条件替代CROSS JOIN #

用ON 1=1的恒真条件模拟CROSS JOIN，同时让优化器只计算一次函数：

SELECT a.status_date, b.next_bus_day
FROM ccijc.episodes a
LEFT JOIN (SELECT ccijc.add_bus_days(sysdate,1) as next_bus_day FROM dual) b ON 1=1
ORDER BY 2 desc

4. 优化自定义函数本身 #

如果上面的方法都没效果，建议直接优化add_bus_days函数：

• 确保函数用了高效的日历表查询逻辑，避免逐行遍历
• 给函数添加DETERMINISTIC关键字，标记为确定性函数，让Oracle可以缓存结果
• 检查函数内部有没有冗余的IO或计算步骤

另外从执行计划的PROJECTION部分能看到，ADD_BUS_DAYS被标记为#keys=1，理论上只会计算一次，但如果实际还是重复执行，可能是函数的DETERMINISTIC属性没设置对，或者优化器选了错误的执行路径，这时候加MATERIALIZE提示通常能解决问题。

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