Force Oracle to use an index for IS [NOT] NULL predicates.

In my previous post, I mentioned how Oracle can't keep track of NULLS in an index, and consequently, cannot use an index to satisfy the IS NULL predicates (WHERE clauses).

There is a way of making Oracle do that though....

If you are trying to make Oracle use an index-on-nullable-column, here's a nifty trick from a reader-comment at http://www.dba-oracle.com/oracle_tips_null_idx.htm :

SQL> select count(1) from t where n is null;

COUNT(1)
----------
334

Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=334 Bytes=1002)

SQL> create index tind on t(n, 1); ----> here 1 is just any arbitary value.

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select count(1) from t where n is null;

COUNT(1)
----------
334

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TIND' (NON-UNIQUE) (Cost=2 Card=3
34 Bytes=1336)


Sandeep Redkar

Postgres can... Oracle can't (index NULLs)

Well, it was a revelation.... For almost ages, I have been made to believe that NULLs cannot be indexed; primarily because Oracle chose not to, and I had used Oracle quite a lot in the past (and worked for them too at one time).

A couple of days ago I was asked by a developer (Arie) this very question, but in context of Postgres:

Subject: Are nulls indexed?
Body:
If I have an index on column A (null-able) will null values be indexed? Basically, if not, does it mean that such a query "select * from T where A is null" will have to do a full table scan?

I blindly said no... that was until I had to test something out based on his next question. And I was in fact surprised! Postgres can use a non-unique index for 'IS NULL' and 'IS NOT NULL' predicates!

Here's a small session to demonstrate that Postgres stores index-records for NULLs and can use such indexes for IS [NOT] NULL predicates:

postgres=# create table t ( a int, b char(32) );
CREATE TABLE
postgres=# create index t_i on t( a );
CREATE INDEX
postgres=# insert into t select s, s+1 from generate_series( 1, 1000000 ) as s;
analyze t;
INSERT 0 1000000
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%';
relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
--------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..8.38 rows=1 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=# truncate t;
TRUNCATE TABLE
postgres=# insert into t select case s%5 when 0 then null else s end, s+1 from generate_series( 1, 1000000 ) as s;
INSERT 0 1000000
postgres=# reindex index t_i;
REINDEX
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%'; relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..7332.37 rows=198972 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=#

I hope you get the picture.

So, another good reason to choose Postgres (or for that matter EnterpriseDB) over Oracle... :)

PS: This works only with the B-Tree indexes though.