TIP #8 : Index for null columns

Index does not store NULL value, consequently if you have null condition in SQL statement, related index is ignored by default.

create index scott.empidx_sal on scott.emp(sal)
select ename from scott.emp where sal is null;
---> Execution plan is : Full table scan.

Workaround :

- Create function-based index on nvl(sal,-1) and changed the query to select ename from scott.emp where nvl(sal,-1)=-1;

- I prefer this solution. Create composed index. In composed index, as far as the whole columns are not null, index does store null value.
create index scott.empidx_salename on scott.emp(ename,sal);
select ename from scott.emp where sal is null;
---> Execution plan is : Index scan


Anonymous said...

Your solution works, but there's one vital piece of information you forgot to mention: If the first index column is null, the row is excluded from the index.


tchien69 said...

@Anonymous: Not true, only if "all" Index Columns are Null. Per Oracle 11g R2 Create Index docs (http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_5012.htm#i2062403): "Note: Oracle does not index table rows in which all key columns are null except in the case of bitmap indexes. Therefore, if you want an index on all rows of a table, then you must either specify NOT NULL constraints for the index key columns or create a bitmap index."

tchien69 said...

Instead of a Function-Based Index, you can add a non-Nullable Index Column (and that Column could even be just a dummy literal). This has the advantage of not requiring "is Null" Queries to be modified to be "= (Some Arbitrary Value Used in the NVL Function in the Function-Based Index)".

Shervin said...

This is one way as I presented, composite index