Forum Stats

  • 3,770,497 Users
  • 2,253,126 Discussions
  • 7,875,486 Comments

Discussions

Bitmap index column goes for full table scan

2»

Answers

  • 34MCA2K2
    34MCA2K2 Member Posts: 442
    jeneesh wrote:

    Normally BITMAP indexes are not much advisable for OLTP systems.. It will cause a lot of locking issues - For example, multiple rows may get locked during a single row UPDATE.
    I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.

    Regards,
  • >
    total number of rows in the table = 1800 ;
    distinct value of type_ids are 101,102,103

    so i created a bit map index on it.

    CREATE BITMAP INDEX btmp_typeid ON transaction_details
    (type_id)
    LOGGING
    NOPARALLEL;

    after creating the index, the explain plan shows the same. why it goes for full table scan?.
    >
    Why would Oracle use that index to retrieve 1700 rows of an 1800 row table? That doesn't make any sense.

    As you already showed Oracle will use the plan with the lowest cost and that is the full table scan.
  • >
    I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.
    >
    See my full reply in this thread
    2399392
    >
    ETL is affected because DML operations (INSERT/UPDATE/DELETE) on tables with bitmapped indexes can have serious performance issues due to the serialization involved. Updating a single bit-mapped column value (e.g. from 'M' to 'F' for gender) requires both bitmapped index blocks to be locked until the update is complete. A bitmap index stored ROWID ranges (min rowid - max rowid) than can span many, many records. The entire 'range' of rowids is locked in order to change just one value.

    To change from 'M' the 'M' rowid range for that one row is locked and the ROWID must be removed from the range byt clearing the bit. To change to 'F' the 'F' rowid id range needs to be found, locked and the bit set that corresponds to that rowid. No other rows with rowids in the range can be changed since this is a serial operation. If the range includes 1000 rows and they all need changed it takes 1000 serial operations.
  • >
    BITMAP indexes are created and are most appropriate for columns having low distinct values.
    >
    That MYTH has been busted for many years now by many people.

    See Richard Foote's series of articles about this myth and many others regarding indexes.
    http://richardfoote.wordpress.com/2010/02/18/myth-bitmap-indexes-with-high-distinct-columns-blow-out/
    >
    One of the great myths in Oracle is that bitmap indexes are only suitable and should only be used with columns that have so-called low cardinality (few distinct) values
    . . .
  • user648773
    user648773 Member Posts: 59
    did you try a normal index? the bitmap is usefull for safe space when you have few values (is not your problem becuase the table is small), and because you can use more than one (with bitmap you can acces by the table by more than one index, with normal optimezer have to choose one, this is correct for you because you have one condition)

    did you collect statistics on the table?
  • Manik
    Manik Member Posts: 2,906 Gold Trophy
    rp0428 wrote:
    >
    BITMAP indexes are created and are most appropriate for columns having low distinct values.
    >
    That MYTH has been busted for many years now by many people.

    See Richard Foote's series of articles about this myth and many others regarding indexes.
    http://richardfoote.wordpress.com/2010/02/18/myth-bitmap-indexes-with-high-distinct-columns-blow-out/
    >
    One of the great myths in Oracle is that bitmap indexes are only suitable and should only be used with columns that have so-called low cardinality (few distinct) values
    . . .
    >
    USED in DW is fine.. I am not arguing that..

    But learnt something from that link.. Thanks RP. This completely changed my view about BITMAP indexes.
    I like the last sentance there :
    The next time you unfortunately read that bitmap indexes should only be used with very “few” distinct values and would be “huge” otherwise, well hopefully you’ll appreciate that’s simply not correct.
    ;)

    I should have read thoroughly the documentation where it says :

    http://docs.oracle.com/cd/B28359_01/server.111/b28313/indexes.htm
    A gender column, which has only two distinct values (male and female), is optimal for a bitmap index. However, data warehouse administrators also build bitmap indexes on columns with higher cardinalities.
    Thanks for sharing!!!

    Cheers,
    Manik.
    Manik
  • jeneesh
    jeneesh Member Posts: 7,168
    34MCA2K2 wrote:
    I am sorry for being ignorant, can you please cite any scenario of locking due to bitmap indices? A link can be useful as well.
    Sorry for the late response. rp0428's post is clear enough for you to understand the issue.

    You could also go through this discussion at asktom : Bitmap indexes and locking
    jeneesh
  • 890271
    890271 Member Posts: 132
    edited Apr 6, 2013 4:39AM
    Thank you all for sharing your ideas. im little bit confused with this concepts . i will so through with the links shared.

    Thanks again for all.

    Edited by: 887268 on Apr 6, 2013 1:39 AM
This discussion has been closed.