NULLs not indexed – not allways true

B-tree indexes does not include NULL values. That is the common understanding of how B-tree indexes organize their values. NULL is the absence of a value so it will be not part of the index. If one need to index NULL values, we need to use Oracle’s bitmap indexes. But this is not completely true. Just follow the example:

SQL> create table mytab as
  2  select owner, object_name, object_type from dba_objects;

Table created.

SQL> update mytab set owner=null where owner=user;

22 rows updated.

SQL> commit;

Commit complete.

SQL> create index mytab_owner on mytab(owner);

Index created.

SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

So far, so good. Let’s see what happens if we query all our NULL values:

SQL> select * from mytab where owner is null;

22 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  catcc32q5jnyw, child number 0
-------------------------------------
select * from mytab where owner is null

Plan hash value: 96696846

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   128 (100)|          |
|*  1 |  TABLE ACCESS FULL| MYTAB |    22 |   858 |   128   (1)| 00:00:02 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER" IS NULL)

Ok, as expected the optimizer goes for a full table scan and filters all the NULL values. But as a matter of fact my application needs to do a lot of searching for those NULL values and my table happen to be very very large. So no good idea to do a full table scan over and over again. Let’s see what we can do:

SQL> drop index mytab_owner;

Index dropped.

SQL> create index mytab_owner on mytab(owner, 0);

Index created.

SQL> exec dbms_stats.gather_schema_stats(user);

PL/SQL procedure successfully completed.

SQL> select * from mytab where owner is null;

22 rows selected.

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  catcc32q5jnyw, child number 0
-------------------------------------
select * from mytab where owner is null

Plan hash value: 1460271605

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYTAB       |    22 |   902 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | MYTAB_OWNER |    22 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER" IS NULL)

The thing is, in a concatenated index NULL values in a column are indexed in case the following column is NOT NULL. And since a constant like “0” is per definition NOT NULL, the preceeding column in the index contains all my NULL values. The optimizer is happy and now geos for an index range scan instead.

That’s all, quite easy isn’t it?

Advertisements