Tuesday, December 29, 2009

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