Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been reading this article, discussing the benefits, tradeoffs, and potential problems with Bitmapped Indexes. It is mentioned:

Also, remember that bitmap indexes are only suitable for static tables and materialized views which are updated at night and rebuilt after batch row loading. If your tables are not read-only during query time, DO NOT consider using bitmap indexes!

Most of our Materialized Views are updated nightly using the fast refresh method. Does the above quote mean that the MV should be rebuilt, or that the indexes should be rebuilt?

share|improve this question
1  
Neither - it just means that bitmap indexes wreck concurrency for update/inserts –  Jack Douglas May 18 '12 at 5:30

1 Answer 1

up vote 2 down vote accepted

The key phrase is "after batch row loading". If your process does not meet this criteria then you do not need to drop/rebuild the bitmap indexes.

Jonathan Lewis has an excellent article on this. A key point he gives is

You can get lucky -- but in general you should start with the assumption that even a serialized batch update will be most effective if you drop the bitmap indexes before the batch and rebuild them afterwards.

The whole article is good and ends with some key facts to remember about bitmap indexes:

  • 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.