There are many little bits and pieces lurking in the Oracle code set that would be very useful if only you had had time to notice them. Here’s one that seems to be virtually unknown, yet does a wonderful job of eliminating calls to decode().
The nvl2() function takes three parameters, returning the third if the first is null and returning the second if the first is not null. This is convenient for all sorts of example 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:
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 last two that a non-null input (first parameter) turns into the null second parameter, and 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 them by index, you need only do the following:
create index t1_f1 on t1(nvl2(columnX,null,1)); select * from t1 where nvl2(columnX,null,1) = 1;
(Don’t forget, of course, that you will need to gather stats on the hidden column underpinning 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 could be significant if it allows a query to become index-only) from using nvl2() rather than nvl() or a predicate like (:bind is null or column = :bind) – see this thread on the OTN database forum for details. It’s a workaround for something that appears to be a limitation in the optimizer’s ability to apply filter predicates in the index.