Oracle 如何避免函数在单个sql语句中被调用两次？

嗨

我们有merge语句，这些语句从不确定函数中获取其值。
某些更新合并并不是 “真实的”，因为实际值不会更改。
我们希望根本不进行这些更新，但不希望对这些功能进行两次评估。

我们如何以Oracle支持的方式完成此操作？
我们找到sofar的唯一方法是使用未记录的具体化提示。

这里是简单的测试用例。

create or replace package lhkoep as
    function lhkoe(p_a number, p_b varchar2, p_c date) return varchar2 ;
end;
/

create or replace package body lhkoep as
    function lhkoe(p_a number, p_b varchar2, p_c date) return varchar2 is
    begin
        dbms_lock.sleep(1);
        return p_a;
    end;
end;
/


select 
    lhkoep.lhkoe(x, 'A', TO_DATE('20190103','YYYYMMDD') )
from (
select
    level x
from 
    dual connect by level < 5
);
-- 4 seconds.
    
select 
    lhkoep.lhkoe(x,'A', TO_DATE('20190103','YYYYMMDD') )
  from 
(select 
     level x
from 
    dual connect by level < 5
)
where not ((lhkoep.lhkoe(x,'A', TO_DATE('20190103','YYYYMMDD') ) is null
       and X is null)
       or (lhkoep.lhkoe(X,'A', TO_DATE('20190103','YYYYMMDD') ) = X)
       );
-- 8 s

select 
    lhkoep.lhkoe(x,'A', TO_DATE('20190103','YYYYMMDD') )
  from 
(select 
     level x
from 
    dual connect by level < 5
)
where ((lhkoep.lhkoe(x,'A', TO_DATE('20190103','YYYYMMDD') ) is null
       and X is null)
       or (lhkoep.lhkoe(X,'A', TO_DATE('20190103','YYYYMMDD') ) = X)
       );
-- 12 s


     
select 
   new_v
from (  
     select 
         lhkoep.lhkoe(x,'A', TO_DATE('20190103','YYYYMMDD') )  new_v,
         x old_v
       from 
           (select 
                 level x
             from 
                  dual connect by level < 5
           )
     )
where ((new_v is null and old_v is null)
      or (new_v = old_v));
-- -- -- 12 s
with d as (
select --+ MATERIALIZE
         lhkoep.lhkoe(x,'A', TO_DATE('20190103','YYYYMMDD') )  new_v,
         x old_v
       from 
           (select 
                 level x
             from 
                  dual connect by level < 5
           )
)
select 
   new_v
from d
where ((new_v is null and old_v is null)
      or (new_v = old_v));
-- 4 s
复制

左侧

We have merge statements which get their values from non deterministic functions.

听起来... 危险的...

无论如何，只需将函数调用推入行生成器，您将回到4s执行时间:

set timing on

with d as (
  select new_v,
         x old_v
  from (
    select level x,
           lhkoep.lhkoe (level,'A',to_date ('20190103','YYYYMMDD')) new_v
    from   dual 
    connect by level < 5
  )
)
select new_v
from   d
where (
  ( new_v is null and old_v is null ) or 
  ( new_v = old_v )
);

NEW_V   
1       
2       
3       
4       

Elapsed: 00:00:04.571
复制