Oracle Scratchpad

April 23, 2012


Filed under: Function based indexes,Indexing,Oracle,Performance — Jonathan Lewis @ 5:43 pm GMT Apr 23,2012

There are many little bits and pieces lurking in the Oracle code 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 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:

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:





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,0));
select * from t1 where nvl2(columnX,null,0) = 0;

Don’t forget 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 it could be significant if it allows a query to become index-only) from using nvl2() rather than nvl() for a predicate for a predicate of the form (: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 a particular filter predicate in the index.



  1. The nvl2 function is really more appropriate for such fbi in fact, since it is faster than the “decode (x, null, 1)” and, although a little slower than the “case when x is null then 1 end”, but more readable and shorter.

    with gen as (
                select n
                from (select level from dual connect by level<=1e6)
                    ,(select 1 n from dual
                      union all 
                      select null from dual
                     ) t
    select sum(nvl2(n,null,1))                 --1.046 secs
           --sum(decode(n,null,1))               -- 1.482 secs
           --sum(case when n is null then 1 end) --0.982 seconds
    from gen

    But it also has disadvantages. For example, in contrast to “decode” it evaluates all its arguments.

    Comment by Sayan Malakshinov — April 23, 2012 @ 7:39 pm GMT Apr 23,2012 | Reply

  2. I fell in love with NVL2 for about a week – and then realised that it wasn’t a supported construct under the WRAP tool, so it was back to decodes. Sigh.

    Comment by Kieran Walsh — April 23, 2012 @ 8:57 pm GMT Apr 23,2012 | Reply

    • @Kieran,

      I never seem to have any problems with nvl2 apart from the fact that nvl2 is not supported in a pl/sql context for some reason.

      3:1051:45945:11234>select * from v$version;

      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE Production
      TNS for Linux: Version - Production
      NLSRTL Version - Production

      3:1051:45945:11234>@ c:\work\sql_scripts\t.sql

      Function created.

      3:1051:45945:11234>select text from user_Source where name = 'TEST_FUNC';

      function test_func(pi_input Varchar2)
      return varchar2
      l_Status Varchar2(10);
      select nvl2(pi_input, 'Not Null', 'Null') into l_status
      from dual;
      return l_Status;

      9 rows selected.

      3:1051:45945:11234>select test_func(null) from dual;


      C:\work\sql_scripts>wrap iname=t.sql oname=t.plb

      PL/SQL Wrapper: Release Production on Tue Apr 24 11:18:34 2012

      Copyright (c) 1993, 2004, Oracle. All rights reserved.

      Processing t.sql to t.plb

      3:1051:45945:11234>@ c:\work\sql_scripts\t.plb

      Function created.

      3:1051:45945:11234>select text from user_Source where name = 'TEST_FUNC';

      function test_func wrapped

      3:1051:45945:11234>select test_func(null) from dual;


      3:1051:45945:11234>select test_func('hello') from dual;

      Not Null



      Comment by Raj — April 24, 2012 @ 10:25 am GMT Apr 24,2012 | Reply

  3. Also more useful if they work. See MOS Bug 5490501 – Wrong Results with COALESCE or NVL2 on aggregations inside subqueries [ID 5490501.8]

    The wrap issue is explained in How to Find out the Reason for PLS-00201 in PL/SQL [ID 269973.1]: “Some recent SQL syntax is not supported by the wrap utility by default. To enable the support for all SQL syntax, specify the option edebug=wrap_new_sql (with no dash). “

    Comment by jgarry — April 24, 2012 @ 8:58 pm GMT Apr 24,2012 | Reply

  4. Thanks for the responses everyone.

    This is one reason why I like the blogging environment to the old Web Page approach. It allows other people to add value to the original article and reveal extra benefits (or problems, or version-dependencies) that you wouldn’t necessarily find out for yourself until it’s too late.

    Comment by Jonathan Lewis — April 30, 2012 @ 2:32 pm GMT Apr 30,2012 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Powered by