High cardinality Bitmap indexes

[rsuri]

Oracle 8.1.7.4 Enterprise on HP-UX 11.0

Is anyone aware of problems with bitmap indexes when the cardinality is high (hundreds or even thousands of unique values)
As bitmaps were meant for low cardinality, I know that high cardinality columns should not be candidates for bitmaps but I need to know if someone does create such indexes what issues may pop up?

Here are the issues I had so far with bitmap indexes and that's why I started looking closely -

1. One of my developers was using Function based indexes (6 months ago) and so set the QUERY_REWRITE_ENABLED parameter to TRUE. This started causing some issues later on with ORA-600 and Oracle support recommended rebuilding the bitmap indexes (they said that other people had faced similar issues with bitmap indexes and rebuilding often would help)

2. We started getting some more Ora-600 issues (after patching to 8.1.7.4 from 8.1.7.0) and this time Oracle support said that we had hit a bug 2767505 and the solution is to drop/recreate all relatted bitmap indexes before DML or go to 9.2.0..4

Looking at the bitmaps I see that some of them have 100,000 unique values out of 6 million records; so the cardinality is less than 2% but still very high.

Just wondering if any of you had such issues with high cardinality (if I can call it that)

Thanks,

[st2003]

I have witnessed tremedous amount of deadlocks (ORA-00060, Never saw 0ra-600, core-dumps) and application resolves deadlocks, but the batch job takes extra 2-3 hours perday. But then there were lots of ad-hoc queries being run and creating bitmap indexes were very effective. Most successful way would be, to everyday drop these bitmaps before bacthjobs and recreate them after the batch jobs, but the tables were so huge that the creating time, extra batch jobs time Vs response times on queries did not work out. So, we dropped bitmaps and went ahead with Normal Indexes.. Hope this helps..
Thanks, ST2003

[slimdave]

I remember that 8.1.7.4 issue. Ah, happy days.

Long story short, dml ops that modify bitmap indexes are a bitch in any version, and i wouldn't trust the resulting index. It's not really an issue for the environmenti work in anymore because we disable the indexes before inserting new data, then rebuild them afterwards. sure is faster than inserting into a bitmap.

As for the cardinality issue, you're probably on the borderline of bitmap and btree, especially if you implement compression on the btree index. benchmarking is the way to go there, comparing index size and performance.

Have you implemented any physical row ordering, by the way? that really cuts down on bitmap index size.


[edited to insert spaces. i have toast crumbs stuck under my space key]

[sreddy]

Yes, I have experienced same issues what slimdave mentioned in our data warehosuing environment(8173). Either way it sucks for maintenance issues. B-Tree indexes to be created takes forever and bit map indexs have to be dropped and recreated each and every time you do massive data operations (DML).

Bit-map indexes goes thru compression and expansion algoritm for heavy DML which is big overhead and cause performance issues.

As Slimdave suggested proper testing needed specific to kinda activity on the database in your shop.

[rsuri]

Thanks for your response guys.
We have a developer who when learned about bitmap indexes, started converting every index into a bitmap - his only logic being that they were much smaller than the b*tree and the performance was about the same if not better than b*tree.
Evaluating the overhead/trouble we are getting into we sure do need to do some benchmarks.

Thanks,

-Rajeev

[slimdave]

Originally posted by rsuri
Thanks for your response guys.
We have a developer who when learned about bitmap indexes, started converting every index into a bitmap - his only logic being that they were much smaller than the b*tree and the performance was about the same if not better than b*tree.
Evaluating the overhead/trouble we are getting into we sure do need to do some benchmarks.

Thanks,

-Rajeev

I think that what your developer needs is some discipline imposed on him/her/it.

The tendency to grab hold of any shiny new features that the database offers without fully investigating the pro's and con's (like reading the doc's, searching metalink for bugs, testing fully) gives this person the Mark Of the Idiot stamped hard on their forehead.

I wouldn't let them near a database, personally.

[DaPi]

Originally posted by slimdave
. . . developer . . . . him/her/it.

"It" is the politically correct form.

[jmodic]

Originally posted by DaPi
"It" is the politically correct form.

And who are you to say this? Don't we have Mr.Hanky here to make the final judgement about politicall correctnes when developers are in question?

[DaPi]

Emulating the Master is surely the greatest compliment one can pay?