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 | | | |
------------------------------------------------------------------------------
