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;
WHERE NVL(name,' ') = ' '
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,' ') = ' '