Hi
We have merge statements which get their values from non deterministic functions.
Some of the updates merge does are not 'real' in a sence that actual values do not change.
We would prefer so that these updates are not made at all but do not want those functions to be evaluated twice .
How can we accomplish this in an Oracle supported way ?
Only way we have found sofar is by using undocumented materialize hint.
Here is simple test case for this.
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
lh
We have merge statements which get their values from non deterministic functions.That sounds... dangerous...
Anyway, just push the function call into your row generator and you'll be back to 4s execution times:
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