Not NULL

Here’s a little detail that I’ve known for years – but keep forgetting until I embarrass myself by rediscovering it (usually in front of a client). I’ll start with a demonstration of a useful little feature of mandatory columns:

drop table t1 purge;

create table t1
as
select
	*
from
	all_objects
where
	rownum <= 10000
;

execute dbms_stats.gather_table_stats(user,'t1')

create index t1_i1 on t1(object_name);

set autotrace traceonly explain

select count(*) from t1;

/*

--------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time
--------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13   (0)| 00:00:0
|   1 |  SORT AGGREGATE       |       |     1 |            |
|   2 |   INDEX FAST FULL SCAN| T1_I1 | 10000 |    13   (0)| 00:00:0
--------------------------------------------------------------------

*/

Oracle can use the index on column object_name to count the number of rows in the table because the column has been declared NOT NULL, so every row in the table also has to appear in the index. Let’s just demonstrate that by changing the column definition:

alter table t1 modify object_name null;
select count(*) from t1;

/*

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    40   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    40   (0)| 00:00:01 |
-------------------------------------------------------------------

*/

Now let’s make the column mandatory again – by adding a constraint:

alter table t1 add constraint t1_ck_on_nn check(object_name is not null);
select count(*) from t1;

What do you think the execution path is going to be ?

Oracle uses a full table scan – even if you supply the optimizer with an index hint.

The special NOT NULL optimisation only applies if the column is declared not null – an ordinary constraint isn’t just good enough.

You may remember a short note I wrote a few days ago about adding a not null constraint to a table with 200 million rows without causing the system to stop for a few minutes. I did it with the intent of giving the optimizer a few extra options for exection plans – but now I need to go back and see whether or not the constraint has any effect at all on any of the plans we generate.

Update 6th Sept 2010

Some of the early comments have highlighted the importance of being very careful with language when describing a problem. In this case I haven’t made the distinction between an “ordinary constraint” and a “not null declaration” clear enough.  To ensure that a column does not allow nulls you can add a check constraint, or you can declare it “not null” – which, in effect, is a special type of constraint. When you declare a column to be not null Oracle will create a check constraint for that column, but also set the null$ column in sys.col$ to be non-zero.

A quick cut and paste job to show the differences (in 11.1)

SQL> alter table t1 modify n1 not null;

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                      NOT NULL NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL> alter table t1 modify n1 null;

Table altered.

SQL> alter table t1 add constraint t1_ck_n1_nn check(n1 is not null);

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                               NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL> alter table t1 drop constraint t1_ck_n1_nn;

Table altered.

SQL> alter table t1 modify n1 not null enable novalidate;

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                               NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL

I haven’t queried user_constraints to show it but in all these cases there will be a check constraint with the search_condition: “N1″ IS NOT NULL. As you can see, though, the only time that the describe command shows the column as NOT NULL (and col$.null$ becomes non-zero) is when you use the not null declaration with the implicit attributes of enable, validate.

It’s only this declaration/setting of null$ that allows the optimizer to do its special optimisation. You might have thought that the optimizer could be programmed to read the constraint information to look for a “check column is not null” constraint to do the same trick but (at present) it simply doesn’t work that way.