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.
Learning is not a spectator sport 
Leave a reply to Columns referenced in the ON Clause cannot be updated – Java, SQL and jOOQ. – Reporter Inovation Cancel reply