What is the fastest way to find if a column has at least one NULL value in ORACLE database?

Question

select 1 from MYTABLE where MYDATE is null AND ROWNUM = 1;

My tables have so much values, that request never ends…

I try to find if a table has at least one NULL value in a particular column.

Does ROWNUM = 1 makes Oracle to stop at the first valid result? Or will it go thought the whole table? How to make it faster?

Answer 1

If your table has one null value hiding among a billion rows, finding it is usually going to require a full scan of the whole table (stopping when it finds one).

That's true for the answers using various SQL forms, answers based on gathering statistics, and answers based on trying to enforce a NOT NULL constraint on the column.

Once you're using ROWNUM=1 to make sure you stop after the first null value, what you can try to do is:

• Use a /*+ PARALLEL */ hint to make the full scan go faster
• If you've gathered stats on the table using a 100% sample size, you can look in ALL_TAB_COLUMNS.NUM_NULLS. This doesn't get around the need for a full table scan, it just avoids it by using the one that was performed when statistics were gathered. The downside of this approach is that the statistics may not be up-to-date.
• If there is an index on the column of interest AND an index on a NOT NULL column in the same table, you can try to SELECT COUNT(INDEXED_COLUMN_NAME) FROM TABLE and SELECT COUNT(INDEXED_NOT_NULL_COLUMN) FROM TABLE and subtract them. This takes advantage of the fact that null values would not appear in the index, so the counts would be different. This would be faster because the indexes will be smaller and therefore faster to read than the full table.

If this is something you will need to do frequently, you can create a function based index to speed this search up in the future:

CREATE index_name ON table_name ( NVL(column_name, impossible_value_for_column) );

e.g.,

CREATE index_name ON table_name ( NVL(column_name, -99999999) );

Then

SELECT * FROM table_name 
WHERE NVL(column_name, -99999999) = -99999999 
AND column_name IS NULL  -- in case your "impossible value" really wasn't impossible;

Answer 2

EXISTS will return as soon as it finds a NULL value:

select
  case 
    when exists (select 1 from MYTABLE where MYDATE is null) then 1 
    else 0
  end
from dual

Answer 3

If you put a predicate of rownum = 1 on your query, Oracle will stop searching after it's found the first row.

If your query is taking time, it's because there were no rows that matched your other predicates so far.

To speed up your query, assuming your it's a regularly run query, not a one-off query, you could always add an index of (mydate, 1) - that will ensure rows with a null mydate will be stored in an index (note the present of the constant, which ensures that all columns in the index won't be all null).

Answer 4

You can even use to issue a DDL by trying to add a NOT NULL constraint on that individual column through

alter table MYTABLE modify MYDATE not null;

statement.

It will hurl with ORA-02296 cannot enable(<schema_name>.)-null values found even if one null record found for that column.

In my opinion, if it's there's no null values, adding such a constraint would be beneficient to keep the existing situation for data consistency.

Answer 5

You could use data dictionary:

select num_nulls 
from user_tab_columns 
where table_name = 'MYTABLE' and column_name = 'MYDATE'

Have in mind that values in the dictionary are updated only after analyzing the tables, so the returned value might not be up to date. Analyze tables can be forced by:

ANALYZE TABLE MYTABLE COMPUTE STATISTICS FOR TABLE;

or using DBMS_STATS.GATHER_SCHEMA_STATS