CODEWITHSUNDEEP
sqloracle-databasecommon-table-expression
Ed Heal
Ed Heal

Reputation: 60037

Different behaviour using Oracle 'with' than using a table

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?

Upvotes: 3

Views: 81

Answers (1)

rghome
rghome

Reputation: 8841

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.

Upvotes: 1

Related Questions