Creating Bitmap Indexes on a Materialized View

indexmaterialized-vieworacle

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?

Best Answer

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.

Related Question