Why does NVL always evaluate 2nd parameter

Question

Does anyone know, why Oracle's NVL (and NVL2) function always evaluate the second parameter, even if the first parameter is not NULL?

Simple test:

CREATE FUNCTION nvl_test RETURN NUMBER AS
BEGIN
  dbms_output.put_line('Called');
  RETURN 1;
END nvl_test;

SELECT NVL( 0, nvl_test ) FROM dual

returns 0, but also prints Called.

nvl_test has been called, even though the result is ignored since first parameter is not NULL.

Cystocarp

Answer 1

It's always been that way, so Oracle has to keep it that way to remain backwards compatible.

Use COALESCE instead to get the short-circuit behaviour.

Senseless

Caveat: it doesn't short-circuit in 9i. – Senseless

Answer 2

Here is a post where Tom Kyte confirms that decode and case short circuit but not nvl but he doesn't give justification or documentation for why. Just states it to be:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:926029357278#14932880517348

So in your case you should use decode or case instead of nvl if an expensive function will be called in your query.

Ogletree

Answer 3

In general, it would make sense that the second parameter is evaluated before calling the function, because in general that is how functions are called: all arguments to the function are evaluated and the evaluated values are sent to the function.

However, in the case of a very common system function like NVL, I would have thought PL/SQL could optimise, treating the function call as a special case. But perhaps that is more difficult than it sounds (to me), as I'm sure this optimisation would have occurred to the developers of Oracle.

Rhiamon

Answer 4

They are obviously not short-circuiting, but I can't find any references in Oracle documentation.

Check out this discussion: http://forums.oracle.com/forums/thread.jspa?messageID=3478040

Stotts