funny -- wonder why there always must be "benefits". It seems obvious that if you have a business rule that says "this column MUST not be null", a not null constraint would be called for..

Well, benefits --

o it adds semantic meaning to your data model. You KNOW that the column will always contain a value.

o it removes ambiguity. "find me all of the people who are not clerks". Seems obvious:

select * from emp where job <> 'CLERK';

well, thats wrong

select * from emp where job <> 'CLERK' or job is NULL;

is correct. Many query tools will either
a) get it wrong (wrong answer, that is a negative)
b) get it right -- but therefore get it slower in many cases.

o it allows for access paths that did not otherwise exist. Consider this simple 10,000 row example:

ops$tkyte@ORA920> create table t1 as select * from all_objects where rownum <= 10000;

Table created.

ops$tkyte@ORA920> create table t2 as select * from all_objects where rownum <= 9950;

Table created.

ops$tkyte@ORA920> create index t2_idx on t2(object_id);

Index created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t2 modify object_id null;

Table altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table t1 compute statistics
2 for table
3 for all indexes
4 for all indexed columns;

Table analyzed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> analyze table t2 compute statistics
2 for table
3 for all indexes
4 for all indexed columns;

Table analyzed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter session set sql_trace=true;

Session altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*)
2 from t1 nullable
3 where object_id not in ( select object_id from t2 );

COUNT(*)
----------
50

ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t2 modify object_id not null;

Table altered.

ops$tkyte@ORA920>
ops$tkyte@ORA920> select count(*)
2 from t1 notnull
3 where object_id not in ( select object_id from t2 );

COUNT(*)
----------
50



tkprof says:


select count(*)
from t1 nullable
where object_id not in ( select object_id from t2 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.09 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 16.67 17.68 0 735876 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 16.68 17.78 0 735876 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
50 FILTER
10000 TABLE ACCESS FULL T1
9950 TABLE ACCESS FULL T2
--------------------------------------------------------------------------------
select count(*)
from t1 notnull
where object_id not in ( select object_id from t2 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.06 0 161 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.09 0 161 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
50 HASH JOIN ANTI
10000 TABLE ACCESS FULL OBJ#(45593)
9950 INDEX FULL SCAN OBJ#(45595) (object id 45595)


ahh, the difference a constraint makes!


In short -- the more information you give the database, the better the quality of the plans. Not null impacts the optimizer, query rewrite, even the queries you must type in to get the correct answer.

Every column that is NOT NULLABLE -- should be marked as such.