Thanks for the question, Jon.

Asked: March 11, 2003 - 10:41 pm UTC

Last updated: January 24, 2018 - 12:17 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Does Oracle perform short-circuit boolean evaluation when evaluating expressions?

For example, consider the following part of a query:

select lcs_id, loan_id,
client_id, security_id, masterfund_id,
category, calc_lvr, price,
greatest(0, decode(value_flag,1,
decode(category,
******> 'D',(select least(mtmv,sum(d.hold_amount))
from dds_loan d
where d.lcs_id = lcs_id),
mtmv+unconfirmed_mtmv),
decode(category,
'O',units_available*price+unconfirmed_mtmv+
(nvl(units_reserved,0)*nvl(option_contract_size,0)*
least(strike_price,price)),
units_available*price+unconfirmed_mtmv))) market_value,
sort_order,
min(sort_order) over(partition by lcs_id) min_sort_order
from (
select lcs.lcs_id, lvrt.sort_order,
lc.loan_id, lc.client_id,
sm.security_id, sm.masterfund_id,
case when lvr.aged_lvr_date >= sysdate then lvr.aged_lvr
when lvr.lvr_type_id = 1 and lvr.aged_lvr is not null
then lvr.aged_lvr
when lvr.single_stock_lvr is not null
******> and (select count(*)
from loan_client_security vlcs, loan_client vlc
where vlcs.loan_client_id = vlc.loan_client_id
and vlc.loan_id = lc.loan_id) = 1
then single_stock_lvr
else lvr.default_lvr
end calc_lvr,
...

In the case statement makred *****> I have put the lvr.single_stock_lvr is not null condition before the inline subquery expression because I know it is false most of the time. So will Oracle only evaluate the subquery when the first condition is true, or will it be evaluated for every row? Same question for the decode also marked ******>.

What about in the where clause, if there is two or more columns involved in a join between two tables:

select *
from users u1, users u2
where u1.first_name = u2.first_name
and u1.last_name = u2.last_name

Will the join "fail" after the one of the predicates evaluate to false, or does that depend on the joining logic being used (nested loop, hash join etc...)

Thanks
Jon

and Tom said...

yes, decode is performed left to right and only the FIRST true condition has its expression evaluated.

Now, for the PREDICATE -- it (the optimizer) will reorder the predicate as it sees fit. The order of evaluation of the predicate is totally up to IT. But by using CASE, you are again forcing its hand -- having something only done if and when you want it.

However -- you have to watch that. You are forcing it to now do that select count(*) as a single row sort of thing, exactly like a correlated subquery. It could be that if it were to turn it into an outer join for example and do it in bulk it could be faster. So, watch out -- you can be too smart for your own good sometimes ;)

In the where clause ALL BETS are off - it'll short circut the evaluation but you have no idea what order the predicate will happen in .

Rating

(36 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

DECODE evaluates the lot ???

Adrian Billington, March 13, 2003 - 4:08 am UTC

Tom

You mention that "decode is performed left to right and only the FIRST true condition has its expression evaluated.".

So why does the following happen?

816> create sequence seq;

Sequence created.

816>
816> select decode(1,1,1,seq.nextval)
2 from all_objects
3 where rownum <= 10;

DECODE(1,1,1,SEQ.NEXTVAL)
-------------------------
1
1
1
1
1
1
1
1
1
1

10 rows selected.

816>
816> select seq.currval from dual;

CURRVAL
----------
10

Looks to me like the whole lot is being evaluated.

Regards

Adrian

March 14, 2003 - 5:04 pm UTC

No, it goes to the internals as to why Seq.nextval is done that way - but a sequence is a horse of a different color.  It is "special".  Using a function, you can observe the behavioral differences.  The sequence is actually replaced in there with the value as the rowsource is evaluated.  The function(s) are evaluated as they are encountered:



ops$tkyte@ORA817DEV> create or replace function f return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')+1 );
  5          return 0;
  6  end;
  7  /

Function created.

ops$tkyte@ORA817DEV> drop sequence s;

Sequence dropped.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> exec dbms_application_info.set_client_info( 0 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> select decode(1,1,1,f)
  2   from all_objects
  3   where rownum <= 10;

DECODE(1,1,1,F)
---------------
              1
              1
              1
              1
              1
              1
              1
              1
              1
              1

10 rows selected.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select userenv('client_info' ) from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
0

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select f
  2   from all_objects
  3   where rownum <= 10;

         F
----------
         0
         0
         0
         0
         0
         0
         0
         0
         0
         0

10 rows selected.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> select userenv('client_info' ) from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
10

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create sequence s;

Sequence created.

ops$tkyte@ORA817DEV> select s.nextval from dual;

   NEXTVAL
----------
         1

ops$tkyte@ORA817DEV> select decode(1,1,1,s.nextval)
  2   from all_objects
  3   where rownum <= 10;

DECODE(1,1,1,S.NEXTVAL)
-----------------------
                      1
                      1
                      1
                      1
                      1
                      1
                      1
                      1
                      1
                      1

10 rows selected.

ops$tkyte@ORA817DEV> select s.nextval from dual;

   NEXTVAL
----------
        12

Evaluation of expressions

Ranga Chakravarthi, March 13, 2003 - 4:30 pm UTC

If you wrap the selection of the sequence in a function,
it doesn't get executed. However, this does not answer
Adrian's question

8.1.6>create sequence seq;

Sequence created.

8.1.6>
8.1.6>create function foo
2 return number
3 is
4 dummy number;
5 begin
6 select seq.nextval
7 into dummy
8 from dual;
9 return dummy;
10 end;
11 /

Function created.

8.1.6>
8.1.6>select decode(1,1,1,foo)
2 from all_objects
3 where rownum <= 10;

DECODE(1,1,1,FOO)
-----------------
1
1
1
1
1
1
1
1
1
1

10 rows selected.

8.1.6>
8.1.6>select seq.nextval from dual;

NEXTVAL
---------
1

8.1.6>

March 14, 2003 - 5:49 pm UTC

see above.

A reader, June 02, 2004 - 1:49 pm UTC

Using a function, you can observe the behavioral differences. The sequence is actually replaced
in there with the value as the rowsource is evaluated. The function(s) are
evaluated as they are encountered:

Your example proved that if the selection from the sequence is wrapped in stored code, it stays untouched. But if it is left as is in the decode(1,1,1,s.nextval), it IS evaluated.

You seem to contradict yourself. If the DECODE() call above internally replaces the reference to the s.nextval with a call to a on-the-fly function, it should NOT bump up the sequence, as you demonstrated. So, why DOES it get bumped up?

Also, you said "functions are evaluated as they are encountered". But the previous reviewer showed that
decode(1,1,1,foo) where FOO selects from a sequence does NOT result in FOO being executed. Is this because it is "not encountered" since the DECODE short-circuits it out?

Thanks

June 02, 2004 - 2:11 pm UTC

sequences are different, special, internal, not functions.

the function foo was "not encountered" in taht example -- decode short circuts (but other functions like NVL() do not short circut)

NVL

A reader, June 02, 2004 - 2:23 pm UTC

I have never understood why NVL() doesnt short circuit. I mean, if I have something like

NVL(1,complicated_function)

Why would Oracle even bother to execute the complicated function since it is clearly not necessary?

Yes, I could code this as

decode(1,1,1,complicated_function)

but if I can do it, why cant Oracle internally re-write the former with the latter?

Short circuiting queries

A reader, April 28, 2005 - 10:31 am UTC

Hi Tom,

I am writing a SQL statement which will execute either one
of two inline selects, dependent on whether the first inline
select returns a row, however, in order to take advantage of short circuiting techniques, I have to use DECODE and run the same query twice, i.e.

SELECT
DECODE((SELECT 1 FROM t),
1,
(SELECT 1 FROM t),
(SELECT 2 FROM t))
FROM dual
/

Is there any function which will do the short-circuiting AND effectively do the NVL, i've tried NVL, COALESCE and NVL2, without having to execute the same query twice?

Thanks in advance

April 28, 2005 - 1:50 pm UTC

try an inline view perhaps:

ops$tkyte@ORA9IR2> create or replace function f( p_num in number ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')+1 );
  5          return p_num;
  6  end;
  7  /
 
Function created.
 
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 0 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select decode( f1, null, f(2), f1 )
  2  from ( select (select f(1) from dual) f1 from dual );
 
DECODE(F1,NULL,F(2),F1)
-----------------------
                      1
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1

<b>the scalar subquery was called once in that case, the decode looked at it, said "not null" and returned the value</b>

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info( 0 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select decode( f1, null, f(2), f1 )
  2  from ( select (select f(null) from dual) f1 from dual );
 
DECODE(F1,NULL,F(2),F1)
-----------------------
                      2
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
2

what is the difference between

A reader, April 28, 2005 - 3:25 pm UTC

select decode( f1, null, f(2), f1 )
from ( select (select f(null) from dual) f1 from dual );

and

select decode( f1, null, f(2), f1 )
from ( select f(null) f1 from dual );

?

April 28, 2005 - 4:20 pm UTC

scalar subquery caching :)

try it and see how many times f is called in each case

Subquery caching

Jay, April 28, 2005 - 5:47 pm UTC

Hi Tom,

In response to your reply above:

Subquery caching cannot be demonstrated by the SQLs:

select decode( f1, null, f(2), f1 )
from ( select (select f(null) from dual) f1 from dual );

and

select decode( f1, null, f(2), f1 )
from ( select f(null) f1 from dual );

since in both the cases f(2) would need to be executed which is different from f1.

April 28, 2005 - 6:39 pm UTC

yes but when f() in the scalar subquery doesn't return null it would not.

that was my point -- ran it twice to show what happens when the scalar subquery returned a value vs not.

Subquery caching

Jay, April 28, 2005 - 5:58 pm UTC

In continuation of the above:

SQL> exec dbms_application_info.set_client_info( 0 );

PL/SQL procedure successfully completed.

SQL> select decode( f1, null, f(2), f1 )
  2  from ( select (select f(1) from dual) f1 from dual );

DECODE(F1,NULL,F(2),F1)
-----------------------
                      1

SQL> exec dbms_application_info.set_client_info( 0 );

PL/SQL procedure successfully completed.

SQL> select decode( f1, null, f(2), f1 )
  2  from ( select f(1) f1 from dual );

DECODE(F1,NULL,F(2),F1)
-----------------------
                      1

This seems to indicate (from the last select above) that the inline view is evaluated only once. So the first select above may not be demonstrating the scalar subquery caching feature

April 28, 2005 - 6:42 pm UTC

the original question was with regards to scalar subqueries -- i needed to have them there to show that working with them.  


i should have:

ops$tkyte@ORA9IR2> select decode( f1, null, (SELECT f(2) FROM DUAL), f1 )
  2  from ( select (select f(null) from dual) f1 from dual );

in hindsight

scalar subquery caching

Jay, April 28, 2005 - 8:50 pm UTC

I maybe missing the point here, but how does this query

select decode( f1, null, (SELECT f(2) FROM DUAL), f1 )
from ( select (select f(null) from dual) f1 from dual );

demonstrate the scalar subquery caching feature ? In the query above, f1 evaluates to null and hence:

(select f(null) from dual) -- evaluated once
(SELECT f(2) FROM DUAL) -- evaluated once and returned

April 28, 2005 - 8:56 pm UTC

select decode( f1, null, (SELECT f(2) FROM DUAL), f1 )
from ( select (select f(null) from dual) f1 from dual );

is the same as

select decode( (select f(null) from dual), null, (SELECT f(2) FROM DUAL), (select f(null) from dual) )
from ( select (select f(null) from dual) f1 from dual );

scalar subquery caching

Jay, April 28, 2005 - 9:20 pm UTC

Even in this query

select decode( (select f(null) from dual), null, (SELECT f(2) FROM DUAL),
(select f(null) from dual) )
from ( select (select f(null) from dual) f1 from dual );

the subquery (select f(null) from dual) maybe evaluated only once due to shortcircuiting and hence no visible demonstration of subquery caching. Maybe I am still missing the point here!

April 28, 2005 - 9:32 pm UTC

it could be, it doesn't have to be. even a /*+ NO_MERGE */ hint might be called for.

to be sure ...

A reader, April 29, 2005 - 4:24 am UTC

Still don't see any difference between

select decode( f1, null, f(2), f1 )
from ( select (select f(null) from dual) f1 from dual );

and

select decode( f1, null, f(2), f1 )
from ( select f(null) f1 from dual );

Subquery caching in both cases !!

Couldn't reproduce any case in which the 1st one is better
than the second one.

What do you think of

select decode( f1, null, f(2), f1 )
from ( select (select (select f(null) from dual) from dual ) f1 from dual);

just to be sure subquery caching is applied ?

April 29, 2005 - 8:22 am UTC

THE ORIGINAL POSTER WAS WANTING SCALAR SUBQUERIES.

*i* added a function so we can observe how OFTEN they would get called.

Their goal: call the scalar subquery ONCE at most per row. using

decode( (scalar subquery), null, (scalar subquery2), (scalar subquery) )

would run (scalar subquery) TWICE. I put the function in there to show how we can make that once.

that is all.

scalar subquery caching

Jay, April 29, 2005 - 11:17 am UTC

Even in this expression

decode( (scalar subquery), null, (scalar subquery2), (scalar subquery) )

scalar subquery caching comes into play only if '(scalar subquery)' evaluates to not null (assuming shortcircuiting happens in decode). All the examples we have been discussing results in '(scalar subquery)' evaluating to null (i.e. f1 is null) and hence '(scalar subquery)' is never even attempted for evaluation the second time

April 29, 2005 - 11:22 am UTC

no they weren't (all example were not)

ops$tkyte@ORA9IR2> select decode( f1, null, f(2), f1 )
  2  from ( select (select f(1) from dual) f1 from dual );
 
DECODE(F1,NULL,F(2),F1)
-----------------------
                      1

I showed a NULL return and a NOT NULL return.  That was my point.

Further on this

A reader, May 04, 2005 - 9:22 am UTC

Hi Tom,

I've been playing with the approach you specified above to great success, however, I recently hit a situation where we had functions, f1 and f2, which were autonomous transactions within the SELECT (presumably) for logging, and noticed that there were more rows logged than I would have expected. 

I've boiled it down to a simple test case :

SQL> CREATE TABLE t ( a VARCHAR2(10) );
Table created.

SQL> CREATE OR REPLACE FUNCTION f1
2 RETURN VARCHAR2
3 AS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT INTO t VALUES ( 'f1' );
7 COMMIT;
8 RETURN 'f1';
9 END f1;
10 /
Function created.

SQL> CREATE OR REPLACE FUNCTION f2
2 RETURN VARCHAR2
3 AS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT INTO t VALUES ( 'f2' );
7 COMMIT;
8 RETURN 'f2';
9 END f2;
10 /
Function created.

Now, run the following query, which returns one row, and processes one row.
SQL> SELECT
2 DECODE(x.out, 'f1', x.out, f2) output
3 FROM ( SELECT f1 out FROM dual WHERE rownum = 1 )
x;
OUTPUT
-------------------------------------------------------------------
f1
1 row selected.

When you query the output table, however, you get 3 rows!
SQL> SELECT * FROM t;
A
----------
f1
f1
f1
3 rows selected.

Obviously, we'd only expect one. Modifying the functions to use DBMS_APPLICATION_INFO or DBMS_AQ.ENQUEUE (i.e. non-autonomous transactions
where can see some "output"), works as expected (i.e. one call).

Any ideas, or is this a fundamental flaw in my understanding of this w.r.t. autonomous transactions?

Thanks in advance

May 04, 2005 - 10:13 am UTC

You cannot control the number of times a function is called from SQL at all.  

dbms_application_info is not getting called once -- it just is showing you the last value.

nothing to do with atrans, everything to do with the fact that SQL is non-procedural and we can limit sometimes the number of times it is called but we cannot control it.


Using a scalar subquery however, we can in this case further reduce it:

ops$tkyte@ORA9IR2> create or replace function f1 return varchar2
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')||'f1 ');
  5          return 'f1';
  6  end;
  7  /
 
Function created.
 
ops$tkyte@ORA9IR2> create or replace function f2 return varchar2
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')||'f2 ' );
  5          return 'f2';
  6  end;
  7  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info('');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> SELECT DECODE(x.out, 'f1', x.out, f2) output
  2    FROM ( SELECT f1 out FROM dual WHERE rownum = 1 ) x;
 
OUTPUT
-------------------------------------------------------------------------------
f1
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
f1 f1 f1
 
<b>shows it was still called 3 times....</b>


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec dbms_application_info.set_client_info('');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> SELECT DECODE(x.out, 'f1', x.out, f2) output
  2    FROM ( SELECT (select f1 from dual) out FROM dual WHERE rownum = 1 ) x;
 
OUTPUT
-------------------------------------------------------------------------------
f1
 
ops$tkyte@ORA9IR2> select userenv('client_info') from dual;
 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
f1

<b>but now only once</b>

Further info...

A reader, May 04, 2005 - 9:29 am UTC

I seem to have stumbled across the problem which leads me to believe this is an Oracle bug, if you omit the rownum = 1 clause (presumably allowing query merging now), it works ok :

SQL> TRUNCATE TABLE t;

Table truncated.

SQL> SELECT * FROM ( SELECT f1 FROM dual );

F1
-------------------------------------------------------------------
f1

1 row selected.

SQL> SELECT * FROM t;

A
----------
f1

1 row selected.

SQL> TRUNCATE TABLE t;

Table truncated.

SQL> SELECT * FROM ( SELECT f1 FROM dual WHERE rownum = 1 );

F1
-------------------------------------------------------------------
f1

1 row selected.

SQL> SELECT * FROM t;

A
----------
f1
f1
f1

3 rows selected.

May 04, 2005 - 10:14 am UTC

not a bug, sql....

Brain's just died!

A reader, May 04, 2005 - 10:26 am UTC

Hi Tom,

Well, I have to admit, this one's got me (including your explanation).

How does Oracle execute a single referenced function three times on a table with only one row?

Sorry, if i'm being really dense here, but this just doesn't make sense to me.

May 04, 2005 - 10:54 am UTC

same reason it used to query the segment header 3 or 4 times during a full scan (once would have done it)...

Jonathan Lewis has a better explanation than I -- but it is a "warm up" thing that happens.

run this:

create or replace function f1 return varchar2
as
l_num1 number := substr( userenv('client_info'), 1,
instr( userenv('client_info'),',')-1 );
l_num2 number := substr( userenv('client_info'),
instr( userenv('client_info'),',')+1 );
begin
dbms_application_info.set_client_info( (l_num1+1)||','||l_num2 );
return 'f1';
end;
/
create or replace function f2 return varchar2
as
l_num1 number := substr( userenv('client_info'), 1,
instr( userenv('client_info'),',')-1 );
l_num2 number := substr( userenv('client_info'),
instr( userenv('client_info'),',')+1 );
begin
dbms_application_info.set_client_info( l_num1||','||(l_num2+1) );
return 'f2';
end;
/
exec dbms_application_info.set_client_info('0,0');
SELECT DECODE(x.out, 'f1', x.out, f2) output
FROM ( SELECT f1 out FROM all_users WHERE rownum >= 1 ) x;
select userenv('client_info') from dual;

exec dbms_application_info.set_client_info('0,0');
SELECT DECODE(x.out, 'f1', x.out, f2) output
FROM ( SELECT f1 out FROM all_users WHERE rownum < 20 ) x;
select userenv('client_info') from dual;

exec dbms_application_info.set_client_info('0,0');
SELECT DECODE(x.out, 'f1', x.out, f2) output
FROM ( SELECT (select f1 from dual) out FROM all_users WHERE rownum < 20 ) x;
select userenv('client_info') from dual;

and you'll see that it has a fixed overhead on a multi-row result and that the scalar subquery caching that kicks in "helps" alot

Very interesting...

A reader, May 04, 2005 - 6:11 pm UTC

Aahhh... I see. It's like a pre-caching thing, in a way. I understand now, that's definitely worth remembering for the future.

Thanks for your help *again*.

Why is this NOT a one-hit?

A reader, June 02, 2005 - 6:56 am UTC

QRY1
select decode( (select f(1) from dual), null, f(2), (select f(1) from dual))
from ( select (select f(1) from dual) f1 from dual );

client_info reads out 2

But for

QRY2
select decode( f1, null, f(2), f1 )
from ( select (select f(1) from dual) f1 from dual );

client_info reads out 1

1) Why is the first query not treated like the second (a scalar), after all, f1 is replaced by its subquery on QRY1 first to create QRY2.

2) Even if Qry1 is not a pure scalar subquery why did the client info not say 3 as surely it should have been executed thrice?

June 02, 2005 - 4:42 pm UTC

because you repeated the scalar subquery and didn't use "f1"? That was the goal of the inline view and all.

why three times? I can see 2 or 3 being "logical" answers.

Ravi, June 03, 2005 - 8:53 am UTC

Tom,
Does that mean to use subquery caching, the queries should either be a WITH clause or an INLINE View?
Cheers
Ravi

June 03, 2005 - 9:56 am UTC

No, not at all -- here we had a case where the same subquery is referenced many times in the "same place".

There, for readability and ease of maintanance I would use an inline view so as to have to type it in once

but subquery caching was happening, just not as good as it could in this case because of the multiple references.

It was an "unusual" case, not the norm

Ravi, June 03, 2005 - 10:26 am UTC

Tom
If you dont mind, can you explain Sub Query Caching in more detail as it appears to be an undocumented feature.
Questions I have
1)Can any Query that has a sub query could become involved in Subquery caching.
2)Is there a Hint to force this to happen.
3)Is the WITH clause a definite subquery caching
Cheers
Ravi

June 03, 2005 - 11:07 am UTC

search for it on this site, I've demo'ed it over and over.

</code> http://asktom.oracle.com/pls/ask/search?p_string=%22scalar+subquery+caching%22 <code>

1) no, scalar subqueries only
2) no, no need, it is automagic
3) no, no -- it might materialize a view, but not cache a scalar subquery

The overhead for multi-row queries is not fixed

A reader, June 06, 2005 - 8:11 pm UTC

I ran the exact code that was given above.  The overhead for multi-row query that returned 895 rows was 8.  The overhead for 19 rows was 2.


SQL> create or replace function f1 return varchar2
  2  as
  3      l_num1 number := substr( userenv('client_info'), 1,
  4                         instr( userenv('client_info'),',')-1 );
  5      l_num2 number := substr( userenv('client_info'),
  6                         instr( userenv('client_info'),',')+1 );
  7  begin
  8      dbms_application_info.set_client_info( (l_num1+1)||','||l_num2 );
  9      return 'f1';
 10  end;
 11  /

Function created.

SQL> create or replace function f2 return varchar2
  2  as
  3      l_num1 number := substr( userenv('client_info'), 1,
  4                         instr( userenv('client_info'),',')-1 );
  5      l_num2 number := substr( userenv('client_info'),
  6                         instr( userenv('client_info'),',')+1 );
  7  begin
  8      dbms_application_info.set_client_info( l_num1||','||(l_num2+1) );
  9      return 'f2';
 10  end;
 11  /

Function created.

SQL> exec dbms_application_info.set_client_info('0,0');

PL/SQL procedure successfully completed.

SQL> SELECT DECODE(x.out, 'f1', x.out, f2) output
  2    FROM ( SELECT f1 out FROM all_users WHERE rownum >= 1 ) x;

OUTPUT
--------------------------------------------------------------------------------------------------------
f1
f1
f1
f1
f1
f1
f1
f1
... <snip> ...
f1

895 rows selected.

SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
903,0

SQL>
SQL>
SQL> exec dbms_application_info.set_client_info('0,0');

PL/SQL procedure successfully completed.

SQL> SELECT DECODE(x.out, 'f1', x.out, f2) output
  2    FROM ( SELECT f1 out FROM all_users WHERE rownum < 20 ) x;

OUTPUT
--------------------------------------------------------------------------------------------------------
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1

19 rows selected.

SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
21,0

SQL>
SQL> exec dbms_application_info.set_client_info('0,0');

PL/SQL procedure successfully completed.

SQL> SELECT DECODE(x.out, 'f1', x.out, f2) output
  2    FROM ( SELECT (select f1 from dual) out FROM all_users WHERE rownum < 20 ) x;

OUTPUT
--------------------------------------------------------------------------------------------------------
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1
f1

19 rows selected.

SQL> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
1,0

SQL>
SQL>

A reader, August 31, 2005 - 3:01 am UTC

select (Select f(null) from dual) ,
(Select f(null) from dual) from dual

Why does that not use Scalar subquery caching?

August 31, 2005 - 1:32 pm UTC

it does, there are two of them however.  it doesn't check to see if the scalar subqueries are the same (why would you do that he asks....) just that they are scalar subqueries.

ops$tkyte-ORA10G> create or replace function f ( x in number ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info( userenv('client_info')+1 );
  5          return 0;
  6  end;
  7  /

Function created.

ops$tkyte-ORA10G>
ops$tkyte-ORA10G> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte-ORA10G> select (select f(null) from dual), (select f(null) from dual)
  2    from all_objects
  3   where rownum <= 5;

(SELECTF(NULL)FROMDUAL) (SELECTF(NULL)FROMDUAL)
----------------------- -----------------------
                      0                       0
                      0                       0
                      0                       0
                      0                       0
                      0                       0

ops$tkyte-ORA10G> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
2

max, March 16, 2006 - 4:39 am UTC

i 've managed to speed up a really complex query by using subquery caching in conjunction with (packaged) user-defined PL/SQL functions.

the "pattern" is like that:

selec ( select MyPck.MyFct1( <some_view_columns> ) from dual ) Col1,
( select MyPck.MyFct2( <some_view_columns> ) from dual ) Col2
( select MyPck.MyFct3( <some_view_columns> ) from dual ) Col3,
<some_view_columns>
from <some_view>

the functions have been introduced as replacements for scalar subqueries that used to run fast unless the were built-in into the query above. all those functions have the same signature and take the same values from the base set.

using subquery caching like shown above reduced the runtime by (almost) the number of function calls (e.g. without subquery caching a query containing 10 function calls took 10 times longer).

the problem is: i can't reproduce the effect in general with the following very simple test case.

create or replace package Counter
is
procedure Initialize ;

function GetValue1( p_nNum All_Objects.Object_ID%type )
return number deterministic ;

function GetValue2( p_nNum All_Objects.Object_ID%type )
return number deterministic ;
end Counter ;
/

create or replace package body Counter
is
procedure Initialize
is
begin
DBMS_Application_Info.Set_Client_Info( '0' ) ;
end Initialize ;

function GetValue1( p_nNum All_Objects.Object_ID%type )
return number deterministic
is
v_cClientInfo V$Session.Client_Info%type ;
v_nClientInfo number ;
begin
DBMS_Application_Info.Read_Client_Info( v_cClientInfo ) ;
v_nClientInfo := nvl( to_number( v_cClientInfo ), 0 ) + 1 ;
DBMS_Application_Info.Set_Client_Info( v_nClientInfo ) ;
return 1 ;
end GetValue1 ;

function GetValue2( p_nNum All_Objects.Object_ID%type )
return number deterministic
is
v_cClientInfo V$Session.Client_Info%type ;
v_nClientInfo number ;
begin
DBMS_Application_Info.Read_Client_Info( v_cClientInfo ) ;
v_nClientInfo := nvl( to_number( v_cClientInfo ), 0 ) + 1 ;
DBMS_Application_Info.Set_Client_Info( v_nClientInfo ) ;
return 1 ;
end GetValue2 ;
end Counter ;
/

begin Counter.Initialize ; end ;
/
select ( select Counter.GetValue1( Object_ID ) from dual ) Counter1,
( select Counter.GetValue2( Object_ID ) from dual ) Counter2
from All_Objects
where RowNum < 11 ;
select Client_Info from V$Session where SID = ( select SID from V$MyStat where RowNum = 1 ) ;

could you please guide me to the answer (note: i 've already tried "no_merge")?

March 16, 2006 - 2:04 pm UTC

well, since object_id is UNIQUE in all_objects - what is there to be cached here exactly????? the inputs will always be DIFFERENT.

max, March 17, 2006 - 10:13 am UTC

but within one row the parameters always stay the same.

i 've observed that with function calls within scalar subqueries that function has been called only once per *row* instead of once per row and occurrence of function within query ...

March 17, 2006 - 5:48 pm UTC

so you are calling two functions - I'm not sure what the perceived problem here is? what were you expecting?

max, March 19, 2006 - 11:20 am UTC

yes, different functions (with the same signature and from the same package) are called per row.

each of these functions accesses the same (complex) view with the same parameters (take from the base query), the functions just return different columns of that view ...

March 19, 2006 - 2:11 pm UTC

so, what were you expecting to have happen is my question?

A reader, May 11, 2007 - 10:51 am UTC

SELECT DECODE ( f1, NULL, f ( 2 ), f1 )
FROM ( SELECT ( SELECT f ( 1 )
FROM DUAL ) f1, ( SELECT f ( 1 )
FROM DUAL ) f5
FROM DUAL
WHERE ( SELECT f ( 1 )
FROM DUAL ) = 1
AND ( SELECT f ( 1 )
FROM DUAL ) = 1 )

Tom,

In the above the scalar sub query is exected 3 times, twice for the where clause and once on the Select clause.

Questions

1) In one of the posts you said
select (select f(null) from dual), (select f(null) from dual)
2 from all_objects
3 where rownum <= 5;

It gets executed twice, but from the above example, it looks like it only executed ONCE on the Select Clause

2) Why cannot the Where clause subqueries talk to each other so that they only get executed once?

Thanks

Ravi

May 11, 2007 - 11:47 am UTC

f5 is not relevant, optimized away, you never select it.

A reader, May 14, 2007 - 5:12 am UTC

Tom,

Thanks for your answer to the previous post, yes, did not know optimizer checked to see F5 was being selected, Oracle only does work if necessary, good.

But, with the Where clause, presumably, the same scalar subquery in two of the clauses do not 'talk' to each other even though they are using the same
scalar subquery, probably not worthwhile to add this 'talk'.

But consider this

SELECT ( SELECT f ( 1 )
FROM DUAL )
FROM DUAL
WHERE ( SELECT f ( 1 )
FROM DUAL ) = 1
AND ( SELECT f ( 1 )
FROM DUAL ) > 0

The scalar subquery appears to get executed 3 times, surely there is a scope for the optimizer to detect that it had
already been executed and just grab the answer from the cache?

Thanks

Ravi

May 14, 2007 - 2:04 pm UTC

it "could" but it certainly doesn't "have to" and it may or may not execute them as it sees fit.

A reader, June 27, 2007 - 6:53 am UTC

Tom

Why is Oracle NOT doing scalar subquery caching by default on where clauses which have functions on one side, considering the benefits of not running them and CPU work etc?
Is the reason because Oracle will only do things if necessary, that is if the code has a scalar subquery construct, it will expend its cache if not why not.
Curiously though if for some reason I have functions in both sides of a where clause, it appears to optimize the where clause away!

My test case

create table t (a number, b number);

insert into t values (1,-1);

insert into t values (3,-1);

insert into t values (5, 6);

create or replace function f1 return number
as
begin
dbms_application_info.set_client_info( userenv('client_info')+1 );
return 1;
end;

begin
dbms_application_info.set_client_info( 0 );
end;

select userenv('client_info' ) from dual;

---- Now Oracle will run the function 3 times

select * from t where b > f1 and a is not null;

select userenv('client_info' ) from dual;

begin
dbms_application_info.set_client_info( 0 );
end;

--- Scalar subquery caching now, just once

select * from t where b >(select f1 from dual) and a is not null ;

select userenv('client_info' ) from dual;

begin
dbms_application_info.set_client_info( 0 );
end;

----- Now its the curious thing with function calls in both sides
---- Oracle optimizes the function call to two times probably
--- one for each side

select * from t where f1 = f1 and a is not null;

select userenv('client_info' ) from dual;

begin
dbms_application_info.set_client_info( 0 );
end;

---- Now with the function calls both sides using SSC
--- Oracle still calls the function twice, but still an optimization

select * from t where (select f1 from dual) = (select f1 from dual) and a is not null;

select userenv('client_info' ) from dual;

User defined function in sql where clause

Amit, April 28, 2009 - 9:42 am UTC

Hi Tom,

Tried the following:

create or replace function f return number
as
begin
         dbms_application_info.set_client_info( userenv('client_info')+1 );
         return 0;
 end;

exec dbms_application_info.set_client_info( 0 );

select object_id from all_objects
where  f = 1 and rownum < 21;

select userenv('client_info' ) from dual;

I expected f to be called for each row returned by the above query. But it is getting called only once. Wanted to know why ?

Its on 10g.

Thanks
Amit

April 28, 2009 - 10:51 am UTC

you have no control over the number of times a function is called - it could be called once per row, twice per row, maybe twice for the entire query, maybe once.

It evaluated "f=1" and said "no, f is not one, we are done"

There were no inputs to f, where f=1 only needs be evaluated once to figure out "f is not 1". If you parameterize f and send it unique inputs, it would be very different:

ops$tkyte%ORA10GR2> create or replace function f( p_id in number default null)  return number
  2  as
  3  begin
  4           dbms_application_info.set_client_info( userenv('client_info')+1 );
  5           return 0;
  6  end;
  7  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info( 0 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select object_id from all_objects where  f = 1 and rownum < 21;

no rows selected

ops$tkyte%ORA10GR2> select userenv('client_info' ) from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
1

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info( 0 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select object_id from all_objects where  f(object_id) = 1 and rownum < 21;

no rows selected

ops$tkyte%ORA10GR2> select userenv('client_info' ) from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
49838

Never assume your function will be called only ONCE or at least ONCE PER ROW or in a certain order, SQL is not procedural, it can optimize things - resulting in a different order of operation.

The above (first) query was optimized with a branch filter that would STOP the query from executing:

ops$tkyte%ORA10GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select object_id from t where  f = 1 and rownum < 21;

Execution Plan
----------------------------------------------------------
Plan hash value: 776512919

------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    20 |   260 |     2   (0)| 00:00:
|*  1 |  COUNT STOPKEY      |      |       |       |            |<b>
|*  2 |   FILTER            |      |       |       |            |</b>
|   3 |    TABLE ACCESS FULL| T    | 45400 |   576K|     2   (0)| 00:00:
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<21)
   2 - filter("F"()=1)

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> select object_id from t where  f(object_id) = 1 and rownum < 21;

Execution Plan
----------------------------------------------------------
Plan hash value: 508354683

------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    20 |   260 |     5  (20)| 00:00:0
|*  1 |  COUNT STOPKEY     |      |       |       |            |
|*  2 |   TABLE ACCESS FULL| T    |   454 |  5902 |     5  (20)| 00:00:0
------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<21)
   2 - filter("F"("OBJECT_ID")=1)

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> set autotrace off

The second execution only has a "filter" applied during processing, not a standalone filter step capable of stopping the query.

Thanks

Amit, April 29, 2009 - 6:21 am UTC

Hi Tom,javascript:doSubmit('SUBMIT_REVIEW')

Excellent explanation and many thanks for your time.

Amit

short-circuiting a sum expression?

Marius, February 09, 2011 - 5:38 am UTC

Since this is about short-circuiting, is there any way to short-circuit a sum expression?

Let me explain. We have a simple query which runs on a very big table every 15 minutes. That query tries to match new rows to older ones so that we can interlink them. Everything is ok if we say the new data must match all in a set conditions since the expression ends up like this:
AND condition1 AND condition2 ... AND condition[n]
where a condition is something like:
oldData.name = newData.name

This expression is easy, the first condition that doesn't match leads to a short-circuit, and that's mana when it comes to performance. The query takes between 1 and 30 seconds to complete, depending on the load, and that is awesome.

Our problem is, the rules are a bit too strict as they are, and we don't get enough matches, so now we would like to give a penalty score to each non matching condition and check the sum of the scores see if it's lower than what we're comfortable with. For now it's simple, we have 8 conditions each and every with a score of -1 and when their sum is < -2 then we would call it quits. Now the expression would look like this:
AND (
CASE WHEN condition1 THEN 0 ELSE -1 END
+
CASE WHEN condition2 THEN 0 ELSE -1 END
...
+
CASE WHEN condition8 THEN 0 ELSE -1 END
) >= -2

This would allow us get the matches we need, but the whole performance is horrible, the query runs sometimes for tens of minutes, and that must be in my opinion because there is no short-circuiting taking place anymore, all conditions get evaluated and that is very expensive especially for the VARCHAR columns.

Is there any way we could help trigger a short-circuit? Any other way you could think of for doing the things?

Using 10g R2 here.

Many thanks!

February 10, 2011 - 4:31 pm UTC

I cannot - off the top of my head - think of an easy way to do this.

where (c1 and (c2 or c3 or c4 ... or c8)
or (c2 and (c3 or c4 ... or c8)
or (c3 and ..... )

would be a method - if c1 was not true - go onto c2, if c2 not true go onto c3 and so on. It would be messy and big though.

just touching the question so it pops to the top datewise to see if anyone else has an idea.

UINON a la first grade.

Marius, February 23, 2011 - 11:25 am UTC

Hi Tom,

thanks for your reply. I tried that but the performance was
less than optimal and the SQL looked awful.

After I tried many an alternative, the best I found from a performance
point of view was to actually create an even uglier SQL with lots of
queried, their results all distinctly put together in a result
set by using UNION.

So now the monster query looks like this:

SELECT * FROM [...]
WHERE condition1 AND condition2 ... AND condition5 AND condition6

UNION
SELECT * FROM [...]
WHERE condition1 AND condition2 ... AND condition5 AND condition7

UNION
SELECT * FROM [...]
WHERE condition1 AND condition2 ... AND condition5 AND condition8

And so on and so forth, plenty of sub-statements.

Now, this gets the job done, it's very fast and the management doesn't
care about it being ugly.
But I'm still not satisfied, there must be some better way to do it,
I hate it when the best thing you can come it looks like first grade stuff :/

So if you or anyone else has any other idea, please post it.

Thanks!

February 24, 2011 - 12:30 pm UTC

well, I did say It would be messy and big though. :)

I don't see how your query would be faster than a single pass on the table with all of the conditions OR'ed together???

SELECT * FROM [...]
WHERE condition1 AND condition2 ... AND condition5 AND condition6
or condition1 AND condition2 ... AND condition5 AND condition7
...
or condition1 AND condition2 ... AND condition5 AND condition8

it seems to me that that would do LESS overall work. As it is - you have to make multiple passes on the table, evaluating the predicates against each and then distincting each result set and concatenating them together.

Rather than just making a single pass and executing as much of the predicate as we need to?

Unless there is an indexing strategy that is working well for you - but not knowing the schema and the actual query, it is hard to say....

CASE vs Decode Performance

A reader, August 07, 2012 - 1:44 pm UTC

Performance wise is CASE better than DECODE. I searched different sites, but could not come up with a conclusive answer.

I was wondering whether I should convert all my DECODE statements to CASE.

SELECT DECODE(COLUMN1, NULL, COLUMN2, COLUMN3) FROM TABLE;

SELECT (CASE WHEN COLUMN1 IS NULL THEN 
                COLUMN2
             ELSE
                COLUMN3
        END)
FROM TABLE;

August 17, 2012 - 12:34 pm UTC

why didn't you benchmark it?????????

sql_trace=true, run it, evaluate actual numbers.

no need to rely on anyone if you do that.

CASE vs Decode Performance

A reader, August 07, 2012 - 3:28 pm UTC

Please ignore my previous question. I found the link

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2998539000346762310

exploit short circuit to raise exception when a condition is verified

Michele, June 18, 2015 - 9:21 am UTC

Would you advise against exploiting the order of evaluation in a decode/case function to raise and exception in an application's select statement whenever an exceptional condition is verified ?

For example.
CREATE OR REPLACE FUNCTION RAISE_EX(error_description varchar ) RETURN number is
begin
dbms_standard.raise_application_error(-20000,error_description);
END;

select
case
when very_complex_condition1 then ...
when very_complex_condition2 then ...
when very_complex_condition3 then ...
when very_complex_condition4 then ...
else
RAISE_EX('this *exceptional* condition should nver happen');
end
from some_table a join some_other_table b on a.id_fk =b.id

Thank you,

Michele

p.s. I must congratulate you for your Expert Oracle Database Architecture book.

What about multiple and clauses and hard coded false?

Saad ahmad, January 23, 2018 - 1:42 pm UTC

Select
From table and joins
Where some stuff
And 1=2

Will optimizer know that it does not have to do anything? I did not see that short circuit in query plan. On sql server query plan shows it.

Thanks
Saad

January 24, 2018 - 12:17 am UTC

SQL> set autotrace traceonly explain
SQL> select * from t where 1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T    |  1000K|  4882K|   432   (3)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

See the FILTER which will stop us from ever getting to step 2 in the plan

What about multiple and clauses and hard coded false? - followup

Saad Ahmad, January 23, 2018 - 4:52 pm UTC

For reference, the query plan for a query like:

select * from <table> where 1=2
is
2-1-TABLE ACCESS FULL <table>

On SQLServer similar query shows plan:
select * from <table> where 1=2
|--Constant Scan

vs when it is 1=1 the plan on SQLSevrer is:
select * from <table> where 1=1
|--Clustered Index Scan(OBJECT:(<object PK>)

It looks like oracle does do short circuit but does it on execution vs planning which may not be such a bad thing. e.g. if I run

select *
from <large table> join <large table> on 1=1
where 1=2

it runs right away, so it definitely did not join first.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

DECODE evaluates the lot ???

Evaluation of expressions

NVL

Short circuiting queries

what is the difference between

Subquery caching

Subquery caching

scalar subquery caching

scalar subquery caching

to be sure ...

scalar subquery caching

Further on this

Further info...

Brain's just died!

Very interesting...

Why is this NOT a one-hit?

The overhead for multi-row queries is not fixed

User defined function in sql where clause

Thanks

short-circuiting a sum expression?

UINON a la first grade.

CASE vs Decode Performance

CASE vs Decode Performance

exploit short circuit to raise exception when a condition is verified

What about multiple and clauses and hard coded false?

What about multiple and clauses and hard coded false? - followup

More to Explore

Performance