It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Btree Vs Bitmap indexes - When to choose whom


  • B-Trees are the typical index type used when you do CREATE INDEX ... in a database:
    1. They are very fast when you are selecting just a small very subset of the index data (5%-10% max typically)
    2. They work better when you have a lot of distinct indexed values.
    3. Combining several B-Tree indexes can be done, but simpler approaches are often more efficient.
    4. They are not useful when there are few distinct values for the indexed data, or when you want to get a large (>10% typically) subset of the data.
    5. Each B-Tree index impose a small penalty when inserting/updating values on the indexed table. This can be a problem if you have a lot of indexes in a very busy table.

    This characteristics make B-Tree indexes very useful for speeding searches in OLTP applications, when you are working with very small data sets at a time, most queries filter by ID, and you want good concurrent performance.
  • Bitmap indexes are a more specialized index variant:
    1. They encode indexed values as bitmaps and so are very space efficient.
    2. They tend to work better when there are few distinct indexed values
    3. DB optimizers can combine several bitmap indexed very easily, this allows for efficient execution of complex filters in queries.
    4. They are very inefficient when inserting/updating values.

    Bitmap indexes are mostly used in data warehouse applications, where the database is read only except for the ETL processes, and you usually need to execute complex queries against a star schema, where bitmap indexes can speed up filtering based on conditions in your dimension tables, which do not usually have too many distinct values.
As a very short summary: use B-Tree indexes (the "default" index in most databases) unless you are a data warehouse developer and know you will benefit for a bitmap index.

2 comments:

This is a really good article, thanks for clearing up any confusion regarding when to choose bitmap and/or btree indexes in Oracle. Theres another good read right here too:

What is a bitmap index

 

Trying to find the Best Dating Website? Join and find your perfect match.

 

You Might Also Like

Related Posts with Thumbnails

Pages