Oracle Scratchpad

January 1, 2014

NVL()

Filed under: Oracle,Performance — Jonathan Lewis @ 6:11 pm BST Jan 1,2014

Here’s a point that’s probably fairly well-known, but worth repeating – nvl() and coalesce() aren’t identical in behaviour but you may have some cases where you’re usingnvl() when coalesce() would be a more efficient option.

The reason for this is “short-circuiting”. The expression nvl(expr1, expr2) will return expr2 if expr1 is null, otherwise it will return expr1; the expression coalesce(expr1, expr2, …, exprN) will return the first non-null expression in the list so, in particular, coalesce(expr1, expr2) will give the same result as nvl(expr1, expr2) ; the big difference is that nvl() will evaluate both expressions, while coalesce will evaluate expr2 only if it needs to (i.e. only if expr1 evaluates to null). In many cases the difference in performance will be insignificant – but consider the following type of construct (t1 is a table with a single, numeric, column n1 and a single row):


create table t1 as select 1 n1 from dual;
execute dbms_stats.gather_table_stats(user,'t1')

select
	nvl(n1, (select max(object_id) from all_objects))	nn
from
	t1
;

select
	coalesce(n1, (select max(object_id) from all_objects))	nn
from
	t1
;

In the first query Oracle will execute the inline scalar subquery whether n1 is null or not.
In the second query Oracle will execute the inline scalar subquery only if n1 is null.
I know which option I would prefer to use if I knew that n1 could be null.

Footnote:

There is a trap that you have to watch out for – try recreating t1 with n1 defined as a varchar2() column and the query with coalesce() will fail with Oracle error ORA-00932: inconsistent datatypes: expected CHAR got NUMBER the expressions that appear in the coalesce() must all be explicitly of the same type while nvl() will do implicit conversions when necessary, so be a little careful with the code when you’re looking for opportunities to make the change.

Note: the same difference (strictness of typing) appears when you compare decode() – which does implicit conversion when necessary – with case end – which does not. (Both case and decode() short-circuit, though).

9 Comments »

  1. coalesce’s short circuiting is great for inline scalar subquerys – see http://marogel.wordpress.com/2013/05/10/partition-info-in-vsession_longops/ for another example

    Comment by Matthias Rogel — January 1, 2014 @ 7:15 pm BST Jan 1,2014 | Reply

    • Matthias,

      Nice example, but I’m not keen on the predicate sw.P1 = de.FILE_ID and sw.P2 between de.BLOCK_ID and de.BLOCK_ID + de.BLOCKS – 1
      For recent versions of Oracle would it be worth looking at row_wait_obj# in v$session rather than going to v$session_wait for the p1, p2 values – I think it might hold the object_id whenever you wait for (among other things) I/O completion.

      Comment by Jonathan Lewis — January 1, 2014 @ 7:43 pm BST Jan 1,2014 | Reply

      • Thank you for the suggestion. I have tested it and never observed that I wasn’t able to get the partition information from v$session.row_wait_obj# ( as suggested by you ), but from my part of the query, so I have updated the query and the post accordingly.

        Comment by Matthias Rogel — February 27, 2014 @ 7:08 am BST Feb 27,2014 | Reply

  2. Hello,

    Not so lazy evaluation:

    create sequence tmp_seq;
    select coalesce(null,tmp_seq.nextval) from dual;
    select coalesce(null,tmp_seq.nextval) from dual;
    select coalesce(10,tmp_seq.nextval) from dual;
    select coalesce(20,tmp_seq.nextval) from dual;
    select coalesce(30,tmp_seq.nextval) from dual;
    select tmp_seq.currval from dual;
    

    OR did I miss something?

    Comment by François — January 2, 2014 @ 7:23 pm BST Jan 2,2014 | Reply

    • François,

      Nice catch: I get 5 in both 11.2.0.4 and 12.1.0.1 – the nextval is executed whether or not the first input is null.
      If we look at the execution plan I think we can make a reasonable guess why it’s a special case:

      
      ----------------------------------------------------
      | Id  | Operation        | Name    | Rows  | Cost  |
      ----------------------------------------------------
      |   0 | SELECT STATEMENT |         |     1 |     2 |
      |   1 |  SEQUENCE        | TMP_SEQ |       |       |
      |   2 |   FAST DUAL      |         |     1 |     2 |
      ----------------------------------------------------
      
      

      It looks as if a sequence call is implemented as something that operates once for every row produced by its child rowsource. This would help to explain why you can call nextval and currval multiple times for the same sequence and get the same value across every occurrence of the call in the row; but it does mean you may call for a value which you don’t use.

      As a side note, it’s also interesting to note another limitation (that’s not entirely surprising if the conjecture is correct):

      select coalesce(20,(select tmp_seq.nextval from dual)) from dual
                                         *
      ERROR at line 1:
      ORA-02287: sequence number not allowed here
      
      

      Comment by Jonathan Lewis — January 2, 2014 @ 7:59 pm BST Jan 2,2014 | Reply

    • That’s how NEXTVAL works

      “…Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence once …”

      http://docs.oracle.com/cd/E16655_01/server.121/e17209/pseudocolumns002.htm#SQLRF50946

      Comment by Matthias Rogel — January 2, 2014 @ 8:01 pm BST Jan 2,2014 | Reply

    • François,

      if you are on 12c, you might want to compare the outputs of

      drop sequence tmp_seq;
      create sequence tmp_seq;
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select nvl(null, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select nvl(null, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select nvl(10, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select nvl(20, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select nvl(30, tmp_seq_nextval()) from dual
      /
      select tmp_seq.currval from dual;
      

      vs.

      drop sequence tmp_seq;
      create sequence tmp_seq;
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select coalesce(null, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select coalesce(null, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select coalesce(10, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select coalesce(20, tmp_seq_nextval()) from dual
      /
      with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
      select coalesce(30, tmp_seq_nextval()) from dual
      /
      select tmp_seq.currval from dual;
      

      and you will see the short-circuit

      Comment by Matthias Rogel — January 3, 2014 @ 8:29 am BST Jan 3,2014 | Reply

      • Matthias,

        Cunning – and you could do the same with a simple pl/sql function in earlier versions – but not a proper emulation; check the result from this:

        
        with function tmp_seq_nextval return number is begin return tmp_seq.nextval; end;
        select tmp_seq_nextval(), tmp_seq_nextval() from dual
        /
        
        

        Comment by Jonathan Lewis — January 3, 2014 @ 8:48 am BST Jan 3,2014 | Reply

  3. The type conversion trap can be evaded thusly (noting that the ‘0’ literal can be the any string that will easily implicitly type converts and thus be a fine stand in for any non-null, thereby never matching null for the decode search, which thus falls to the default). I have not done performance testing versus coalesce, but I’d suppose any difference is less than significant if the point is to avoid an expensive clause by short circuiting. (Of course if the expensive nvl result will not type convert to the type of n1 then either form should fail, but that should not be a problem with existing working though suboptimal code.)

    select 
       decode(n1,nvl(n1,'0'),n1,(select max(object_id) from all_objects))   nn
    from
        t1
    

    Comment by rsiz — January 2, 2014 @ 8:39 pm BST Jan 2,2014 | 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,910 other followers