Why prefer COALESCE over NVL


I prefer using "COALESCE" over "NVL" is some of the scenarios. Last week One of my friend asked me what is the advantage of using "COALESCE" where we can simply use "NVL". I simply gave him the reply from Oracle Docs i.e. NVL lets you replace null (returned as a blank) with a string in the results of a query and COALESCE returns the first non-null expr in the expression list. Oracle Database uses short-circuit evaluation with "COALESCE".

He replied that he knows the difference, he knows that "COALESCE" can take multiple arguments and so on. He was more interested in understanding "short-circuit evaluation" of "COALESCE" and the scenarios where I prefer using "COALESCE" over "NVL".

To make him understand I created following function which takes One second in every execution and simply returns '--null--' and executed 2 very similar queries one with NVL and other with COALESCE.

SQL> create or replace function f_null return varchar2
  2  is
  3  begin
  4     dbms_lock.sleep(1);
  5     return '--null--';
  6  end;
  7  /

Function created.

Following are the queries which I used to demonstrate that NVL evaluates both arguments even if the second argument is not used and COALESCE uses short-circuit evaluation i.e. it only evaluates the arguments only if they are needed.

SQL> select e.empno, e.ename ename, nvl(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:14.01

SQL> select e.empno, e.ename ename, coalesce(m.ename,f_null) mname
  2  from scott.emp e, scott.emp m
  3  where e.mgr = m.empno(+);

     EMPNO ENAME      MNAME
---------- ---------- ----------
      7902 FORD       JONES
      7788 SCOTT      JONES
      7900 JAMES      BLAKE
      7844 TURNER     BLAKE
      7654 MARTIN     BLAKE
      7521 WARD       BLAKE
      7499 ALLEN      BLAKE
      7934 MILLER     CLARK
      7876 ADAMS      SCOTT
      7782 CLARK      KING
      7698 BLAKE      KING
      7566 JONES      KING
      7369 SMITH      FORD
      7839 KING       --null--

14 rows selected.

Elapsed: 00:00:01.01

Here we can see easily that the first query with NVL took 14+ seconds, one second for each record even if the "f_null" value was used only in one record. On the contrary as mentioned in Oracle Documentation "COALESCE" uses its "short-circuit evaluation" and "f_null" was called only once and so second query took only One Second. "COALESCE" is certainly use less resources than NVL.

Hope you have enjoyed this little article on "COALESCE" and its big advantage called "short-circuit evaluation". Feedbacks are always welcome :)

Related Posts:
- Why Primary Key Foreign Key Relationship and Join Elimination
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- Why my distinct query is not using the Index?
- Avoiding unnecessary function calls to optimize SQL statements
- How to Get Execution Plan and Statistics of Query
- Query optimization tips for Oracle
- Append String to CLOB in Optimized way
- Using TKPROF with simple steps

31 comments:

  1. Great. This is a very helpful post.

    Thanks Nimish.

    ReplyDelete
    Replies
    1. That's very interesting. We have many querys with a NVL. When I have to change a query I will certainly change that too.
      Thnx

      Delete
    2. Now, do a million row test and find out which is faster. Also do the million row test with mixed datatypes in both and see which is faster. I know what the results will be in SQL Server but not in Oracle. One of us is in for a surprise. ;-)

      Delete
    3. Jeff,
      Let's assume 1% of 1,000,000 rows will make call to the function, So, it will take around 2 hours to complete query. Do you think people should follow your suggestion and spend 2 hours to find out something that you could lay down here?


      Hayk Mkrtchyan
      SQL/DB Developer at Clear Vision Information Systems

      Delete
  2. Yes, the behaviour of NVL is non intuitive and rather old fashioned regarding efficiency.
    But Oracle can not change it, because users may use the "old" behaviour (not short-circuiting).
    But it's so convenient, just those 3 well known letters NVL...

    ReplyDelete
  3. Hi Nimish,


    What you showed hire is completely correct and is also the case if instead "coalesce(m.ename,f_null)" one would use "DECODE(m.ename, NULL, f_null, m.ename)". Both COALSESCE and DECODE are clever not to evaluate all values of m.ename and run the function only when m.ename is NULL. On the other hand NVL runs function before evaluation of weather m.ename is NULL or some other value, and replace e.name with function result when it is really NULL.

    Regards,
    Djerdj Lukač

    ReplyDelete
  4. When I read this, my first thought was, this can't be right.

    Then I thought, there must be some subtle difference between NVL and COALESCE. Indeed, the two parameters in NVL can be two different datatyhpes, while COALESCE is more restrictive. So, I spent half an hour trying to figure out how that could make a difference.

    When I cam up empty I decided that this must have been tested on a 10i database which didn't have function caching. So I repeated the test. tried everything I could think of.

    My conclusion is that the internal code for NVL hasn't been updated this century. Thanks for pointing this out to me. Going forward, my motto is No More NVLs!!!


    Phillip Singer, OCP
    Database designer, developer, DBA. and PL/SQL expert.

    ReplyDelete
  5. Right, and interesting, thanks.

    I would add that NVL could make an implicit data type conversion, whilst COALESCE raise an error if data type is inconsistent, even if there is no data evaluation, i.e. if you use "nvl(m.ename,to_date('2014', 'yyyy')) mname" with empno 7839 in where condition, or "coalesce(m.ename,to_date('2014', 'yyyy')) mname", even with empno 7369.

    Another good reason to write explicit data type conversions!


    Nicola Amerio
    Senior Consultant at Scai S.p.A.

    ReplyDelete
  6. Just tested this on 12c, and the same behavior applies.

    I'll be letting my DBA OCP students at Austin Community College know about this.

    Rich

    ReplyDelete
  7. Very clear! Excellent reason to use coalesce!

    When we use, most of the time, a hard coded value with the NVL the results are not so different. But it's really good to keep in mind when we are using functions to determine the non null value.


    Valfrid-ly Couto
    System Analyst at ePharma

    ReplyDelete
  8. The conclusion is not entirely correct. Rather than claiming that COALESCE uses less resources than NVL, the correct answer would be that the parser evaluates and executes COALESCE more efficiently than NVL when used inside a function.

    ReplyDelete
  9. I normally use COALESCE as it is available in Oracle, DB2 and SQL Server. I wasn't aware that there is a difference in how NVL and COALESCE are handled in Oracle. Always good to learn something new, thanks for sharing.


    John McNeilly
    DB2 and Oracle Application Specialist

    ReplyDelete
  10. In addition, COALESCE is ANSI SQL standard and NVL is not. So, it is easier when you migrate the code from one db to another one.


    Cristian Dragoste
    Oracle DBA and Java

    ReplyDelete
  11. The two functions should not be compared - Coalesce and NVL functions are used for different purposes. - Coalesce can take multiple arguments, NVL take only two arguments.
    NVL always evaluates both arguments.
    COALESCE doesn't evaluate anything after it finds the first non-NULL argument.
    Did you run it multple times and get those numbers consistently specially on huge set of records?


    Vineet Rai
    Senior Associate at Cognizant Technology Solutions

    ReplyDelete
  12. I had to clear that out, so tried two same scripts one for NVL and another COALESCE (never used by me before) the findings were as follows:
    1. NVL()

    238550 rows selected.

    Elapsed: 00:00:31.30
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    2. COALESCE()
    238550 rows selected.

    Elapsed: 00:00:27.49
    SQL> exit
    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    I did not get an impression that COALESCE might be a "silver bullet" for any wrong done by NVL()...I believe it could improve performances, but that depends on the query being executed.


    Felix Laventman
    Senior Technical Consultant at Galam Ltd.

    ReplyDelete
  13. Thanks to all for new knowledge about such ancient and well-known function (NVL)! As for me I would be conservative to switch to COALESCE in every situation. I will remember to try it when the query uses NVL and extremely slow. In other cases COALESCE couldn't help.

    Sergey Logichev
    Database Expert

    ReplyDelete
  14. For me, NVL for two columns, NVL2 for three columns and coalesce for any number of columns (well, did not really test the limit but for so many years with so many SQL statements I have never reached any limits).


    Ben Gong
    Ramsey County

    ReplyDelete
  15. Hi I have one doubt when we say nvl evaluates both the argument and COALESCE evaluates only one argument. Here what does the evaluates means?

    ReplyDelete
  16. What's up, I wish for to subscribe for this weblog to obtain most recent updates,
    so where can i do it please assist.

    ReplyDelete
  17. Simple test to find Difference between NVL and coalesce:

    1) Coalesce will return the first non null item in its parameter list
    Whereas nvl only takes two parameters and returns the first if it is not null, otherwise it returns the second.

    SELECT SUM(val)
    FROM (
    SELECT NVL(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
    FROM dual
    CONNECT BY
    level <= 10000
    );

    O/p requires====0.031 seconds

    SELECT SUM(val)
    FROM (
    SELECT COALESCE(1, LENGTH(RAWTOHEX(SYS_GUID()))) AS val
    FROM dual
    CONNECT BY
    level <= 10000
    ) ;
    O/p requires====0.015 seconds

    ReplyDelete
  18. This is a topic that's near to my heart... Cheers! Where are your contact details though?

    ReplyDelete
  19. What's up, yup this article is really pleasant and I have learned lot of things from
    it on the topic of blogging. thanks.

    ReplyDelete
  20. Thank you for helpig out, good information.

    ReplyDelete
  21. Pretty nice post. I just stumbled upon your weblog and wanted to say that I have really enjoyed browsing your blog posts.
    After all I'll be subscribing to your feed and I hope you write
    again soon!

    ReplyDelete
  22. Marvelous, what a weblog it is! This web site gives helpful facts to us, keep it up.

    ReplyDelete
  23. Wondering if caching causes this behavior. Running same query without changing where clause will be influenced by caching and second time will always work fast.

    ReplyDelete
  24. Hi Nimish,
    just took help of your article
    http://nimishgarg.blogspot.com/2016/03/avoiding-unnecessary-function-calls-to.html

    22:25:29 SQL> select e.empno, e.ename ename, nvl(m.ename,(select scott.f_null from dual)) mname
    22:25:29 2 from scott.emp e, scott.emp m
    22:25:29 3 where e.mgr = m.empno(+);

    EMPNO ENAME MNAME
    ---------- ---------- --------------------
    7902 FORD JONES
    7788 SCOTT JONES
    7900 JAMES BLAKE
    7844 TURNER BLAKE
    7654 MARTIN BLAKE
    7521 WARD BLAKE
    7499 ALLEN BLAKE
    7934 MILLER CLARK
    7876 ADAMS SCOTT
    7782 CLARK KING
    7698 BLAKE KING
    7566 JONES KING
    7369 SMITH FORD
    7839 KING --null--

    14 rows selected.

    Elapsed: 00:00:01.03
    22:25:30 SQL>

    ReplyDelete
  25. Very nice and useful one, thank you!

    ReplyDelete
  26. Awesome explanation of COALESCE Vs NVL. Thanks

    ReplyDelete