CODEWITHSUNDEEP
sqloracle-databaseplsql
Yenier Torres
Yenier Torres

Reputation: 748

Count the number of null values into an Oracle table?

I need to count the number of null values of all the columns in a table in Oracle.

For instance, I execute the following statements to create a table TEST and insert data.

 CREATE TABLE TEST
 (  A VARCHAR2(20 BYTE), 
    B VARCHAR2(20 BYTE), 
    C VARCHAR2(20 BYTE)
  );
Insert into TEST (A) values ('a');
Insert into TEST (B) values ('b');
Insert into TEST (C) values ('c');

Now, I write the following code to compute the number of null values in the table TEST:

declare 
cnt number :=0;
temp number :=0;
begin
  for r in ( select column_name, data_type
             from    user_tab_columns 
             where table_name = upper('test')
             order by column_id )
  loop
      if r.data_type <> 'NOT NULL' then
         select count(*) into temp FROM TEST where r.column_name IS NULL;
         cnt := cnt + temp;
      END IF;
  end loop;
   dbms_output.put_line('Total: '||cnt);
end;
/

It returns 0, when the expected value is 6.

Where is the error?

Thanks in advance.

Upvotes: 0

Views: 2444

Answers (5)

Jon Heller
Jon Heller

Reputation: 36817

Use the data dictionary to find the number of NULL values almost instantly:

select sum(num_nulls) sum_num_nulls
from all_tab_columns
where owner = user
    and table_name = 'TEST';

SUM_NUM_NULLS
-------------
6

The values will only be correct if optimizer statistics were gathered recently and if they were gathered with the default value for the sample size.

Those may seem like large caveats but it's worth becoming familiar with your database's statistics gathering process anyway. If your database is not automatically gathering statistics or if your database is not using the default sample size those are likely huge problems you need to be aware of.

To manually gather stats for a specific table a statement like this will work:

begin
    dbms_stats.gather_table_stats(user, 'TEST');
end;
/

Upvotes: 0

abrittaf
abrittaf

Reputation: 537

Counting NULLs for each column

In order to count NULL values for all columns of a table T you could run

SELECT COUNT(*) - COUNT(col1) col1_nulls
     , COUNT(*) - COUNT(col2) col2_nulls
     ,..
     , COUNT(*) - COUNT(colN) colN_nulls
     , COUNT(*) total_rows
FROM   T
/

Where col1, col2, .., colN should be replaced with actual names of columns of T table.

Aggregate functions -like COUNT()- ignore NULL values, so COUNT(*) - COUNT(col) will give you how many nulls for each column.

Summarize all NULLs of a table

If you want to know how many fields are NULL, I mean every NULL of every record you can

WITH d as (    
    SELECT COUNT(*) - COUNT(col1) col1_nulls
         , COUNT(*) - COUNT(col2) col2_nulls
         ,..
         , COUNT(*) - COUNT(colN) colN_nulls
         , COUNT(*) total_rows
    FROM   T
) SELECT col1_nulls + col1_nulls +..+ colN_null
  FROM d 
/

Summarize all NULLs of a table (using Oracle dictionary tables)

Following is an improvement in which you need to now nothing but table name and it is very easy to code a function based on it

DECLARE
  T    VARCHAR2(64) := '<YOUR TABLE NAME>';
  expr VARCHAR2(32767);
  q    INTEGER;
BEGIN
  SELECT 'SELECT /*+FULL(T) PARALLEL(T)*/' || COUNT(*) || ' * COUNT(*) OVER () - ' || LISTAGG('COUNT(' || COLUMN_NAME || ')', ' + ') WITHIN GROUP (ORDER BY COLUMN_ID) || ' FROM ' || T
  INTO   expr
  FROM   USER_TAB_COLUMNS
  WHERE  TABLE_NAME = T;

  -- This line is for debugging purposes only
  DBMS_OUTPUT.PUT_LINE(expr);

  EXECUTE IMMEDIATE expr INTO q;

  DBMS_OUTPUT.PUT_LINE(q);
END;
/

Due to calculation implies a full table scan, code produced in expr variable was optimized for parallel running.

User defined function null_fields

Function version, also includes an optional parameter to be able to run on other schemas.

CREATE OR REPLACE FUNCTION null_fields(table_name IN VARCHAR2, owner IN VARCHAR2 DEFAULT USER)
  RETURN INTEGER IS
  T    VARCHAR2(64) := UPPER(table_name);
  o    VARCHAR2(64) := UPPER(owner);
  expr VARCHAR2(32767);
  q    INTEGER;
BEGIN
  SELECT 'SELECT /*+FULL(T) PARALLEL(T)*/' || COUNT(*) || ' * COUNT(*) OVER () - ' || listagg('COUNT(' || column_name || ')', ' + ') WITHIN GROUP (ORDER BY column_id) || ' FROM ' || o || '.' || T || ' t'
  INTO   expr
  FROM   all_tab_columns
  WHERE  table_name = T;

  EXECUTE IMMEDIATE expr INTO q;

  RETURN q;
END;
/

-- Usage 1
SELECT null_fields('<your table name>') FROM dual
/

-- Usage 2
SELECT null_fields('<your table name>', '<table owner>') FROM dual
/

Upvotes: 2

miracle173
miracle173

Reputation: 1973

The dynamic SQL you execute (this is the string used in EXECUTE IMMEDIATE) should be 

select sum(
    decode(a,null,1,0)
    +decode(b,null,1,0)
    +decode(c,null,1,0)
    ) nullcols
from test;

Where each summand corresponds to a NOT NULL column.

Here only one table scan is necessary to get the result.

Upvotes: 0

Gurdyal
Gurdyal

Reputation: 301

select COUNT(1) TOTAL from table where COLUMN is NULL;

Upvotes: -1

Yenier Torres
Yenier Torres

Reputation: 748

Thank you @Lord Peter :

The below PL/SQL script works

declare 
cnt number :=0;
temp number :=0;
begin
  for r in ( select column_name, nullable
             from    user_tab_columns 
             where table_name = upper('test')
             order by column_id )
  loop
      if r.nullable = 'Y' then
         EXECUTE IMMEDIATE 'SELECT count(*) FROM test where '|| r.column_name ||' IS NULL' into temp ;
         cnt := cnt + temp;
      END IF;
  end loop;
   dbms_output.put_line('Total: '||cnt);
end;
/

The table name test may be replaced the name of table of your interest.

I hope this solution is useful!

Upvotes: 0

Related Questions