Re: COALESCE does short circuit - NOT
Date: Thu, 19 Jan 2012 18:22:43 +0100
Message-ID: <e500a260b1f0ca287021492e865cd8b0.squirrel_at_mx1bln1.prossl.de>
Hej Maxim,
this is a good hint. Looks like this rule takes precedence over the short-circuit behaviour of functions. Well, sequences are something special in many ways...
Too bad, in cases where a sequence is only rarely needed lots of its values get thrown away then.
I've got another simple test case to show this:
SQL> create table testtab( x number );
Tabelle wurde erstellt.
SQL> insert into testtab values(NULL);
1 Zeile wurde erstellt.
SQL> insert into testtab values(42);
1 Zeile wurde erstellt.
SQL> insert into testtab values(4711);
1 Zeile wurde erstellt.
SQL> select x, COALESCE( x, seq_test.nextval ) y, seq_test.currval cur 2 from testtab;
X Y CUR
---------- ---------- ----------
12 12 42 42 13 4711 4711 14
Cheers,
Uwe
-- http://oraculix.wordpress.com/ Am Do, 19.01.2012, 17:37, schrieb Maxim:Received on Thu Jan 19 2012 - 11:22:43 CST
> Uwe, i think, it simply works this way. It doesn't matter, should it be
> short circuited or not, single reference in the select is sufficient for
> oracle to increment the sequence (once per row in the resultset) - it can
> be as well interpreted this way from documentation:
> http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns002.htm#SQLRF50946
>
> Within a single SQL statement containing a reference to NEXTVAL, Oracle
> increments the sequence once:
>
> For each row returned by the outer query block of a SELECT statement. Such
> a query block can appear in the following places:
>
> -
>
> A top-level SELECT statement
>
>
> The same effect you can observe in dead leafs of case statement (e.g. case
> when 1=2 then seq.nextval -- will be incremented as well)
>
> Best regards
>
> Maxim
>
> 2012/1/19 "Uwe Küchler" <uwe_at_kuechler.org>
>
>> Hi Folks,
>>
>>
>>
>>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-l