NVL2()

There are many little bits and pieces lurking in the Oracle code that would have been very useful if only you had had time to notice them when they frist appeared. Here’s one that seems to be virtually unknown but does an excellent job of eliminating calls to decode().

The nvl2() function takes three parameters, returning the second if the first is not null and returning the third if the first is null. This is  convenient for all sorts of examples where you might otherwise use an expression involving  case or decode(), but most particularly it’s a nice little option if you want to create a function-based index that indexes only those rows where a column is null.

Here’s a code fragment to demonstrate the effect:

rem
rem     Script: nvl2.sql
rem     Author: J.P Lewis
rem     Dated:  Sep 2012
rem

select nvl2(1,2,3) from dual;
select nvl2(null,2,3) from dual;
select nvl2(1,null,3) from dual;
select nvl2(null,null,3) from dual;

And here’s the resulting output – conveniently the function call is also the column heading in the output:

NVL2(1,2,3)
-----------
          2

NVL2(NULL,2,3)
--------------
             3

NVL2(1,NULL,3)
--------------


NVL2(NULL,NULL,3)
-----------------
                3

Note, particularly, from the thrid example that a non-null input (first parameter) turns into the null second parameter, and (fourth example) the null input turns into the non-null third parameter. To create a function-based index on rows where “columnX” is null and be able to access those rows by index you need only do the following:

create index t1_f1 on t1(nvl2(columnX,null,0));
select * from t1 where nvl2(columnX,null,0) = 0;

Don’t forget that you will need to gather stats on the hidden column supporting the function-based index before you can expect the optimizer to use it in the correct cases.

Update (August 2015)

You can get a slightly unexpected optimisation (generally small, but it could be significant if it allows a query to become index-only) from using nvl2() rather than nvl() for a predicate of the form (:bind is null or column = :bind) – see this thread on the Oracle database forum for details. The nvl2() function can be used as a workaround for a suprising limitation in the optimizer’s ability to apply a particular filter predicate in the index. Bear in mind, though, that the limitation is an example of the type of defect that tends to disappear after a couple of upgrades.