Something about nothing...

Something about nothing. A frequently misunderstood concept with NULLS and indexes is the myth that NULL values are not tracked in “normal” indexes such as a conventional B*Tree index. Therefore, many people conclude erroneously that “WHERE COLUMN IS NULL” will not use, in fact cannot, use an index.

The facts are:

• Entirely NULL keys are not entered into a ‘normal’ B*Tree in Oracle


• Therefore, if you have a concatenated index on say C1 and C2, then you will likely find NULL values in it – since you could have a row where C1 is NULL but C2 is NOT NULL – that key value will be in the index.

As long as you have some not null column in your set of indexed columns, you will find NULLs in the index. Additionally – you always have a NOT NULL (virtual) column to use no matter what!

First, consider this example, supposed we want to leave OBJECT_ID as NULLABLE, but did have a column OWNER that was NOT NULL:

ops$tkyte@ORA9IR2> create table t
2  as
3  select object_id, owner, object_name
4    from dba_objects;
Table created.

ops$tkyte@ORA9IR2> alter table t modify (owner NOT NULL);
Table altered.

ops$tkyte@ORA9IR2> create index t_idx on t(object_id,owner);
Index created.

ops$tkyte@ORA9IR2> desc t
Name                    Null?    Type
----------------------- -------- ----------------
OBJECT_ID                        NUMBER
OWNER                   NOT NULL VARCHAR2(30)
OBJECT_NAME                      VARCHAR2(128)

ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.

Well, that index can certainly be used to satisfy “IS NOT NULL” when applied to OBJECT_ID:

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id is null;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34)
2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1)

In fact – even if the table did not have any NOT NULL columns, or we didn’t want/need to have a concatenated index involving OWNER – there is a transparent way to find the NULL OBJECT_ID values rather easily:

ops$tkyte@ORA9IR2> drop index t_idx;
Index dropped.

ops$tkyte@ORA9IR2> create index t_idx_new on t(object_id,0);
Index created.

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_id is null;

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=34)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=34)
2    1     INDEX (RANGE SCAN) OF 'T_IDX_NEW' (NON-UNIQUE) (Cost=2 Card=1)

The “secret sauce” here is to create what is known as a function based index (although some would argue there is no such thing! I detect a Jonathan Lewis write up there…). We simply added “0” to the index – we can search on OBJECT_ID as before, plus find all records where “OBJECT_ID IS NULL” without changing a thing.

I have used (and will continue to use!) the fact that entirely null entries are not made in B*Tree indexes as a positive thing (selective uniqueness for example – ensure that “project name is unique for all active projects” can be enforced via: create unique index proj_name_unique on t(case when status = ‘ACTIVE’ then project_name end ); - that uniquely indexes only the STATUS=’ACTIVE’ records..). But here, we can use the “reverse” of that trick to ensure we never index an entirely NULL key!