Re: NVL doesn't short-circuit

From: <Kenneth>
Date: Mon, 03 Nov 2003 20:45:37 GMT
Message-ID: <3fa6b738.3636278@news.inet.tele.dk>

Hi Ctcgag,

Cannot confirm your observation regarding DECODE (at least not in 9i):

SQL>
SQL> select decode('a','a',3,0/0) from dual;

DECODE('A','A',3,0/0)

---

                    3

If it did not short-circuit, you would get

"ORA-01476 divisor is equal to zero".

PL/SQL *does* short-circuit.PL/SQL users guide states that :

"Quote"

"PL/SQL uses short-circuit evaluation. That is, PL/SQL stops
evaluating the expression as soon as the result can be determined (...) In any case, it is a poor programming practice to rely on short-circuit evaluation.

"Unquote"

I do not agree with the last sentence, but that's personal.

SQL seems to short-circuit in some cases and some not:

---

SQL> select 'Does short-circuit' from dual   2 where 1 = 1
  3 or 0/0 = 1;

'DOESSHORT-CIRCUIT
------------------
Does short-circuit

SQL>
SQL> select 'Does short-circuit' from dual   2 where 0 = 1
  3 and 0/0 = 1;
and 0/0 = 1

     *
FEJL i linie 3:
ORA-01476: divisor is equal to zero

---

Conclusion : Do not count on SQL short-circuiting. Ever.

NVL and DECODE are built-in functions, and AFAIK, Oracle doesnt' state anything about these short-circuiting or not. So don't count on them doing so. Even though DECODE seems to short-circuit in 9i, that could change with 10G or 11W....

• Kenneth Koenraadt

On 03 Nov 2003 19:49:37 GMT, ctcgag_at_hotmail.com wrote:

>I did the experiment below (9.2.0.2, SunOS 5.8), and was very surprised to
>see that the second expression of the nvl is evaluated even when
>it's result will be thrown away (i.e., when the first expression is not
>null). The same behavior seems to apply with "decode", also.
>
>I was very surprised at this, because such behaviour would seem to lead
>to worse performance (imagine that the second expression is a slow external
>function call, which could be avoided 99.9% of the time by
>short-circuiting). Is there some subtle reason I'm missing that forces it
>to behave this way for consistency sake?  Is there some pure-SQL way around
>it?
>
>Thanks,
>
>Xho
>
>
>SQL> select nvl(null,test_seq.nextval) from dual;
>
>NVL(NULL,TEST_SEQ.NEXTVAL)
>--------------------------
>                       123
>
>SQL> select nvl(1,test_seq.nextval) from dual;
>
>NVL(1,TEST_SEQ.NEXTVAL)
>-----------------------
>                      1
>
>SQL> /
>
>NVL(1,TEST_SEQ.NEXTVAL)
>-----------------------
>                      1
>
>SQL> /
>
>NVL(1,TEST_SEQ.NEXTVAL)
>-----------------------
>                      1
>
>SQL> /
>
>NVL(1,TEST_SEQ.NEXTVAL)
>-----------------------
>                      1
>
>SQL> select nvl(null,test_seq.nextval) from dual;
>
>NVL(NULL,TEST_SEQ.NEXTVAL)
>--------------------------
>                       128
>
>-- 
>-------------------- http://NewsReader.Com/ --------------------
>Usenet Newsgroup Service              New Rate! $9.95/Month 50GB

Received on Mon Nov 03 2003 - 14:45:37 CST