Jonathan Lewis just published a blog post about NVL and COALESCE and the optimizer costings for each.
There is also perhaps a significant difference between NVL and COALESCE in that the former seems to have an in-built optimization for handling bind variables and nulls.
Consider an application where users optionally pass in search criteria and you have to query a table based on that criteria. You have three natural choices here to implement that:
WHERE column = :search_criteria or :search_criteria is null
or
WHERE column = nvl(:search_criteria ,column)
or
WHERE column = coalesce(:search_criteria,column)
Functionally they are identical*, but the implementation detail shows a nice little optimizer trick that only works with NVL.
SQL> create table t as select * From dba_objects;
Table created.
SQL> variable search_criteria number
SQL>
SQL> exec :search_criteria := 123
PL/SQL procedure successfully completed.
SQL>
SQL> create index ix1 on t ( object_id ) ;
Index created.
SQL>
SQL> set feedback only
SQL> select *
2 from t
3 where object_id = nvl(:search_criteria,object_id);
1 row selected.
SQL>
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID 0g820t1jw00hm, child number 0
-------------------------------------
select * from t where object_id = nvl(:search_criteria,object_id)
Plan hash value: 2258578794
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 430 (100)| |
| 1 | VIEW | VW_ORE_1B35BA0F | 78868 | 36M| 430 (1)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 132 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IX1 | 1 | | 1 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL | T | 78867 | 9M| 428 (1)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(:SEARCH_CRITERIA IS NOT NULL)
5 - access("OBJECT_ID"=:SEARCH_CRITERIA)
6 - filter(:SEARCH_CRITERIA IS NULL)
7 - filter("OBJECT_ID" IS NOT NULL)
27 rows selected.
SQL>
SQL> set feedback only
SQL> select *
2 from t
3 where object_id = coalesce(:search_criteria,object_id);
1 row selected.
SQL>
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID am3uvm7nvx5d9, child number 0
-------------------------------------
select * from t where object_id = coalesce(:search_criteria,object_id)
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 427 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 132 | 427 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=COALESCE(:SEARCH_CRITERIA,"OBJECT_ID"))
18 rows selected.
SQL>
SQL>
SQL>
SQL> set feedback only
SQL> select *
2 from t
3 where ( object_id = :search_criteria or :search_criteria is null );
1 row selected.
SQL>
SQL> set feedback on
SQL> select * from dbms_xplan.display_cursor();
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------
SQL_ID ff0s2j51scxss, child number 0
-------------------------------------
select * from t where ( object_id = :search_criteria or
:search_criteria is null )
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 427 (100)| |
|* 1 | TABLE ACCESS FULL| T | 3945 | 508K| 427 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:SEARCH_CRITERIA IS NULL OR
"OBJECT_ID"=:SEARCH_CRITERIA))
20 rows selected.
SQL>
Only NVL gets the benefit of the query being “split” into two pieces – one to handle the case where the passed criteria is null, and the other for when the criteria is not null. The FILTER in line 3 shows that we will only run one or the other.
So for these particular types of queries, make sure you test all the possibilities – you might find NVL (currently) is your best bet.
* – Addenda: Thanks to Jonathan for pointing out that you can get discrepancies in the results for the three strategies above for columns that may contain nulls, so as always, take care.