Oracle doesnt recommend to build BITMAP indexes on heavily updated or insertedI was planning to change the index type on these columns to BITMAP but
No, not at all! Rebuilding indexes "cleans them up", packs the leaf nodes, and it prevents fragmentation, not leads to it! Lt. Col. Garmany has a good note on this:dropping indexes before the load and rebuilding them after the load has completed what can lead to often tablespace fragmentations.
That's ridiculous, arrogant and pretentious.You are asking the wrong question for the wrong reason.
That's none of your business. It has NOTHING to do with his question about bitmaps.What business problem are you facing and in what version?
So, Don, when you want to be rude about me you scream: "you don't have enough information to make that comment". But when Daniel Morgan asks for more information you abuse him for asking questions.That's none of your business. It has NOTHING to doWhat business problem are you facing and in what version?
with his question about bitmaps.
This person asked a legitimate question, and he DOES
NOT deserve to be talked-down to and treated like he
is a dumb child, especially from an Oracle ACE.
The table contains three IDEAL columns for bitmap indexes the first of whichContrary to popular legend, and possibly contrary even to the manuals and Metalink, these columns are NOT necessarily ideal for bitmap indexes. Consider a query with:
may have only two, the second three and the third four distinct values.
col1 = '1_of_2'You have a total of 24 possible combinations. Given your 16M rows, this means that on average the optimizer will expect to collect about 670.000 rows spread across something like 100,000 to 130,000 blocks. Under these circumstances you may find that Oracle doesn't use the indexes anyway (unless you fool it by fiddling with parameters like the optimizer_index_cost_adj, and that's generally a bad idea) - and if the model is a reasonable description of the actual data it probably shouldn't use the indexes.
and col2 = '1_of_3'
and col3 = '1_of_4'
Interesting! In my experience, updates to tables with bitmaps is VERY time-consuming.we perform both inserts and updates (or rather merge) on tables with lots of bitmap indexes and that is NOT a problem most of the time.
That's an excellent rule-of-thumb!If I update most of the rows in table, and the total number of rows stays somewhat constant, I drop & rebuild.
Morgan wrote: "You are asking the wrong question for the wrong reason."But when Daniel Morgan asks for more information you abuse him for asking questions.
You have called me a lot worse! Do you really think that your "friends" don't share your e-mails with me?so from now on I think I'll just have to call you "both ways Burleson"
Interesting! In my experience, updates to tablesI just accept the increased runtime. There is a reason the index is there. As long as I don't have concurrent modifications it's fine. This is also what I meant by and that is NOT a problem most of the time. :)
with bitmaps is VERY time-consuming.
Just curious, how do you get around the bitmap update
issue? Are you using server resources or fast disk
to speed it up?
Just to compare notes, I've see update on tables with three bitmap indexes take 20 wall-clock seconds each.I just accept the increased runtime.