Oracle Live SQL - Script: Bitmap vs. B-tree Index Comparison

Statement 1

create table olym_medals as  
  select * from olym.olym_medals_view

Table created.

Statement 2

exec dbms_stats.gather_table_stats(sys_context('userenv', 'current_user'), 'olym_medals')

Statement processed.

Statement 3

create bitmap index olym_sport_bi on olym_medals ( sport )

Index created.

Statement 4

You can only have one visible index on a set of columns at once. So to create the B-tree and bitmaps on the same columns, one needs to be invisible. This hides them from the optimizer, so it won't pick them for execution plans.

create index olym_sport_i on olym_medals ( sport ) invisible

Index created.

Statement 5

create bitmap index olym_gender_bi on olym_medals ( gender )

Index created.

Statement 6

create index olym_gender_i on olym_medals ( gender ) invisible

Index created.

Statement 7

create bitmap index olym_medal_bi on olym_medals ( medal )

Index created.

Statement 8

create index olym_medal_i on olym_medals ( medal ) invisible

Index created.

Statement 9

create bitmap index olym_edition_bi on olym_medals ( edition )

Index created.

Statement 10

create index olym_edition_i on olym_medals ( edition ) invisible

Index created.

Statement 11

create bitmap index olym_event_bi on olym_medals ( event )

Index created.

Statement 12

create index olym_event_i on olym_medals ( event ) invisible

Index created.

Statement 13

create bitmap index olym_athlete_bi on olym_medals ( athlete )

Index created.

Statement 14

create index olym_athlete_i on olym_medals ( athlete ) invisible

Index created.

Statement 15

Leaf_blocks in the *_indexes views is a proxy for how much space the index consumes. Larger numbers mean more space! As you can see, most bitmap indexes need drastically less space than their B-tree equivalents.

with inds as (    
    select substr(    
             index_name, instr(index_name, '_') + 1,     
             instr(index_name, '_', 1, 2) - instr(index_name, '_') - 1    
           ) col, leaf_blocks,    
           index_type    
    from   user_indexes    
)    
  select * from inds    
  pivot (     
    sum(leaf_blocks)     
    for index_type in ('NORMAL' btree, 'BITMAP' bitmap)    
  )


COL BTREE BITMAP
EDITION 61 6
SPORT 83 1
ATHLETE 117 115
MEDAL 71 3
EVENT 111 5
GENDER 64 1

6 rows selected.

COL	BTREE	BITMAP
EDITION	61	6
SPORT	83	1
ATHLETE	117	115
MEDAL	71	3
EVENT	111	5
GENDER	64	1

Statement 16

begin  
  for rws in (  
    select /*+ gather_plan_statistics WG2000 */*   
    from   olym_medals   
    where  gender = 'Women' and medal = 'Gold' and edition = 2000  
  ) loop  
    null;  
  end loop;  
end;

Statement processed.

Statement 17

The optimizer can BITMAP AND the results of the bitmap indexes together. This is efficient, needing just 7 IO operations to do ( see the Buffers column in line 2 of the plan ).

select p.*   
from v$sql s, table (   
  dbms_xplan.display_cursor (   
    s.sql_id, s.child_number, 'ALLSTATS LAST'   
  )   
) p   
where s.sql_text like '%WG2000%'   
and   s.sql_text not like '%not this%'   
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  5n1mnyf8hb0wj, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics WG2000 */* FROM OLYM_MEDALS WHERE 
GENDER = 'Women' AND MEDAL = 'Gold' AND EDITION = 2000
 
Plan hash value: 4279147394
 
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |    293 |00:00:00.01 |     151 |      3 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS     |      1 |    187 |    293 |00:00:00.01 |     151 |      3 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |                 |      1 |        |    293 |00:00:00.01 |       7 |      3 |
|   3 |    BITMAP AND                       |                 |      1 |        |      1 |00:00:00.01 |       7 |      3 |
|*  4 |     BITMAP INDEX SINGLE VALUE       | OLYM_EDITION_BI |      1 |        |      1 |00:00:00.01 |       4 |      2 |
|*  5 |     BITMAP INDEX SINGLE VALUE       | OLYM_MEDAL_BI   |      1 |        |      2 |00:00:00.01 |       2 |      1 |
|*  6 |     BITMAP INDEX SINGLE VALUE       | OLYM_GENDER_BI  |      1 |        |      1 |00:00:00.01 |       1 |      0 |
--------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("EDITION"=2000)
   5 - access("MEDAL"='Gold')
   6 - access("GENDER"='Women')
 
SQL_ID  7q436nx87zvqd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index (olym_medals 
olym_gender_medal_edition_i) WG2000 */* FROM OLYM_MEDALS WHERE GENDER = 
'Women' AND MEDAL = 'Gold' AND EDITION = 2000
 
Plan hash value: 3599023646
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |      1 |        |    293 |00:00:00.01 |     149 |      2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS                 |      1 |    191 |    293 |00:00:00.01 |     149 |      2 |
|*  2 |   INDEX RANGE SCAN                  | OLYM_GENDER_MEDAL_EDITION_I |      1 |    191 |    293 |00:00:00.01 |       5 |      2 |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EDITION"=2000 AND "GENDER"='Women' AND "MEDAL"='Gold')
 

47 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 5n1mnyf8hb0wj, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics WG2000 /* FROM OLYM_MEDALS WHERE
GENDER = 'Women' AND MEDAL = 'Gold' AND EDITION = 2000

Plan hash value: 4279147394

--------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
--------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 293 \|00:00:00.01 \| 151 \| 3 \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 187 \| 293 \|00:00:00.01 \| 151 \| 3 \|
\| 2 \| BITMAP CONVERSION TO ROWIDS \| \| 1 \| \| 293 \|00:00:00.01 \| 7 \| 3 \|
\| 3 \| BITMAP AND \| \| 1 \| \| 1 \|00:00:00.01 \| 7 \| 3 \|
\|* 4 \| BITMAP INDEX SINGLE VALUE \| OLYM_EDITION_BI \| 1 \| \| 1 \|00:00:00.01 \| 4 \| 2 \|
\|* 5 \| BITMAP INDEX SINGLE VALUE \| OLYM_MEDAL_BI \| 1 \| \| 2 \|00:00:00.01 \| 2 \| 1 \|
\|* 6 \| BITMAP INDEX SINGLE VALUE \| OLYM_GENDER_BI \| 1 \| \| 1 \|00:00:00.01 \| 1 \| 0 \|
--------------------------------------------------------------------------------------------------------------------------

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

4 - access("EDITION"=2000)
5 - access("MEDAL"='Gold')
6 - access("GENDER"='Women')

SQL_ID 7q436nx87zvqd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index (olym_medals
olym_gender_medal_edition_i) WG2000 / FROM OLYM_MEDALS WHERE GENDER =
'Women' AND MEDAL = 'Gold' AND EDITION = 2000

Plan hash value: 3599023646

--------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
--------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 293 \|00:00:00.01 \| 149 \| 2 \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 191 \| 293 \|00:00:00.01 \| 149 \| 2 \|
\|* 2 \| INDEX RANGE SCAN \| OLYM_GENDER_MEDAL_EDITION_I \| 1 \| 191 \| 293 \|00:00:00.01 \| 5 \| 2 \|
--------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("EDITION"=2000 AND "GENDER"='Women' AND "MEDAL"='Gold')

Statement 18

This section is making the bitmap indexes invisible and the B-tree visible. This enables the optimizer to use the B-trees so we can compare performance.

alter index olym_gender_bi invisible

Statement processed.

Statement 19

alter index olym_medal_bi invisible

Statement processed.

Statement 20

alter index olym_edition_bi invisible

Statement processed.

Statement 21

alter index olym_gender_i visible

Statement processed.

Statement 22

alter index olym_medal_i visible

Statement processed.

Statement 23

alter index olym_edition_i visible

Statement processed.

Statement 24

begin  
  for rws in (  
    select /*+ gather_plan_statistics WG2000 */*   
    from   olym_medals   
    where  gender = 'Women' and medal = 'Gold' and edition = 2000  
  ) loop  
    null;  
  end loop;  
end;

Statement processed.

Statement 25

The database can convert the B-trees to bitmaps. Then BITMAP AND the results of these together. But this is nearly 10x more work than with the bitmap indexes. The steps below BITMAP CONVERSION TO ROWIDS need 50 buffers (IOs) compared to 7 in the previous plan.

select p.*   
from v$sql s, table (   
  dbms_xplan.display_cursor (   
    s.sql_id, s.child_number, 'ALLSTATS LAST'   
  )   
) p   
where s.sql_text like '%WG2000%'   
and   s.sql_text not like '%not this%'   
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  5n1mnyf8hb0wj, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics WG2000 */* FROM OLYM_MEDALS WHERE 
GENDER = 'Women' AND MEDAL = 'Gold' AND EDITION = 2000
 
Plan hash value: 1404209326
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |    293 |00:00:00.12 |     194 |     47 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS    |      1 |    187 |    293 |00:00:00.12 |     194 |     47 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |                |      1 |        |    293 |00:00:00.12 |      50 |     47 |
|   3 |    BITMAP AND                       |                |      1 |        |      1 |00:00:00.12 |      50 |     47 |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |                |      1 |        |      1 |00:00:00.01 |       6 |      5 |
|*  5 |      INDEX RANGE SCAN               | OLYM_EDITION_I |      1 |        |   2015 |00:00:00.01 |       6 |      5 |
|   6 |     BITMAP CONVERSION FROM ROWIDS   |                |      1 |        |      1 |00:00:00.06 |      24 |     23 |
|*  7 |      INDEX RANGE SCAN               | OLYM_MEDAL_I   |      1 |        |   9849 |00:00:00.01 |      24 |     23 |
|   8 |     BITMAP CONVERSION FROM ROWIDS   |                |      1 |        |      1 |00:00:00.04 |      20 |     19 |
|*  9 |      INDEX RANGE SCAN               | OLYM_GENDER_I  |      1 |        |   7495 |00:00:00.01 |      20 |     19 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   5 - access("EDITION"=2000)
   7 - access("MEDAL"='Gold')
   9 - access("GENDER"='Women')
 
SQL_ID  7q436nx87zvqd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index (olym_medals 
olym_gender_medal_edition_i) WG2000 */* FROM OLYM_MEDALS WHERE GENDER = 
'Women' AND MEDAL = 'Gold' AND EDITION = 2000
 
Plan hash value: 3599023646
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |      1 |        |    293 |00:00:00.01 |     149 |      2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS                 |      1 |    191 |    293 |00:00:00.01 |     149 |      2 |
|*  2 |   INDEX RANGE SCAN                  | OLYM_GENDER_MEDAL_EDITION_I |      1 |    191 |    293 |00:00:00.01 |       5 |      2 |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EDITION"=2000 AND "GENDER"='Women' AND "MEDAL"='Gold')
 

50 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 5n1mnyf8hb0wj, child number 1
-------------------------------------
SELECT /+ gather_plan_statistics WG2000 /* FROM OLYM_MEDALS WHERE
GENDER = 'Women' AND MEDAL = 'Gold' AND EDITION = 2000

Plan hash value: 1404209326

-------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
-------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 293 \|00:00:00.12 \| 194 \| 47 \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 187 \| 293 \|00:00:00.12 \| 194 \| 47 \|
\| 2 \| BITMAP CONVERSION TO ROWIDS \| \| 1 \| \| 293 \|00:00:00.12 \| 50 \| 47 \|
\| 3 \| BITMAP AND \| \| 1 \| \| 1 \|00:00:00.12 \| 50 \| 47 \|
\| 4 \| BITMAP CONVERSION FROM ROWIDS \| \| 1 \| \| 1 \|00:00:00.01 \| 6 \| 5 \|
\|* 5 \| INDEX RANGE SCAN \| OLYM_EDITION_I \| 1 \| \| 2015 \|00:00:00.01 \| 6 \| 5 \|
\| 6 \| BITMAP CONVERSION FROM ROWIDS \| \| 1 \| \| 1 \|00:00:00.06 \| 24 \| 23 \|
\|* 7 \| INDEX RANGE SCAN \| OLYM_MEDAL_I \| 1 \| \| 9849 \|00:00:00.01 \| 24 \| 23 \|
\| 8 \| BITMAP CONVERSION FROM ROWIDS \| \| 1 \| \| 1 \|00:00:00.04 \| 20 \| 19 \|
\|* 9 \| INDEX RANGE SCAN \| OLYM_GENDER_I \| 1 \| \| 7495 \|00:00:00.01 \| 20 \| 19 \|
-------------------------------------------------------------------------------------------------------------------------

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

5 - access("EDITION"=2000)
7 - access("MEDAL"='Gold')
9 - access("GENDER"='Women')

SQL_ID 7q436nx87zvqd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index (olym_medals
olym_gender_medal_edition_i) WG2000 / FROM OLYM_MEDALS WHERE GENDER =
'Women' AND MEDAL = 'Gold' AND EDITION = 2000

Plan hash value: 3599023646

--------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
--------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 293 \|00:00:00.01 \| 149 \| 2 \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 191 \| 293 \|00:00:00.01 \| 149 \| 2 \|
\|* 2 \| INDEX RANGE SCAN \| OLYM_GENDER_MEDAL_EDITION_I \| 1 \| 191 \| 293 \|00:00:00.01 \| 5 \| 2 \|
--------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("EDITION"=2000 AND "GENDER"='Women' AND "MEDAL"='Gold')

Statement 26

drop index olym_gender_i

Index dropped.

Statement 27

drop index olym_medal_i

Index dropped.

Statement 28

drop index olym_edition_i

Index dropped.

Statement 29

This creates a composite B-tree index on all three columns.

create index olym_gender_medal_edition_i on olym_medals ( edition, gender, medal  )

Index created.

Statement 30

begin  
  for rws in (  
    select /*+ gather_plan_statistics index (olym_medals olym_gender_medal_edition_i) WG2000 */*   
    from   olym_medals   
    where  gender = 'Women' and medal = 'Gold' and edition = 2000  
  ) loop  
    null;  
  end loop;  
end;

Statement processed.

Statement 31

It takes a B-tree index on all three columns to get similar performance to the three separate bitmap indexes.

select p.*    
from v$sql s, table (    
  dbms_xplan.display_cursor (    
    s.sql_id, s.child_number, 'ALLSTATS LAST'    
  )    
) p    
where s.sql_text like '%index%WG2000%'    
and   s.sql_text not like '%not this%'    
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  7q436nx87zvqd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index (olym_medals 
olym_gender_medal_edition_i) WG2000 */* FROM OLYM_MEDALS WHERE GENDER = 
'Women' AND MEDAL = 'Gold' AND EDITION = 2000
 
Plan hash value: 3599023646
 
--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                             |      1 |        |    293 |00:00:00.01 |     149 |      2 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS                 |      1 |    191 |    293 |00:00:00.01 |     149 |      2 |
|*  2 |   INDEX RANGE SCAN                  | OLYM_GENDER_MEDAL_EDITION_I |      1 |    191 |    293 |00:00:00.01 |       5 |      2 |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EDITION"=2000 AND "GENDER"='Women' AND "MEDAL"='Gold')
 

21 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 7q436nx87zvqd, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics index (olym_medals
olym_gender_medal_edition_i) WG2000 / FROM OLYM_MEDALS WHERE GENDER =
'Women' AND MEDAL = 'Gold' AND EDITION = 2000

Plan hash value: 3599023646

--------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
--------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 293 \|00:00:00.01 \| 149 \| 2 \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 191 \| 293 \|00:00:00.01 \| 149 \| 2 \|
\|* 2 \| INDEX RANGE SCAN \| OLYM_GENDER_MEDAL_EDITION_I \| 1 \| 191 \| 293 \|00:00:00.01 \| 5 \| 2 \|
--------------------------------------------------------------------------------------------------------------------------------------

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

2 - access("EDITION"=2000 AND "GENDER"='Women' AND "MEDAL"='Gold')

Statement 32

drop index olym_gender_medal_edition_i

Index dropped.

Statement 33

alter table olym_medals modify ( event null )

Table altered.

Statement 34

Null event for 5 rows.

update olym_medals   
set    event = null  
where  rownum <= 5

5 row(s) updated.

Statement 35

select /*+ gather_plan_statistics */* from olym_medals   
where  event is null


CITY EDITION SPORT DISCIPLINE ATHLETE NOC GENDER EVENT EVENT_GENDER MEDAL
Beijing 2008 Judo Judo BRAYSON, Oscar CUB Men  - M Bronze
Atlanta 1996 Judo Judo DOUILLET, David FRA Men  - M Gold
Sydney 2000 Judo Judo DOUILLET, David FRA Men  - M Gold
Beijing 2008 Judo Judo ISHII, Satoshi JPN Men  - M Gold
Atlanta 1996 Judo Judo MOELLER, Frank GER Men  - M Bronze

5 rows selected.

CITY	EDITION	SPORT	DISCIPLINE	ATHLETE	NOC	GENDER	EVENT	EVENT_GENDER	MEDAL
Beijing	2008	Judo	Judo	BRAYSON, Oscar	CUB	Men	-	M	Bronze
Atlanta	1996	Judo	Judo	DOUILLET, David	FRA	Men	-	M	Gold
Sydney	2000	Judo	Judo	DOUILLET, David	FRA	Men	-	M	Gold
Beijing	2008	Judo	Judo	ISHII, Satoshi	JPN	Men	-	M	Gold
Atlanta	1996	Judo	Judo	MOELLER, Frank	GER	Men	-	M	Bronze

Statement 36

The query can use the bitmap index to process the "is null" condition.

select p.*     
from v$sql s, table (     
  dbms_xplan.display_cursor (     
    s.sql_id, s.child_number, 'ALLSTATS LAST'     
  )     
) p     
where s.sql_text like '%event is null%'     
and   s.sql_text not like '%not this%'     
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  dp6rc1kd141w6, child number 0
-------------------------------------
select * from olym_medals   where  event is null
 
Plan hash value: 475678949
 
-----------------------------------------------------------------------
| Id  | Operation                           | Name           | E-Rows |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |        |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS    |      1 |
|*  2 |   INDEX RANGE SCAN                  | OLYM_EVENT_1_I |      1 |
-----------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EVENT" IS NULL)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
SQL_ID  4x0d43k1dy7v2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */* from olym_medals    where  event 
is null
 
Plan hash value: 3202796910
 
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |      1 |        |      5 |00:00:00.01 |       7 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS   |      1 |      1 |      5 |00:00:00.01 |       7 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |               |      1 |        |      5 |00:00:00.01 |       2 |
|*  3 |    BITMAP INDEX SINGLE VALUE        | OLYM_EVENT_BI |      1 |        |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EVENT" IS NULL)
 

46 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID dp6rc1kd141w6, child number 0
-------------------------------------
select * from olym_medals where event is null

Plan hash value: 475678949

-----------------------------------------------------------------------
\| Id \| Operation \| Name \| E-Rows \|
-----------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \|
\|* 2 \| INDEX RANGE SCAN \| OLYM_EVENT_1_I \| 1 \|
-----------------------------------------------------------------------

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

2 - access("EVENT" IS NULL)

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID 4x0d43k1dy7v2, child number 0
-------------------------------------
select /+ gather_plan_statistics /* from olym_medals where event
is null

Plan hash value: 3202796910

---------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
---------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 5 \|00:00:00.01 \| 7 \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \| 1 \| 5 \|00:00:00.01 \| 7 \|
\| 2 \| BITMAP CONVERSION TO ROWIDS \| \| 1 \| \| 5 \|00:00:00.01 \| 2 \|
\|* 3 \| BITMAP INDEX SINGLE VALUE \| OLYM_EVENT_BI \| 1 \| \| 1 \|00:00:00.01 \| 2 \|
---------------------------------------------------------------------------------------------------------------

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

3 - access("EVENT" IS NULL)

Statement 37

drop index olym_event_bi

Index dropped.

Statement 38

alter index olym_event_i visible

Statement processed.

Statement 39

select /*+ gather_plan_statistics */* from olym_medals   
where  event is null


CITY EDITION SPORT DISCIPLINE ATHLETE NOC GENDER EVENT EVENT_GENDER MEDAL
Beijing 2008 Judo Judo BRAYSON, Oscar CUB Men  - M Bronze
Atlanta 1996 Judo Judo DOUILLET, David FRA Men  - M Gold
Sydney 2000 Judo Judo DOUILLET, David FRA Men  - M Gold
Beijing 2008 Judo Judo ISHII, Satoshi JPN Men  - M Gold
Atlanta 1996 Judo Judo MOELLER, Frank GER Men  - M Bronze

5 rows selected.

CITY	EDITION	SPORT	DISCIPLINE	ATHLETE	NOC	GENDER	EVENT	EVENT_GENDER	MEDAL
Beijing	2008	Judo	Judo	BRAYSON, Oscar	CUB	Men	-	M	Bronze
Atlanta	1996	Judo	Judo	DOUILLET, David	FRA	Men	-	M	Gold
Sydney	2000	Judo	Judo	DOUILLET, David	FRA	Men	-	M	Gold
Beijing	2008	Judo	Judo	ISHII, Satoshi	JPN	Men	-	M	Gold
Atlanta	1996	Judo	Judo	MOELLER, Frank	GER	Men	-	M	Bronze

Statement 40

But not the regular B-tree index. This is because wholly null entries aren't indexed!

select p.*     
from v$sql s, table (     
  dbms_xplan.display_cursor (     
    s.sql_id, s.child_number, 'ALLSTATS LAST'     
  )     
) p     
where s.sql_text like '%event is null%'     
and   s.sql_text not like '%not this%'     
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  dp6rc1kd141w6, child number 0
-------------------------------------
select * from olym_medals   where  event is null
 
Plan hash value: 475678949
 
-----------------------------------------------------------------------
| Id  | Operation                           | Name           | E-Rows |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |        |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS    |      1 |
|*  2 |   INDEX RANGE SCAN                  | OLYM_EVENT_1_I |      1 |
-----------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EVENT" IS NULL)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
SQL_ID  4x0d43k1dy7v2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */* from olym_medals    where  event 
is null
 
Plan hash value: 4049040404
 
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |      1 |        |      5 |00:00:00.01 |     374 |
|*  1 |  TABLE ACCESS FULL| OLYM_MEDALS |      1 |      1 |      5 |00:00:00.01 |     374 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("EVENT" IS NULL)
 

44 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID dp6rc1kd141w6, child number 0
-------------------------------------
select * from olym_medals where event is null

Plan hash value: 475678949

-----------------------------------------------------------------------
\| Id \| Operation \| Name \| E-Rows \|
-----------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| \|
\| 1 \| TABLE ACCESS BY INDEX ROWID BATCHED\| OLYM_MEDALS \| 1 \|
\|* 2 \| INDEX RANGE SCAN \| OLYM_EVENT_1_I \| 1 \|
-----------------------------------------------------------------------

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

2 - access("EVENT" IS NULL)

Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level

SQL_ID 4x0d43k1dy7v2, child number 0
-------------------------------------
select /+ gather_plan_statistics /* from olym_medals where event
is null

Plan hash value: 4049040404

-------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
-------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 5 \|00:00:00.01 \| 374 \|
\|* 1 \| TABLE ACCESS FULL\| OLYM_MEDALS \| 1 \| 1 \| 5 \|00:00:00.01 \| 374 \|
-------------------------------------------------------------------------------------------

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

1 - filter("EVENT" IS NULL)

Statement 41

drop index olym_event_i

Index dropped.

Statement 42

To use a B-tree index in "is null" SQL, you need to have a not null value after the column in the index. This could be a mandatory column or, as shown here, a constant.

create index olym_event_1_i on olym_medals ( event , 1 )

Index created.

Statement 43

select /*+ gather_plan_statistics */* from olym_medals   
where  event is null


CITY EDITION SPORT DISCIPLINE ATHLETE NOC GENDER EVENT EVENT_GENDER MEDAL
Beijing 2008 Judo Judo BRAYSON, Oscar CUB Men  - M Bronze
Atlanta 1996 Judo Judo DOUILLET, David FRA Men  - M Gold
Sydney 2000 Judo Judo DOUILLET, David FRA Men  - M Gold
Beijing 2008 Judo Judo ISHII, Satoshi JPN Men  - M Gold
Atlanta 1996 Judo Judo MOELLER, Frank GER Men  - M Bronze

5 rows selected.

CITY	EDITION	SPORT	DISCIPLINE	ATHLETE	NOC	GENDER	EVENT	EVENT_GENDER	MEDAL
Beijing	2008	Judo	Judo	BRAYSON, Oscar	CUB	Men	-	M	Bronze
Atlanta	1996	Judo	Judo	DOUILLET, David	FRA	Men	-	M	Gold
Sydney	2000	Judo	Judo	DOUILLET, David	FRA	Men	-	M	Gold
Beijing	2008	Judo	Judo	ISHII, Satoshi	JPN	Men	-	M	Gold
Atlanta	1996	Judo	Judo	MOELLER, Frank	GER	Men	-	M	Bronze

Statement 44

The database can now use the B-tree index.

select p.*     
from v$sql s, table (     
  dbms_xplan.display_cursor (     
    s.sql_id, s.child_number, 'ALLSTATS LAST'     
  )     
) p     
where s.sql_text like '%event is null%'     
and   s.sql_text not like '%not this%'     
and   s.sql_text not like 'begin%'


PLAN_TABLE_OUTPUT
SQL_ID  dp6rc1kd141w6, child number 0
-------------------------------------
select * from olym_medals   where  event is null
 
Plan hash value: 475678949
 
-----------------------------------------------------------------------
| Id  | Operation                           | Name           | E-Rows |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |        |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS    |      1 |
|*  2 |   INDEX RANGE SCAN                  | OLYM_EVENT_1_I |      1 |
-----------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EVENT" IS NULL)
 
Note
-----
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level
 
SQL_ID  4x0d43k1dy7v2, child number 0
-------------------------------------
select /*+ gather_plan_statistics */* from olym_medals    where  event 
is null
 
Plan hash value: 475678949
 
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |      5 |00:00:00.01 |      11 |      1 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| OLYM_MEDALS    |      1 |      1 |      5 |00:00:00.01 |      11 |      1 |
|*  2 |   INDEX RANGE SCAN                  | OLYM_EVENT_1_I |      1 |      1 |      5 |00:00:00.01 |       6 |      1 |
-------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EVENT" IS NULL)
 

45 rows selected.

Bitmap vs. B-tree Index Comparison

Additional Information