Q

Help with bitmaps and b-tree indexes

How is the b-tree index stored internally? Also, if a query is submitted, how is the query result loaded in memory? Is it block-oriented or column-oriented?

  1. How is the b-tree index stored internally?
  2. If a query is submitted, how is the query result loaded in memory? Is it block-oriented or column-oriented?
Bitmaps are stored compressed with one row/column value in the array. A bitmap for a 1,000-row table on a 50-valued index would have 50,000 bit entries. The bitmaps are block-oriented storage-wise, and they are uncompressed at runtime. Also, updates are very, very slow compared to b-tree indexes.

Oracle also has bitmap join indexes:

http://www.dba-oracle.com/art_builder_bitmap_join_idx.htm

In general, you will want a bitmap index when:

  1. Table column is low cardinality -- As a rough guide, consider a bitmap for any index with less than 100 distinct values.
        select region, count(*) from sales group by region; 
    
  2. The table has low DML -- You must have low insert./update/delete activity. Updating bitmapped indexes takes a lot of resources, and bitmapped indexes are best for largely read-only tables and tables that are batch updated nightly.
  3. Multiple columns -- Your SQL queries reference multiple, low-cardinality values in their where clause. Oracle cost-based SQL optimizer (CBO) will scream when you have bitmap indexes on.
This was first published in November 2005

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close