Oracle Scratchpad

April 23, 2012

NVL2()

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

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

5 Comments »

  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 BST 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 BST 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;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      PL/SQL Release 11.1.0.7.0 - Production
      CORE 11.1.0.7.0 Production
      TNS for Linux: Version 11.1.0.7.0 - Production
      NLSRTL Version 11.1.0.7.0 - 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';

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

      9 rows selected.

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

      TEST_FUNC(NULL)
      ----------------------------------------------------------------------------------------------------
      Null

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

      PL/SQL Wrapper: Release 10.2.0.1.0- 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';

      TEXT
      ----------------------------------------------------------------------------------------------------
      function test_func wrapped
      snipped

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

      TEST_FUNC(NULL)
      ----------------------------------------------------------------------------------------------------
      Null

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

      TEST_FUNC('HELLO')
      ----------------------------------------------------------------------------------------------------
      Not Null

      3:1051:45945:11234>

      Thanks

      Comment by Raj — April 24, 2012 @ 10:25 am BST 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 BST 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 BST 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:

WordPress.com Logo

You are commenting using your WordPress.com 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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,989 other followers