This discussion is archived
1 2 3 Previous Next 36 Replies Latest reply: May 30, 2008 5:35 AM by Aman.... RSS

Trying to make use of bitmap indexes

631129 Newbie
Currently Being Moderated
Hello!
I have a table that contains about 16 mill rows and each night about
60.000-70.000 rows are proccessed against the table so that part of the rows
is updated and another part is inserted.
The table contains three IDEAL columns for bitmap indexes the first of which
may have only two, the second three and the third four distinct values.
I was planning to change the index type on these columns to BITMAP but
Oracle doesnt recommend to build BITMAP indexes on heavily updated or inserted
columns.
So the only use of bitmap indexes turns out to be read-only tables.
From the other hand a sloution might be dropping indexes before the load and rebuilding them after the load has completed what can lead to often tablespace fragmentations.
So, the question is how can I use bitmap indexes in a case like this one?
What are wayouts?
Thank you very mcuh for the reply.
  • 1. Re: Trying to make use of bitmap indexes
    damorgan Oracle ACE Director
    Currently Being Moderated
    You are asking the wrong question for the wrong reason.

    One does not choose a technology and then go looking for someplace to use it. One looks at a business problem and then seeks the appropriate technology to solve it.

    What business problem are you facing and in what version?

    Lets assume you have 10.2.0.2 or above and the business problem is that a query is too slow. Ok ... what query? Where's the explain plan output? Do you know that any index will be used in resolving the query?

    Start from the right place to get where you want to go.

    BTW: Bitmap indexes are very valuable in many situations but those situations are not defined as READ ONLY tables. Read Jonathan Lewis' published remarks on the subject for in-depth knowledge.
  • 2. Re: Trying to make use of bitmap indexes
    108476 Journeyer
    Currently Being Moderated
    Hi,
    I was planning to change the index type on these columns to BITMAP but
    Oracle doesnt recommend to build BITMAP indexes on heavily updated or inserted
    columns.

    Yes, but in a batch environment the bitmaps are dropped, and rebuilt after the nightly updates.
    dropping indexes before the load and rebuilding them after the load has completed what can lead to often tablespace fragmentations.
    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:

    http://www.dba-oracle.com/oracle_news/2005_6_14_indexes_bad_ugly.htm

    --------------------

    Hope this helps. . .

    Donald K. Burleson
    Oracle Press author
    Author of "Oracle Tuning: The Definitive Reference":
    http://www.dba-oracle.com/bp/s_oracle_tuning_book.htm
  • 3. Re: Trying to make use of bitmap indexes
    108476 Journeyer
    Currently Being Moderated
    You are asking the wrong question for the wrong reason.
    That's ridiculous, arrogant and pretentious.
    What business problem are you facing and in what version?
    That's none of your business. It has NOTHING to do 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.
  • 4. Re: Trying to make use of bitmap indexes
    153119 Pro
    Currently Being Moderated
    Don,

    Please explain what kind of fragmentation rebuilding indexes resolves.
    With a working example please.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 5. Re: Trying to make use of bitmap indexes
    548860 Newbie
    Currently Being Moderated
    Hi,

    Do you have a problem with the update process, or are you just asking for opinions on using bitmap index on tables that gets updated?

    I work in a batch oriented DSS environment, and 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.
    Having the indexes in place does slow down the process, but that is a trade off you have to do. I tend to look at the tables as "read-mostly", because 23 and a half hour of the day they are read-only, and the last 30 minutes they are "write-only".

    I use the following rules of thumb:
    If I update most of the rows in table, and the total number of rows stays somewhat constant, I drop & rebuild.

    If I'm working with a "daily" data table, that collect "todays sales", or similar, I tend to load all new rows in a separate table, then build the bitmap index, gather stats on this table, and finally using partition exchange into the target table.


    Best Regards
    Ronnis
  • 6. Re: Trying to make use of bitmap indexes
    633045 Newbie
    Currently Being Moderated
    @Ronnis
    thats a smart approach ronnis. well, i follow somewhat the same kind of procedure. as per business policy we have to keep data of current month + last 2 months data in the main partitioned table which is a very volatile table having inserts of around 45 lac average rows per day. at the end of the month, we have to create a separate table for the last months out of business policy which would be used only for queries. so, first i create a non partitioned table of the same structure of the main online table and using exchange partition jus shift the entire data within few seconds. that works amazingly faster.
  • 7. Re: Trying to make use of bitmap indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    What business problem are you facing and in what version?
    That's none of your business. It has NOTHING to do
    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.
    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.

    You can't have it both ways.

    (I've lost count of the number of times I've pointed this out in the last 48 hours - so from now on I think I'll just have to call you "both ways Burleson" whenever you try to pull that stunt again).


    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 8. Re: Trying to make use of bitmap indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    The table contains three IDEAL columns for bitmap indexes the first of which
    may have only two, the second three and the third four distinct values.
    Contrary 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:
        col1 = '1_of_2'
    and col2 = '1_of_3'
    and col3 = '1_of_4'
    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.

    There are various special circumstance that might make the indexes effective for querying, though. (Note - at this point I'm not considering the impact on inserts, updates and deletes). The most obvious example is where all three columns each have at least one very repetitive value and all your queries are trying to find data for the remaining "rare" values. If this is the case then you need to index the columns and collect histograms on the columns so that the optimiser can model the data correctly; and then you may also need to modify your SQL to ensure that your queries against these columns always use literal values, not bind variables.

    If some of your queries are supposed to return small amounts of data, there are various mechanisms you could use to do this efficiently. If your queries are always going to return large amounts of data, then there are other strategies that are likely to be more appropriate.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 9. Re: Trying to make use of bitmap indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Ronnis,
    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.
    Interesting! In my experience, updates to tables 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?
    If I update most of the rows in table, and the total number of rows stays somewhat constant, I drop & rebuild.
    That's an excellent rule-of-thumb!
  • 10. Re: Trying to make use of bitmap indexes
    108476 Journeyer
    Currently Being Moderated
    But when Daniel Morgan asks for more information you abuse him for asking questions.
    Morgan wrote: "You are asking the wrong question for the wrong reason."

    1 - It's not a question, genius

    2 - Morgan was the one who was abusive.

    "You are asking the wrong question for the wrong reason.", that's just rude.

    Anyone with good manners would not say such a thing.

    The OP's question makes perfect sense to me.
    so from now on I think I'll just have to call you "both ways Burleson"
    You have called me a lot worse! Do you really think that your "friends" don't share your e-mails with me?
  • 11. Re: Trying to make use of bitmap indexes
    548860 Newbie
    Currently Being Moderated
    Hi Don,
    Interesting! In my experience, updates to tables
    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?
    I 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. :)

    As for using additional server resources to speed things up... I am lucky enough to have the option of redesigning (I'm a developer). And I like it!
    Our server is allready a monster, and if it cannot execute my code fast enough, I'm usually doing something inherently non-scalable. Fixing that, yields better results in my (admittedly limited) experience.

    Ronnis
  • 12. Re: Trying to make use of bitmap indexes
    108476 Journeyer
    Currently Being Moderated
    Hi Ronnis,
    I just accept the increased runtime.
    Just to compare notes, I've see update on tables with three bitmap indexes take 20 wall-clock seconds each.

    What kind of response time do you see, per update?

    For how many bitmap updates?

    Do you have any special tricks or techniques?

    Pleae advise. Thanks!
  • 13. Re: Trying to make use of bitmap indexes
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    Are you aware of the significant improvements in how bitmap indexes deal with DML statements since 10g and why they can be considered for single user concurrent DML environments ?

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 14. Re: Trying to make use of bitmap indexes
    311441 Employee ACE
    Currently Being Moderated
    As Jonathan has suggested, columns with really low cardinality can be as useless to index with bitmaps as with normal b-tree indexes. It depends on how many bitmap indexes are being used in combination and the overall selectivity of the SQL.

    Note also that columns with many many 1,000s of distinct values can be useful candidates for bitmap indexes, they don't need to have a handful of values as often claimed.

    You may find this post of mine useful:

    http://richardfoote.wordpress.com/2008/02/01/bitmap-indexes-with-many-distinct-column-values-wotsuh-the-deal/

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
1 2 3 Previous Next