Skip to Main Content
  • Questions
  • What performs better NVL or DECODE for evaluating NULL values

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: January 07, 2014 - 7:37 pm UTC

Answered by: Tom Kyte - Last updated: September 29, 2017 - 9:36 pm UTC

Category: Developer - Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Afternoon,

Could anyone tell me which of the following statements would perform better?
SELECT 1
  FROM DUAL
 WHERE NVL (NULL, '-1') = NVL (NULL, '-1')

OR
SELECT 1
  FROM DUAL
 WHERE DECODE(NULL, NULL, '1', '0') = '1'

Both will evaluate nulls as being equal. I was just curious which would perform better as I have to have about 30 of these statements together in a case statement. In my statements the two nulls represent different columns. Right now the decode to me seems to be the better choice considering it rules out the possibility of a replaced '-1', or whatever value I choose to replace null with, being compared as equal to an actual '-1'.

Any other thoughts are always welcome.

and we said...

you cannot use such simple examples - if you attempt to test those, you'd find the optimizer out smarts you:

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> SELECT 1
  2    FROM DUAL
  3   WHERE NVL (NULL, '-1') = NVL (NULL, '-1')
  4  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> SELECT 1
  2    FROM DUAL
  3   WHERE DECODE(NULL, NULL, '1', '0') = '1'
  4  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

ops$tkyte%ORA11GR2> set autotrace off



so, we have to use something more "real"


so, here is more "real"




ops$tkyte%ORA11GR2> create table t as select subobject_name, object_type from all_objects;

Table created.

ops$tkyte%ORA11GR2> insert into t select * from t;

73000 rows created.

ops$tkyte%ORA11GR2> insert into t select * from t;

146000 rows created.

ops$tkyte%ORA11GR2> insert into t select * from t;

292000 rows created.

ops$tkyte%ORA11GR2> insert into t select * from t;

584000 rows created.

ops$tkyte%ORA11GR2> insert into t select * from t;

1168000 rows created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> @trace
ops$tkyte%ORA11GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR2> select 1 from t where nvl(subobject_name,'-1') = nvl( object_type, '-1' );

no rows selected

ops$tkyte%ORA11GR2> select 1 from t where decode( subobject_name, object_type, '1', '0' ) = '1';

no rows selected



we'll compare the two approaches for a few million rows... but wait, let me add another:

ops$tkyte%ORA11GR2> select 1 from t where (subobject_name = object_type or (subobject_name is null AND object_type is null));

no rows selected




a tad more verbose, but hey - we are coders, that is what we are paid to do.... write code :)

and tkprof says:


select 1
from
 t where nvl(subobject_name,'-1') = nvl( object_type, '-1' )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.29       0.29       1482       4895          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.29       0.29       1482       4895          1           0
********************************************************************************
select 1
from
 t where decode( subobject_name, object_type, '1', '0' ) = '1'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.20       0.20         57       4895          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.20       0.20         57       4895          1           0
********************************************************************************
select 1
from
 t where (subobject_name = object_type or (subobject_name is null AND
  object_type is null))


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.11       0.11          3       4895          1           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.11       0.11          3       4895          1           0



#3 seems best - AND you don't have to come up with some "fake value" - no -1's or aything like that. works with all data...

and you rated our response

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Exactly what I was looking for.

January 08, 2014 - 11:59 pm UTC

Reviewer: Peter B from Norfolk, VA

Thank you for looking into this for me. You went above and beyond and answered my question perfectly. Sorry about not giving a real world example of what I was performing. I just wanted to keep the concept simple.


either or

January 09, 2014 - 2:58 pm UTC

Reviewer: Jim from Phila, PA

so it appears that decode is faster (#2 versus #1)
unless you can recast the test to avoid needing NVL or decode (option #3) in many cases.
Tom Kyte

Followup  

January 09, 2014 - 7:30 pm UTC

#3 is the clear winner

#2 is second

#1 is last

The reverse...

January 10, 2014 - 10:46 am UTC

Reviewer: Michael from Germany

If want to find out differences - while considering two NULLs as equal - #3 is also the winner (at least according to my tests).

In this (reverse) case #3 looks like:
select 1
from   t
where  (object_name != subobject_name or 
        (object_name is null and subobject_name is not null) or
        (object_name is not null and subobject_name is null))

But, i have to admint, #2 is quite briefer:
select 1
from   t
where  decode(object_name, subobject_name, 1, 0) = 0

Perhaps it would be a good idea that Oracle adds a special EQUALS function (or NULLEQ or whatever name ...) to SQL and PL/SQL in Oracle 12cR2 that considers NULLs as equal and returns either true or false (never NULL).

Then we could easily write:
select 1
from   t
where  equals(object_name, subobject_name)

Or for differences:
select 1
from   t
where  not equals(object_name, subobject_name)

How about Join Plans - NL vs HJ

September 29, 2017 - 5:49 pm UTC

Reviewer: Deb from Cary, NC, USA

Doesn't OR clause not result in Nested Loops instead of Hash Join?

In case of large table - 100's of millions of records in Exadata resulting in Nested Loop for OR clause as compared to NVL or DECODE.
Connor McDonald

Followup  

September 29, 2017 - 9:36 pm UTC

It may...or it may not. That is a *costed* decision by the optimizer.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here