Different behaviour using Oracle 'with' than using a table

Question

Tried the following code:

--------------- Setup ------------
drop table suk_rc_t1;

create table suk_rc_t1 (x number, y number);

insert into suk_rc_t1(x) values(1); 

commit;

create or replace function suk_instn_id_wrap(
call_id pls_integer )
 return pls_integer as
begin
  dbms_output.put_line('suk_instn_id_wrap ' || call_id);
  --return suk_instn_id;
  return 123;
end;
/ 

--------------- How many RUNs of suk_instn_id_wrap in 2 queries below ? ------------

select 3 from suk_rc_t1
where (coalesce (y, suk_instn_id_wrap(1)) = suk_instn_id_wrap(2)
       or suk_instn_id_wrap(3) is null);    

begin
   dbms_output.put_line('Done');
end;
/
with suk_rc_t1 as (select 1 x, null y from dual)
select 3 from suk_rc_t1
where (coalesce (y, suk_instn_id_wrap(1)) = suk_instn_id_wrap(2)
       or suk_instn_id_wrap(3) is null);    
begin
   dbms_output.put_line('Done');
end;
/

I was expecting to get the same output. Instead I got:

suk_instn_id_wrap 3
suk_instn_id_wrap 1
suk_instn_id_wrap 2
Done

suk_instn_id_wrap 1
suk_instn_id_wrap 2
Done

Does anybody have an explanation for this behaviour?

`coalesce (y, suk_instn_id_wrap(1)) = suk_instn_id_wrap(2)` is true in both cases, no? The engine is neither required to nor prohibited from evaluating the other clause of the `OR` in this case, nor is the order of clauses semantically relevant. It is also not required to perform the same evaluation steps for a CTE as opposed to an actual table (that is, short circuit the same for both queries). [See also](https://stackoverflow.com/q/8900631/4137916). — Jeroen Mostert, Aug 20 '18 at 12:51
What version of the database are you running your query against? — Boneist, Aug 20 '18 at 13:28

score 1 · Accepted Answer · answered Aug 20 '18 at 14:10

It will be an optimizer thing, and although I can't say for certain I suspect the reasoning might be as follows:

In the first case, Oracle needs to read the database to access column y, which it would prefer not to do if it doesn't have to, so it prefers to evaluate suk_instn_id_wrap(3) first and avoid reading the database. Of course, it turns out to be false and so has to evaulate the first expression anyway. Bad luck.

In the second case, Oracle knows that y is null, so in that case neither side of the OR condition costs any more in terms of database access. In this case, perhaps it defaults to the original order of the expressions. You might think that the second condition would be better since there is only one function call, but perhaps that is not considered.

The predicates sections of the execution plans bear this out: `filter(("DEMO_FN"(3) IS NULL OR COALESCE("Y","DEMO_FN"(1))="DEMO_FN"(2)))` for the physical table (three calls), and `filter((COALESCE(NULL,"DEMO_FN"(1))="DEMO_FN"(2) OR "DEMO_FN"(3) IS NULL))` for the WITH version (two calls). — William Robertson, Aug 20 '18 at 15:29

Different behaviour using Oracle 'with' than using a table

1 Answers1