Re: COALESCE does short circuit - NOT
- From: "Uwe Küchler" <uwe@xxxxxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 19 Jan 2012 18:22:43 +0100
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:
> 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@xxxxxxxxxxxx>
>
>> Hi Folks,
>>
>>
>>
>>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
