Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  Oracle Tips by Burleson

Bitmapped Index Usage

A bitmapped index is used for low-cardinality data such as sex, race, hair color, and so on.1  If a column to be indexed has a selectivity of greater than 30 to 40 percent of the total data, then it is probably a good candidate for bitmap indexing.     

Bitmap indexing is not suggested for high-cardinality, high-update, or high-delete-type data, as bitmap indexes may have to be frequently rebuilt in these type situations.     

There are three things to consider when choosing an index method:

  • Performance

  • Storage

  • Maintainability   

The major advantages of using bitmapped indexes are performance impact for certain queries and their relatively small storage requirements. Note, however, that bitmapped indexes are not applicable to every query; and bitmapped indexes, like B-tree indexes, can impact the performance of insert, update, and delete statements. Bitmaps store large amounts of data about various rows in each block of the index structure, and because bitmap locking is at the block level, any insert, update, or delete activity may lock an entire range of values.   

Bitmapped indexes can provide very impressive performance improvements. Under test conditions, the execution times of certain queries improved by several orders of magnitude. The queries that benefit the most from bitmapped indexes

have the following characteristics:

  • The WHERE clause contains multiple predicates on low-cardinality columns.

  • The individual predicates on these low-cardinality columns select a large number of rows.

  • Bitmapped indexes have been created on some or all of these low-cardinality columns.

  • The tables being queried contain many rows.

An advantage of bitmapped indexes is that multiple bitmapped indexes can be used to evaluate the conditions on a single table. Thus, bitmapped indexes are very useful for complex ad hoc queries that contain lengthy WHERE clauses involving low-cardinality data.  

Bitmapped indexes incur a small storage cost and have a significant storage savings over B-tree indexes. A bitmapped index can require 100 times less space than a B-tree index for a low-cardinality column.

See Code Depot


www.oracle-script.com

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.