Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> NVL doesn't short-circuit

NVL doesn't short-circuit

From: <ctcgag_at_hotmail.com>
Date: 03 Nov 2003 19:49:37 GMT
Message-ID: <20031103144936.932$gZ@newsreader.com>


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 - 13:49:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US