Add new comment

I have a customer with a million row 125mb table. A query they are running returns very slowly:

explain plan for SELECT * FROM BIGTABLE WHERE col1 IS NULL;
SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------      
| Id  | Operation            |  Name             | Rows  | Bytes | Cost  |      
--------------------------------------------------------------------------      
|   0 | SELECT STATEMENT     |                   |     1 |    96 |  1436 |      
|*  1 |  TABLE ACCESS FULL   | BIGTABLE          |     1 |    96 |  1436 |      
--------------------------------------------------------------------------      

So where do we go from here? The table didn't have any indexes at all so we were starting with a clean slate.

Regular B-Tree indexes won't help in this situation since they don't record NULLs.

You can do some fancy stuff by using a function based index and NVL.

create index ix1 on bigtable(nvl(col1,-1));

SELECT * FROM BIGTABLE WHERE col1 = -1;

This works great if you can change your original query. And you don't mind having a magic value like -1 hanging around forever to confuse future dbas and developers doing work on your database.

But we do have another option. Bitmap indexes will record information about NULLs. This is helpful because now the Optimizer can use this information to determine how to create an execution plan with something better than a Full Table Scan.

create bitmap index i2 on bigtable(col1);
exec dbms_stats.gather_table_stats(tabname=>'BIGTABLE');

explain plan for SELECT * FROM BIGTABLE WHERE col1 IS NULL;
SELECT * FROM TABLE(dbms_xplan.display);


--------------------------------------------------------------------------------------                                                                          
| Id  | Operation                    |  Name                 | Rows  | Bytes | Cost  |                                                                          
--------------------------------------------------------------------------------------                                                                          
|   0 | SELECT STATEMENT             |                       |     1 |   100 |     2 |                                                                          
|   1 |  TABLE ACCESS BY INDEX ROWID | BIGTABLE              |     1 |   100 |     2 |                                                                          
|   2 |   BITMAP CONVERSION TO ROWIDS|                       |       |       |       |
|*  3 |    BITMAP INDEX SINGLE VALUE | IX1                   |       |       |       |  
--------------------------------------------------------------------------------------                                                                          

Keep in mind BitMap indexes have gotten a bad reputation in OLTP databases, but this is less of an issue with modern versions of Oracle. I would suggest reading the following two articles by Richard Foote to get familiar with possible performance implications:

Bitmap Indexes with many Distinct Column Values
Bitmap Index Degradation Since 10g