NULL's vs NOT NULL's and Performance

When it comes to giving the cost based optimiser the best possible chance to make the "right" decisions, many DBA's are dilligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes).

However one often neglected area is that the the nullability of columns also impacts the optimiser decisions. NULL and NOT NULL do more than just act as constraints, they also add (or detract) to the value of indexes on those columns.

The following PL/SQL procedure deduces a 'number of rows that are null' count for all columns that are indexed for all tables in the schema passed, although you can pass a table name to restrict the procedure to just that table. For any column that contains no nulls, the appropriate 'alter table ... ( column_name not null)' command will be output (make sure you set serveroutput nice and high).


create or replace 
procedure check_indexed_columns_for_null(
                 p_schema varchar2, 
                 p_table_name varchar2 default null) is
  cursor x is
     select distinct a.table_name, a.column_name, b.colcount
     from all_ind_columns a, 
     ( select table_name , count(distinct column_name) colcount
       from all_ind_columns
       where index_owner = p_schema
       group by table_name ) b
     where a.index_owner = p_schema
     and b.table_name = a.table_name
     and a.table_name = nvl(upper(p_table_name),a.table_name);

  str0 varchar2(32767); 
  str1 varchar2(32767); 
  str2 varchar2(32767); 
  str3 varchar2(32767);

  prev varchar2(100) := '*';
  cnt number;
  trailer varchar2(5);

procedure do_sql(thesql varchar2) is
  tcursor integer;
  dummy integer;
  tcursor := dbms_sql.open_cursor;
  dummy := dbms_sql.execute(tcursor);

for i in x loop
  if prev != i.table_name then
    str0 := 'declare ';
    str1 := 'begin select '; str2 := ' into ';
    str3 := ' '; cnt := 1;
  end if;
  if cnt = i.colcount then 
    trailer := ' ';
    trailer := ','||chr(10);
  end if;
  str0 := str0 || 'v'||ltrim(cnt)||' number;';
  str1 := str1 || 'sum(decode('||i.column_name||',null,1,0))'||trailer;
  str2 := str2 || 'v'||ltrim(cnt)||trailer;
  str3 := str3 || 'if v'||ltrim(cnt)||' = 0 then '||
    'dbms_output.put_line(''alter table '||p_schema||'.'||i.table_name||
    ' modify ('||i.column_name||' not null);''); end if;'||chr(10);
  if cnt = i.colcount then
    str2 := str2 ||' from '||p_schema||'.'||i.table_name||';';
    str3 := str3 ||' end;';
    do_sql(str0||' '||str1||' '||str2||' '||str3);
  end if;
  prev := i.table_name;
  cnt := cnt + 1;
end loop;