Quote Originally Posted by Bore
PAVB, I am not very experienced with the bitmap indexes, can you pls. explain why they starts growing?
Let me quote Jonathan Lewis (http://jonathanlewis.wordpress.com/2...ing-in-action/) on this one.

"it is fairly common for bitmap indexes to grow dramatically when the underlying tables are subject to DML. A single bitmap index “chunk” can be as large as 3,900 bytes (in an 8K block), and changing the value of the indexed column for just one row of the table makes Oracle clone two such chunks - which can cause two leaf block splits, which sometimes causes two branch block splits."

Imagine this scenario, taken for a dw prod environment.

-- You have a partioned bitmap index bitmap_idx on table_a.
-- Before your daily ETL process you check the size of bitmap_idx:Mar2007 partition, it could be something like 15 Megs.
-- After your daily ETL process which is heavily hitting Mar2007 partitions you go back to check the size of bitmap_idx:Mar2007... don't be surprised if now is as big as 1.5 Gig
-- After rebuilding it goes back to 15 Megs.

We actually have a process after ETL looking for bitmap index partitions larger than 1 Gig and, rebuilding them as they are found.

Why you want to use bitmap indexes if they are such a pain? 'cause they are great for Oracle's query-rewrite functionality which gives you better performance in a dw environment. They are like antibiotics, use them just when actually needed.