Why Does Nvl Always Evaluate 2Nd Parameter

Why does NVL always evaluate 2nd parameter

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.

What is the purpose of NVL having to evaluate both arguments?

The point probably is that there might be side effects so that not only the return value of both arguments is important but also their side effects. NVL won't miss those side effects while COALESCE will miss the side effects of the second argument in some cases (due to the short-circuit behavior). So NVL would be very useful in those cases (when you don't want to miss any side effects).

Why does NVL work over COALESCE in Oracle?

From the NVL documentation:

The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

• If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1.
• If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

From the COALESCE documentation:

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

If all occurrences of expr are numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type, then Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

You will notice that NVL explicitly states it will perform an implicit conversion so that expr2 is the same data type as expr1 whereas COALESCE (although slightly confusingly worded) does not mention performing an implicit conversion (except for numeric data types) and will expect that all expressions in its argument list are the same data type.

Your query for NVL is effectively converted to:

UPDATE my_table
SET    some_column = CAST(
                       NVL(
                         :your_string_bind_variable,
                         CAST( some_column AS VARCHAR2 )
                       )
                       AS NUMBER
                     )
WHERE...

but your COALESCE function is:

UPDATE my_table
SET    some_column = COALESCE(
                       :your_string_bind_variable,
                       some_column
                     )
WHERE...

and expr1 and expr2 have different data types and the query raises an exception.

---

Assuming that there are no other columns being modified, you do not need to perform an UPDATE if the value is NULL as it is not going to change anything and could re-write your Java code as:

MyEnum enumValue = myClass.getMyEnum();
if ( enumValue != null )
{
  getJdbcTemplate().update(
    "UPDATE my_table SET some_column = ? WHERE...",
    enumValue.getStatus()
  );
}

Oracle Differences between NVL and Coalesce

COALESCE is more modern function that is a part of ANSI-92 standard.

NVL is Oracle specific, it was introduced in 80's before there were any standards.

In case of two values, they are synonyms.

However, they are implemented differently.

NVL always evaluates both arguments, while COALESCE usually stops evaluation whenever it finds the first non-NULL (there are some exceptions, such as sequence NEXTVAL):

SELECT  SUM(val)
FROM    (
        SELECT  NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This runs for almost 0.5 seconds, since it generates SYS_GUID()'s, despite 1 being not a NULL.

SELECT  SUM(val)
FROM    (
        SELECT  COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
        FROM    dual
        CONNECT BY
                level <= 10000
        )

This understands that 1 is not a NULL and does not evaluate the second argument.

SYS_GUID's are not generated and the query is instant.

Oracle is executing NVL second argument query when the first argument is not null

You can use COALESCE instead:

Oracle Database uses short-circuit evaluation. The database evaluates each expr value and determines whether it is NULL, rather than evaluating all of the expr values before determining whether any of them is NULL.

There is also the advantage that it's standard SQL, rather than the proprietary NVL.

Can NVL Function be Cascaded?

Better yet, use COALESCE

PL/SQL Logical Operators evaluate whole function

The CS name is short-circuiting (wikipedia entry on short-circuit evaluation) and yes, plsql does exactly that.

---

Related Topics