NVL vs DECODE

Oracle NVL vs COALESCE vs DECODE: Short-Circuit Evaluation

Oracle’s NVL, COALESCE, and DECODE can produce similar results, but they differ in how expressions are evaluated.

NVL: Oracle may evaluate both arguments even when the first argument is not NULL.

COALESCE: Uses short-circuit evaluation, stopping at the first non-NULL expression.

DECODE: Uses short-circuit evaluation when checking search expressions.

CASE: Uses short-circuit evaluation when evaluating conditions.

These differences matter when an expression calls a resource-intensive function or could raise an exception.

In this article, I demonstrate these behaviors using Oracle SQL examples, a sequence to count function executions, and a division-by-zero test that produces ORA-01476 with NVL but not with COALESCE.

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.“

FunctionEvaluation BehaviorPotential Consequence
NVLMay evaluate both argumentsUnnecessary function calls or errors
COALESCEShort-circuitAvoids evaluating later expressions once a non-NULL result is found
DECODEShort-circuits search expressionsStops searching after a match
CASEShort-circuits conditionsSkips subsequent conditions once a match is found

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

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.

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:

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
NVL eager evaluate
NVL does not short-circuit

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
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
Decode-Strict Evaluation
Decode and Coalesce 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

Does Oracle NVL use short-circuit evaluation?

Unlike COALESCE, Oracle’s NVL does not guarantee short-circuit evaluation. Its second argument may be evaluated even when the first argument is not NULL. This can cause unnecessary function executions or unexpected errors.

Is COALESCE faster than NVL in Oracle?

Not necessarily. For simple expressions, the performance difference may be negligible. However, COALESCE can be more efficient when later expressions involve expensive functions because it avoids evaluating them once a non-NULL result has been found. Always verify performance using the actual SQL statement and execution plan.

Why can NVL raise ORA-01476 when COALESCE does not?

NVL may evaluate its second argument even when the first argument is not NULL. If that argument contains a division by zero, Oracle can raise ORA-01476. COALESCE avoids evaluating later arguments once it encounters a non-NULL value, preventing that error in the demonstrated scenario.

Although NVL, COALESCE, and DECODE can produce similar results, their evaluation behavior differs significantly.

When fallback expressions involve expensive function calls or operations that might raise exceptions, COALESCE or CASE may be preferable because of their short-circuit evaluation behavior.

However, these expressions are not always interchangeable, particularly regarding datatype conversion and return types. Choose the appropriate function based on the required semantics and test its behavior in your Oracle Database environment.

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

Also my other Common SQL/PLSQL Pitfalls – Blog Posts:

Common SQL and PL/SQL Pitfalls: Insights from Real-World Experience

NVL and DECODE: Lazy vs Eager Evaluation (Part 1)

Scalar Subquery Caching Behavior in a SQL Statement (Part 2)

Why NO_DATA_FOUND Behavior Differs in SQL and PL/SQL (Part 3)

Oracle SUM() Returns NULL or No Rows? GROUP BY Explained (Part 4)

Avoid Misusing LEFT JOIN in SQL Queries (Part 5)


Discover More from Osman DİNÇ


Comments

Leave your comment