Re: COALESCE does short circuit - NOT

From: Andy Klock <andy_at_oracledepot.com>
Date: Thu, 19 Jan 2012 11:01:35 -0500
Message-ID: <CADo_RaMxxWxUSD25NZhMrVB-0AGtYAyKacjkBqrb+r08D=gyGg_at_mail.gmail.com>



Thanks Uwe. Now I see what you mean. And I also see the Oracle documentation splattered with the phrase short-circuit which apparently has escaped me all these years. The problem with your example was my eyes didn't see that you used "CURRVAL". So my confusion was self-induced. I did a quick test with case which also uses a short-circuit evaluation to verify that it behaved the same way.

SQL> select seq_test.currval from dual;

   CURRVAL


         8

SQL> select case 1 when 1 then 1 when 2 then seq_test.nextval end from dual;

CASE1WHEN1THEN1WHEN2THENSEQ_TEST.NEXTVALEND


                                          1

SQL> select seq_test.currval from dual;

   CURRVAL


         9

I traced your original example and it looks like that sequence gets fired when the cursor is closed (STAT).

PARSING IN CURSOR #140715042653128 lenP dep=0 uid=0 oct=3 lid=0 tim26987848717176 hv62166629 ad='6fff0f38' sqlid='g0uust1nhj1v5' select COALESCE( 9, seq_test.nextval ) x from dual END OF STMT
PARSE
#140715042653128:cH992,ew240,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh06770343,tim26987848717163 EXEC
#140715042653128:c999,e150,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh06770343,tim26987848727534 FETCH
#140715042653128:c=0,e3,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh06770343,tim26987848727705

  • 2012-01-19 10:44:08.750 STAT #140715042653128 id=1 cnt=1 pid=0 pos=1 objw818 op='SEQUENCE SEQ_TEST (cr=0 pr=0 pw=0 timeQ us)' STAT #140715042653128 id=2 cnt=1 pid=1 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)' FETCH #140715042653128:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh06770343,tim26987848751181
  • 2012-01-19 10:44:17.524 CLOSE #140715042653128:c=0,e,dep=0,type=0,tim26987857524047

So, I've learned something new. Thank you.

2012/1/19 "Uwe Küchler" <uwe_at_kuechler.org>

> Hi Andy,
>
> "Short Circuit" means, the database evaluates each expr value and
> determines whether it is NULL, rather than evaluating all of the expr
> values before determining whether any of them is NULL.
> http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm
>
> That's what the Docu says, but in fact this is not the case with
> Sequences. As you can see, the sequence is incremented, although the first
> expr is NOT NULL and COALESCE should have exited before evaluating
> seq_test.nextval.
>
> Cheers,
> Uwe
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jan 19 2012 - 10:01:35 CST

Original text of this message