Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> NVL doesn't short-circuit
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 50GBReceived on Mon Nov 03 2003 - 13:49:37 CST