Oracle NVL and NULL: Performance considerations

A recent query I had to tune had a WHERE clause similar to this:

where t.myno = 1 and t.jz1 not in(3,4,5)
          and (nvl(t.amount,0) != 0)
          and  nvl(t.status,'!') != 'J'

Notice the NVL functions.  Let’s look at the first one.

nvl(t.amount,0) != 0

In pseudo-code, what it’s doing is:

IF amount IS NULL
   THEN set amount equal to zero
   ELSE leave amount alone
RETURN TRUE if amount is not equal to zero

The problem here is that it’s a misuse of the NVL function based on a misunderstanding of how Oracle evaluates NULL.  The usage is based on the premise that NULL compared to 0 would evaluate to TRUE; but that’s incorrect.  NULL compared to anything, including another NULL, returns UNKNOWN.  SELECT statements with a condition in the WHERE clause that evaluates to UNKNOWN return no rows, just like FALSE.

So the above condition can be rewritten without the NVL and get the same results:

t.amount != 0

But if the results are the same, does it really matter if the NVL function is used?  Yes.  NVL is not free and consumes extra CPU.  Consider 500 executions of a simple query with NVL, and another 500 functionally equivalent without:

SELECT COUNT(*)
FROM
nvltest WHERE NVL(id,0) != 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      500      0.00       0.01          0          0          0           0
Execute    500      0.00       0.01          0          0          0           0
Fetch      500     20.48      33.94    2627501    2629500          0         500
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1500     20.50      33.97    2627501    2629500          0         500


SELECT COUNT(*)
FROM
nvltest WHERE id != 0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      500      0.00       0.01          0          0          0           0
Execute    500      0.01       0.02          0          0          0           0
Fetch      500      6.70      12.14    2627501    2629500          0         500
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1500      6.72      12.18    2627501    2629500          0         500

Five hundred executions with the NVL incurred a CPU overhead of > 20 seconds, compared to 6.72 seconds without the NVL, and the results are the same.  This is the kind of overhead that puts a drag on the user experience.

So when you are looking at an SQL query, always be suspicious of function calls.  They can introduce a lot of overhead, in addition to other issues.