set timing on
drop table t1 purge ;
create table t1 nologging as
select rownum id,
mod(rownum,10) btree_col,
mod(rownum,10) bitmap_col,
rpad(‘x’,200) padding
from all_source
where rownum <= 250000 ;
create index t1_btree on t1(btree_col);
Executed in 2,078 seconds
create bitmap index t1_bit on t1(bitmap_col);
Executed in 0,328 seconds
analyze table t1 compute statistics for table for all indexed columns;
SELECT a.index_name,
a.index_type,
a.leaf_blocks,
a.avg_data_blocks_per_key,
a.clustering_factor,
a.num_rows
FROM user_indexes a
WHERE a.table_name = ‘T1′ ;
INDEX_NAME INDEX_TYPE LEAF_BLOCKS AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS
—————————— ————————— ———– ———————– —————– ———-
T1_BTREE NORMAL 239 3732 37320 250000
T1_BIT BITMAP 30 6 60 60
SELECT object_name, object_id
FROM user_objects where object_name in (‘T1_BTREE’, ‘T1_BIT’);
OBJECT_NAME OBJECT_ID
——————————————————————————– ———-
T1_BIT 339951
T1_BTREE 339950
alter session set tracefile_identifier = btree_index_dump ;
ALTER SESSION SET EVENTS ‘immediate trace name treedump level 339950′ ;
alter session set tracefile_identifier = bitmap_index_dump ;
ALTER SESSION SET EVENTS ‘immediate trace name treedump level 339951′ ;
$ pwd
/oracle10/app/oracle/product/10.2/LOGS/udump
$ ls -lt | more
total 23877916
-rw-r–r– 1 oracle dba 1909 Dec 4 11:36 xxx_ora_28846_BITMAP_INDEX_DUMP.trc
-rw-r–r– 1 oracle dba 15392 Dec 4 11:36 xxx_ora_28846_BTREE_INDEX_DUMP.trc
…
$ more xxx_ora_28846_BITMAP_INDEX_DUMP.trc
…
—– begin tree dump
branch: 0×9341ba8c -1824408948 (0: nrow: 30, level: 1)
leaf: 0×9341ba8d -1824408947 (-1: nrow: 2 rrow: 2)
leaf: 0×9341ba8e -1824408946 (0: nrow: 2 rrow: 2)
leaf: 0×9341ba8f -1824408945 (1: nrow: 2 rrow: 2)
…
leaf: 0×9341baa8 -1824408920 (26: nrow: 2 rrow: 2)
leaf: 0×9341baa9 -1824408919 (27: nrow: 2 rrow: 2)
leaf: 0×9341baaa -1824408918 (28: nrow: 2 rrow: 2)
—– end tree dump
$ more xxx_ora_28846_BTREE_INDEX_DUMP.trc
…
—– begin tree dump
branch: 0×93419f0c -1824415988 (0: nrow: 239, level: 1)
leaf: 0×93419f0d -1824415987 (-1: nrow: 1119 rrow: 1119)
leaf: 0×93419f0e -1824415986 (0: nrow: 1119 rrow: 1119)
leaf: 0×93419f0f -1824415985 (1: nrow: 1119 rrow: 1119)
leaf: 0×93419f10 -1824415984 (2: nrow: 1119 rrow: 1119)
…
leaf: 0×93419ff8 -1824415752 (234: nrow: 1039 rrow: 1039)
leaf: 0×93419ff9 -1824415751 (235: nrow: 1039 rrow: 1039)
leaf: 0×93419ffa -1824415750 (236: nrow: 1039 rrow: 1039)
leaf: 0×93419ffb -1824415749 (237: nrow: 931 rrow: 931)
—– end tree dump
11:39:14 > set timing on
11:39:15 > set autot on
11:39:18 > SELECT COUNT(*)
11:39:20 2 FROM t1 t
11:39:20 3 WHERE t.btree_col = 5
11:39:20 4 OR t.btree_col = 3 ;
COUNT(*)
———
50000
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=48 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 INLIST ITERATOR
3 2 INDEX (RANGE SCAN) OF ‘T1_BTREE’ (INDEX) (Cost=48 Card=50000 Bytes=100000)
Statistics
———————————————————-
1 recursive calls
0 db block gets
52 consistent gets
0 physical reads
0 redo size
…
11:39:21 > SELECT COUNT(*)
11:39:21 2 FROM t1 t
11:39:21 3 WHERE t.bitmap_col = 5
11:39:21 4 OR t.bitmap_col = 3 ;
COUNT(*)
———
50000
Elapsed: 00:00:00.00
Execution Plan
———————————————————-
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 INLIST ITERATOR
3 2 BITMAP CONVERSION (COUNT) (Cost=6 Card=50000 Bytes=100000)
4 3 BITMAP INDEX (SINGLE VALUE) OF ‘T1_BIT’ (INDEX (BITMAP))
Statistics
———————————————————-
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
…
Conclusions
Bitmap indexes are widely used in OLAP environments.
These environments typically have large amounts of data and ad hoc queries, but a low level of concurrent DML transactions.
For such applications, bitmap indexing provides:
- Reduced response time for large classes of ad hoc queries.
- Reduced storage requirements compared to other indexing techniques.
- Dramatic performance gains even on hardware with a relatively small number of CPUs or a small amount of memory.
- Efficient maintenance during parallel DML and loads.
The key facts to remember are:
• If a B*tree index is not an efficient mechanism for accessing data, it is unlikely to become more efficient simply because you convert it to a bitmap index.
• Bitmap indexes can usually be built quickly, and tend to be surprisingly small.
• The size of the bitmap index varies dramatically with the distribution of the data.
• Bitmap indexes are typically useful only for queries that can use several such indexes at once.
• Updates to bitmapped columns, and general insertion/deletion of data can cause serious lock contention.
• Updates to bitmapped columns, and general insertion/deletion of data can degrade the quality of the indexes quite dramatically.
References
Understanding Bitmap indexes
http://www.jlcomp.demon.co.uk/03_bitmap_1i.html
http://jonathanlewis.wordpress.com/2006/11/29/bitmap-indexes/
Oracle® Database Data Warehousing Guide 10g Release 2 (10.2)
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14223/indexes.htm#i1004902
Index Block Dump Example
http://tonguc.oracleturk.org/index.php/2006/08/13/index-block-dump/
Index Clustering Factor Example
http://tonguc.oracleturk.org/index.php/2006/08/12/index-clustering-factor/