High cardinality Bitmap indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: High cardinality Bitmap indexes

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407

    High cardinality Bitmap indexes

    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,
    Rajeev Suri

  2. #2
    Join Date
    Jul 2003
    Location
    Cincinnati
    Posts
    68
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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]
    Last edited by slimdave; 11-07-2003 at 12:56 PM.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    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.
    Reddy,Sam

  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    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
    Rajeev Suri

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    . . . developer . . . . him/her/it.
    "It" is the politically correct form.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Emulating the Master is surely the greatest compliment one can pay?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width