Re: COALESCE does short circuit - NOT
- From: Andy Klock <andy@xxxxxxxxxxxxxxx>
- To: Uwe Küchler <uwe@xxxxxxxxxxxx>
- Date: Thu, 19 Jan 2012 11:01:35 -0500
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@xxxxxxxxxxxx>
> 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
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
