2010-01-21

Potential not nulls

Something to send to a development team of the model or just run the clauses from these generators. Here are ways to figure out potential missing not null constraints.

First a test table



create table nnc(n number, m number);

insert into nnc values (1,2);

commit;




By counting the actual rows



select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));'
from user_tab_columns
where nullable='Y'
order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;



Or another way by using stats



exec dbms_stats.gather_table_stats(user,'NNC');

select t.table_name, c.column_name, t.num_rows
from user_tab_cols c
inner join user_tables t
on t.table_name=c.table_name
where c.nullable='Y'
and t.temporary='N'
and num_rows > 0
and num_nulls = 0
order by t.table_name,c.column_name
;


select '--alter table '||t.table_name||' modify '||c.column_name||' not null;'
from user_tab_cols c
inner join user_tables t
on t.table_name=c.table_name
where c.nullable='Y'
and t.temporary='N'
and num_rows > 0
and num_nulls = 0
order by t.table_name,c.column_name
;


And the thirdth option putting those together. Candidates from stats and after that checking the actual data.



select 'select ''--alter table '||table_name||' modify '||column_name||' not null;'' c from ( select count(*) from '||table_name||' having count(*)>0 and count(*) = sum(case when '||column_name||' is not null then 1 end));'
from user_tab_columns
where nullable='Y'
and table_name in (
select t.table_name
from user_tab_cols c
inner join user_tables t
on t.table_name=c.table_name
where c.nullable='Y'
and t.temporary='N'
and num_rows > 0
and num_nulls = 0
)
order by table_name,column_name
;

select '--alter table NNC modify M not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when M is not null then 1 end));

select '--alter table NNC modify N not null;' c from ( select count(*) from NNC having count(*)>0 and count(*) = sum(case when N is not null then 1 end));

--alter table NNC modify M not null;

--alter table NNC modify N not null;




Well maybe the stats version is the one to use.

I prefer having names also to the not null constraints. So after dealing with the developer



alter table NNC modify M constraint nnc_m_nn not null;

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.