NVL and DECODE: Lazy vs Eager Evaluation

At first glance, Oracle’s NVL and DECODE seem like simple tools to handle NULL values, right? But under the hood, they have a different behavior some of them you may not know and this behavior is the topic of this blog post. If you’re using them interchangeably, you might be introducing performance issues or even runtime errors without knowing it.

This isn’t a new discovery; Tom Kyte demonstrated this behavior over 25 years ago in 2000. However, it’s still not widely known. For more insights, you can refer to the Ask TOM discussion titled “DECODE or NVL.“

First I will explain the lazy and eager evaluation terms simply:

Eager evaluation:

Eager evaluation refers to a programming or query evaluation strategy where all expressions (or arguments) are evaluated immediately, whether or not they are needed. In the context of SQL functions like NVL, eager evaluation means that both arguments of the function are evaluated, even if the first input is sufficient to determine the result.

Lazy Evaluation (The Opposite) – Short Circuit

In contrast, lazy evaluation only evaluates the second argument when it is actually needed. In the NVL example, lazy evaluation would mean that counter_function(col1) is only called when col1 is null. In SQL, CASE, DECODE and COALESCE expressions exhibit lazy evaluation behavior, only executing the second argument if the first condition isn’t met.

Let’s unravel the difference, starting with an eye-opener:

NVL / NVL2 does not short circuit :

Here’s a common pattern that looks harmless:

SQL> SELECT NVL(col1, counter_function(col1)) FROM my_table;

You might expect that:

If col1 is not null, it should be returned directly without executing high_cost_computation(col1).
If col1 is null, only then should counter_function(col1) be called.

But that’s not what happens. Oracle evaluates both arguments of NVL, no matter what. That means counter_function(col1) is always executed even if col1 is not null. This can lead to unnecessary work, performance issues, or even runtime errors.

Here is the case :

I will create a table, a sequence for a counter, and an inline function (counter_function) – Prepare Phase:

SQL> CREATE TABLE my_table (col1 VARCHAR2(20));
SQL> INSERT INTO my_table VALUES ('insanedba');
SQL> INSERT INTO my_table VALUES (NULL);
SQL> COMMIT;

SQL> CREATE SEQUENCE call_counter_seq ;

Now i will query the sql statement for all the table records and see how much counter_function is executed.

-- Demonstrate (eager evaluation)
SQL> WITH
  FUNCTION counter_function(p_val VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    -- Increment the sequence to track calls
    DECLARE n NUMBER;
    BEGIN
      SELECT call_counter_seq.NEXTVAL INTO n FROM dual;
    END;
    RETURN 'fallback';
  END;
SELECT NVL(col1, counter_function(col1)) AS result
FROM my_table;
/


RESULT
--------------------------------------------------------------------------------
insanedba
fallback

SQL> SELECT call_counter_seq.CURRVAL AS function_call_count FROM dual;

FUNCTION_CALL_COUNT
-------------------
                  2

It is not only for performance reasons, but you may also encounter unexpected errors.

SQL> CREATE TABLE sample_table (
    col1 NUMBER,
    col2 NUMBER,
    col3 NUMBER
);

SQL> insert into sample_table (col1, col2, col3) values (1,0,63);
SQL> insert into sample_table (col1, col2, col3) values (null,1,64);
SQL> COMMIT;

SQL> select * from sample_table;

   COL1    COL2    COL3
_______ _______ _______
      1       0      63
              1      64

SQL> select NVL(col1,col3/col2) from sample_table;

Error starting at line : 1 in command -
select NVL(col1,col3/col2) from sample_table
Error at Command Line : 1 Column : 21
Error report -
SQL Error: ORA-01476: divisor is equal to zero

https://docs.oracle.com/error-help/db/ora-01476/01476. 00000 -  "divisor is equal to zero"
*Cause:    An expression attempted to divide by zero.
*Action:   Correct the expression, then retry the operation.

More Details :
https://docs.oracle.com/error-help/db/ora-01476/

SQL> select COALESCE(col1,col3/col2) from sample_table;

   COALESCE(COL1,COL3/COL2)
___________________________
                          1
                         64

DECODE short circuits :

SQL> ALTER SEQUENCE call_counter_seq RESTART START WITH 1;

-- Demonstrate (lazy evaluation - short circuit)
SQL> WITH
  FUNCTION counter_function(p_val VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    -- Increment the sequence to track calls
    DECLARE n NUMBER;
    BEGIN
      SELECT call_counter_seq.NEXTVAL INTO n FROM dual;
    END;
    RETURN 'fallback';
  END;
SELECT DECODE(col1, null, counter_function(col1),col1) AS result
FROM my_table;
/

SQL> SELECT call_counter_seq.CURRVAL AS function_call_count FROM dual;

FUNCTION_CALL_COUNT
-------------------
                  1

COALESCE also short circuits :

You may also use COALESCE which is part of the ANSI SQL standard.

SQL> ALTER SEQUENCE call_counter_seq RESTART START WITH 1;

-- Demonstrate (lazy evaluation)
SQL> WITH
  FUNCTION counter_function(p_val VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    -- Increment the sequence to track calls
    DECLARE n NUMBER;
    BEGIN
      SELECT call_counter_seq.NEXTVAL INTO n FROM dual;
    END;
    RETURN 'fallback';
  END;
SELECT COALESCE(col1, counter_function(col1)) AS result
FROM my_table;

SQL> SELECT call_counter_seq.CURRVAL AS function_call_count FROM dual;

FUNCTION_CALL_COUNT
-------------------
                  1

Conclusion:

While NVL is syntactically simpler, its lack of lazy evaluation can lead to both performance and some error issues in certain queries. If your fallback value involves a high cost function or potentially error-prone function, consider using DECODE or even CASE for safer, more efficient logic.

Hope it helps. See you on the next post : Part 2: Scalar Subquery Caching Behavior in a SQL Statement.