Jpdg Jpdg - 10 months ago 36
SQL Question

PLSQL oracle - what's more performant? NVL() or IS NULL?

I need to migrate 4 millions of records.

In the validator, to check if a column is NULL, what's more performant?

WHERE name IS NULL;


or

WHERE NVL(name,' ') = ' '

Answer Source

On my machine for a table with 3689150 rows:

create table t234 as
select editionable as name
from all_objects
cross join (
  select 1 from dual
  connect by level <= 50
);
select count(*) from t234;

  COUNT(*)
----------
   3689150

the first one is much more faster:

set timing on
select count(*) from t234 where name is null;

  COUNT(*)
----------
   1612400

Elapsed: 00:00:00.094

select count(*) from t234 where NVL(name,' ') = ' ';

  COUNT(*)
----------
   1612400

Elapsed: 00:00:00.203

The first query takes 94 miliseconds, the second one 203 miliseconds,
so the first one is over 100% faster - you will save over 100 miliseconds using NOT NULL operator instead of NVL(name,' ') = ' '

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download