This discussion is archived
1 2 3 Previous Next 36 Replies Latest reply: May 30, 2008 5:35 AM by Aman.... Go to original post RSS
  • 15. Re: Trying to make use of bitmap indexes
    108476 Journeyer
    Currently Being Moderated
    Are you aware of the significant improvements in how bitmap indexes deal with DML statements
    Not the internals . . . .

    Are you offering to enlighten me, or is this a trap to insult me?
  • 16. Re: Trying to make use of bitmap indexes
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    A bitmap index entry basically consists of the indexed column or columns, a start rowid and an end rowid that specifies all the possible rows referenced by the specific bitmap index piece and finally all the bitmap bits, compressed as necessary.

    Prior to 10g, if a new index value didn't fit within the existing rowid ranges specified in an existing bitmap index piece, Oracle would create a new index piece for the index entry. This is both an expensive operation and inefficient from a storage point of view. Note many/most subsequent inserts would generally not fit within an existing rowid range and would require a new bitmap index entry piece.

    However, post 10g, Oracle will do it's damndest to fit a new index value into an existing bitmap piece, even it means changing the rowid ranges of an index entry. As a result, new inserts are much more efficient, don't generate as much redo and don't use anywhere near as much storage.

    As such, 10g bitmap indexes more efficient to navigate and don't necessarily need to be rebuilt after lots of DML activity.

    In short, bitmap indexes in 10g are nowhere as problematic with regard to the actual maintainance and storage implications of DML but they still have the same locking issues (worse even as there are likely to be fewer bitmap pieces).

    Note I discuss bitmap indexes in some detail in my seminar.

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 17. Re: Trying to make use of bitmap indexes
    Jonathan Lewis Oracle ACE Director
    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.
    Why do you assume that anybody reading this theead will be too stupid to notice that I was commenting on the following exchange, and that you've tried to avoid the issue:

    >
    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.


    >
    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?
    I'll take a guess that you have an internal rationalisation mechanism that says something like: "if I don't make a declarative statement, it doesn't matter what I say, it doesn't count as a lie."

    Why not just say that someone copied you an email that I sent them and quote what it said - I don't think there's anything I've said privately about your work that I haven't also said publicly.

    You'll have to explain what you mean by the use of quotation marks, by the way - the best guess I can make is that you're pretending (to yourself) that the email I send to the Oracle-L listserver counts as private email to friends.

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 18. Re: Trying to make use of bitmap indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    >
    Just to compare notes, I've see update on tables with
    three bitmap indexes take 20 wall-clock seconds
    each.
    Can you supply any information about the typical types of activity where you see this happen ?

    How many rows in the table, how many updated by the statement, what access path used to find the target rows, what fraction of the updates change the values in the indexed columns, how many rows per key value, and what sort of patterns of data distribution do you see in the indexed columns ?

    Does this happen for a single statement or is it when you use a large number of statements that each update a few rows - if the latter do you commit between statements or not ?

    Is this after a large delete on the table - if so do you commit after the delete or not ?

    Does this occur randomly, or fairly consistently - if the latter what wait states show up when you try a 10046 trace or sample v$session_event for the session ?

    There are various reasons why even serialised DML on a table with multiple bitmap indexes could take longer than you might expect - but if you can pin-point the cause there are cases where it is relatively easy to minimise the damage.

    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk
  • 19. Re: Trying to make use of bitmap indexes
    damorgan Oracle ACE Director
    Currently Being Moderated
    Don I think I can encapsulate how you've earned your current reputation in this business
    with the following two sentences. The first by me, the second from you.
    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.


    You are 100% incorrect by essentially every definition. Bitmap indexes, B*Tree indexes, Function based indexes ... they are used to solve business problems. If someone can't state the business problem in a single declarative sentence they don't belong in front of the keyboard. And, further, bitmap indexes as initially implemented by Oracle are very different from bitmap indexes in the latest releases (10.2.0.4 and 11.1.0.6).

    Reread the original post. You don't know if the person that posted it is in 9.2.0.1 or 11.1.0.6 or any version in between. Do you really think generic advice applies equally to all versions? You don't know how the database is being utilized either. Do you really think generic advice applies equally to all possible usages?
  • 20. Re: Trying to make use of bitmap indexes
    631129 Newbie
    Currently Being Moderated
    Hello, again!
    Just wanted to say that there is no JUST ONE
    business problems that depends on this, because this table is used in a lot of reports of different kinds (9.2.0.6.0 Ora version).
    I made a copy of the original table then on the test server and
    1: Created a B-tree index on one of these colums and executed a simple query
    like 'SELECT * FROM TABLE WHERE BTREE_INDEXED_COLUMN = SMTH '
    2: Created a Bitmap index on the same column and executed the same query

    The result was that in the second case the query worked MUCH MORE FASTER
    than that of when the column was indexed by B-Tree.
    So this is why I decided to pass to a Bitmap index on this and the other appropriate columns.
  • 21. Re: Trying to make use of bitmap indexes
    damorgan Oracle ACE Director
    Currently Being Moderated
    Whether one report or a thousand it appears the table is being used for reporting. That is useful information. How it is being loaded and other information would also be helpful in addressing the issue.

    Saying something is much faster is a good data point. But where in this thread can we see why it is faster? Where is the explain plan output? How do we know the B*Tree index was being used at all? How about the impact on DML or other queries?

    The point here is that to know the best solution you must test. Some people think they can dish out advice that is generically correct for all versions, all applications, all operating systems, etc. Reality has never worked that way and in our lifetimes never will.
  • 22. Re: Trying to make use of bitmap indexes
    311441 Employee ACE
    Currently Being Moderated
    Hi Fadai

    When it ran "MUCH MORE FASTER" with the bitmap index, how do you know it wasn't simply a result of having more associated table blocks cached as a result of your first run ?

    Do you actually have any idea why it ran faster the second time ?

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 23. Re: Trying to make use of bitmap indexes
    155651 Newbie
    Currently Being Moderated
    Another alternative to bitmap indexes is to create compressed indexes. Again index compression is costly when there are too many transactions

    http://www.myoracleguide.com/s/CompressedIndex.htm
  • 24. Re: Trying to make use of bitmap indexes
    548860 Newbie
    Currently Being Moderated
    Hi, you can also try the hint fast_mode.
    It seems to have a default value of false

    This is the default case:
    select count(*) 
       from item_country_config_t;
    
      COUNT(*)
    ----------
       6609172
    
    Elapsed: 00:00:04.15
    And here I have, eh, proved that it is faster with the hint.
    select /*+ fast_mode(true)*/ count(*) 
       from item_country_config_t;
    
      COUNT(*)
    ----------
       6609172
    
    Elapsed: 00:00:01.12
    / Ronnie
  • 25. Re: Trying to make use of bitmap indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    Ronnis,
    Can you please provide a link to this hint?Seems it is missing from the docs,
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#i8327
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28274/hintsref.htm#i8327
    I also couldnt find relevant for it from Google too.
    Aman....
  • 26. Re: Trying to make use of bitmap indexes
    427367 Newbie
    Currently Being Moderated
    He probably just had an outline or something for the query text without the hint.
  • 27. Re: Trying to make use of bitmap indexes
    548860 Newbie
    Currently Being Moderated
    Hehe, I thought that "fast=true" was something that all flavours of the IT industry would recognoze as a joke...

    Sorry, next time I will include the <sarcasm> tags :)
    Or at least the relevant info from autotrace showing that the second query had 0 physical reads...

    Best Regards
    Ronnis
  • 28. Re: Trying to make use of bitmap indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    LOL
    Heck!You know what I really got some bizzare search results with this parameter but I couldn't relate them to Oracle.And I was really amazed that is there really any hint which is like this?You are responsible for making me scratching my head for 15minutes trying to find what the heck this hint is and what it does ;-)!
    It was good one!
    Cheers
    Aman....
  • 29. Re: Trying to make use of bitmap indexes
    631129 Newbie
    Currently Being Moderated
    I flushed cached blocks from memory every time before executing the query.
    In 9i as far as I know there is smth like
    alter system set events ' immediate name trace flush '
    just like "alter system flush buffer_cache" in 10g.
    Well, B-tree index suits now well, I must say, but having low cardinality
    columns decided to test Bitmap indexes as well.
    So I'm not passing to it, jus leaving B-tree.
    Thank you all for suggestions.