Something About NULL

scott@9iR2> select * from product_component_version;

PRODUCT                        VERSION    STATUS
------------------------------ ---------- ---------------
NLSRTL                         9.2.0.8.0  Production
Oracle9i Enterprise Edition    9.2.0.8.0  Production
PL/SQL                         9.2.0.8.0  Production
TNS for 32-bit Windows:        9.2.0.8.0  Production  

I will say that I seriously wish the default state of a database column was NOT NULL – that is, instead of columns by default being NULLABLE, and therefore we would have to go out of our way to make them NULLABLE. most columns are in fact NOT NULL – and we just “forget” to state the obvious. This leads to indexes that sometimes cannot be used for a particular class of questions. For example:

scott@9iR2> create table T as
  2  select *
  3  from dba_objects
  4  where object_id is not null;

Table created.

scott@9iR2> create unique index t_ind on t(object_id);

Index created.

scott@9iR2> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',cascade=>TRUE);

PL/SQL procedure successfully completed.

scott@9iR2> set autotrace traceonly explain;
scott@9iR2> select count(*) from T;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=46 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=46 Card=33712)

The Optimizer could just use these index to count these rows, and the index is small – why isn’t the database doing that?”. The database will steadfastly refuse to use the index to count the rows in this case. The reason – it is entirely possible that an index key entry in the index we created could have an entirely NULL entry – and hence not every row in the table is represented in the index!

But the reality of the situation is that OBJECT_ID is in fact “NOT NULL” and all we need to do is tell the database this fact. And when we do – good things happen
 
scott@9iR2> alter table t modify object_id not null;

Table altered.

scott@9iR2> select count(*) from T;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T_IND' (UNIQUE) (Cost=8 Card=33712)

In fact, the optimizer makes use of constraints all of the time (the cost base optimizer mostly, the rule based optimizer is pretty much brain dead in this respect:

scott@9iR2> select /*+ rule */ count(*) from T;
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'T'