Null and execution plan for B* Tree Index and Bitmap Index

Finding 1: B* Tree Index does not contain NULL values, so it can only be used in "some" scenarios when the column defined as "NOT NULL"

SQL> create table t
  2  as
  3  select prod_id,cust_id,promo_id from sh.sales;

Table created.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                   NOT NULL NUMBER
 CUST_ID                                   NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER

SQL> create index t_n1 on t(prod_id);

Index created.



SQL> explain plan for select count(*) from t;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2225357383

----------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |   718   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE       |      |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| T_N1 |   967K|   718   (1)| 00:00:09 |
----------------------------------------------------------------------

When NULL is possible for that column, then full tablescan must be used, instead of INDEX FAST FULL SCAN.

SQL> alter table t modify (prod_id null);

Table altered.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROD_ID                                            NUMBER
 CUST_ID                                   NOT NULL NUMBER
 PROMO_ID                                  NOT NULL NUMBER

SQL> explain plan for select count(*) from t;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   817   (1)| 00:00:10 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    |   967K|   817   (1)| 00:00:10 |
-------------------------------------------------------------------

NULL Value is included in the bitmap indexes.

SQL> drop index t_n1;

Index dropped.

SQL> create bitmap index t_b1 on t(prod_id);

Index created.

SQL> explain plan for select count(*) from t;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3600098451

------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    27   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |      |     1 |            |          |
|   2 |   BITMAP CONVERSION COUNT     |      |   967K|    27   (0)| 00:00:01 |
|   3 |    BITMAP INDEX FAST FULL SCAN| T_B1 |       |            |          |
------------------------------------------------------------------------------