This discussion is archived
10 Replies Latest reply: Sep 2, 2013 9:33 PM by Girish Sharma RSS

Why we cannot specify BITMAP when creating a global partitioned index.

core_oracle Newbie
Currently Being Moderated

Hi

why it is that we cannot create a bitmap index on globally partitioned table and only local bitmap index is possible.

  • 1. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    sb92075 Guru
    Currently Being Moderated

    core_oracle wrote:

     

    Hi

    why it is that we cannot create a bitmap index on globally partitioned table and only local bitmap index is possible.

    How can we reproduce what you report?

     

    How do I ask a question on the forums?

    https://forums.oracle.com/message/9362002#9362002

  • 2. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    StefanKoehler Explorer
    Currently Being Moderated

    Hi core_oracle,

     

    > why it is that we cannot create a bitmap index on globally partitioned table

    What is a globally partitioned table? I assume that this is a typo and you mean "global partitioned index".

     

    > and only local bitmap index is possible

    Because of it is a restriction of the BITMAP index - you just need to read the documentation: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm

    Restrictions on Bitmap Indexes Bitmap indexes are subject to the following restrictions:

    • You cannot specify BITMAP when creating a global partitioned index.
    • You cannot create a bitmap secondary index on an index-organized table unless the index-organized table has a mapping table associated with it.
    • You cannot specify both UNIQUE and BITMAP.
    • You cannot specify BITMAP for a domain index.
    • A bitmap index can have a maximum of 30 columns.

     

    Regards

    Stefan

  • 3. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    JohnWatson Guru
    Currently Being Moderated

    core_oracle wrote:

     

    Hi

    why it is that we cannot create a bitmap index on globally partitioned table and only local bitmap index is possible.

    I think you are asking why this restriction is in place? Stefan's quote of the docs does not explain this. If you think it through, I would say that the reason is to prevent us from doing something silly.

    A global partitioned index must be prefixed (why? That would be another question) so your globally partitioned bitmap index would have to be partitioned on the index key. Something like this:

    orclz>

    orclz> create table parts(c1 number,c2 number) partition by hash (c1) partitions 2;

     

    Table created.

     

    orclz> create bitmap index c2bmi on parts(c2) global partition by hash (c2) partitions 2;

    create bitmap index c2bmi on parts(c2) global partition by hash (c2) partitions 2

                                           *

    ERROR at line 1:

    ORA-25113: GLOBAL may not be used with a bitmap index

     

     

    orclz>

    But if this were possible, what would it give you? Nothing. You would not get the usual benefit of reducing contention for concurrent inserts, because of the need to lock entire blocks of  a bitmap index (and therefore ranges of rows) when doing DML. Range partitioning a bitmap index would be ludicrous, because of the need to use equality predicates to get real value from bitmaps. Even with hash partitions, you would not get any benefit from partition pruning, because using equality predicates on a bitmap index in effect prunes the index already: that is what a bitmap index is for. So it seems to me that a globally partitioned bitmap index would deliver no benefit, while adding complexity and problems of index maintenance.

     

    I cannot think of a technology limitation, so I suspect that Uncle Oracle is protecting us from ourselves.

    I am of course open to correction on this.

  • 4. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    StefanKoehler Explorer
    Currently Being Moderated

    Hi,

    that is the difference between "Why can we not specify something?" and "Why is that restriction in place?"

     

    > .... , because of the need to use equality predicates to get real value from bitmaps.

    No, not really. The query just needs to be a bit more complex than "WHERE COLUMN <> VALUE". Richard Foote wrote two blogs about this (not equal predicates): Bitmap Indexes and Not Equal (Holy Holy) and Bitmap Indexes and Not Equal Part II (Sheep)

     

     

    Another important point to mention is (which can explain the limitation in some context as well): Think about how index entries are stored by using a bitmap index (rowid range and bitmap sequence). The rowid includes the data object id and that would be a mess (indexed column values can be spread over all table partitions) in case of a "global partitioned bitmap" (theoretical) on a different column than the table partition key. Just think about the rowid range coverage of such entries and the corresponding bitmap sequence. What would be the benefit of such a global partitioned bitmap index? Quoting Richard Foote again (for the rowid range coverage):

    However, if the bitmap entry only has a start and end rowid range, how does it actually know the location of all the corresponding rows, as there could be differing number of rows for any of the given data blocks. How does it know just how many rows actually exist within the rowid range ?

    The answer is that it can’t possibly know. Therefore, Oracle makes a very important assumption and based on the definition of the table, determines the maximum number of rows that could potentially fit within a block and assigns a bit for every possible rowid that could in theory exist within the specified rowid range.

    If the rowid actually corresponds to an existing row, then the bit is set accordingly depending on the value of the indexed column for that row. If the rowid doesn’t exist (or doesn’t exist yet), then the corresponding bit is simply set to a 0. If there are a whole bunch of consecutive 0s for rows that don’t exist, they get compressed and the overheads are minimised. However, the value of a bit set to 0 can therefore potentially mean one of two things. It could mean that the row exists but doesn’t have the value represented by the index entry or it could mean that the row simply doesn’t exist at all. There is no way for Oracle to tell the difference between these two scenarios.

     

    Regards

    Stefan

  • 5. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    core_oracle Newbie
    Currently Being Moderated

    Thanks John and Stefan.

    Yes my question was why there is a limitation that we cannot create global bitmap index on partitioned table.Oracle documentation says that we need to create local index only and that's it. The why is not provided. that's the reason I asked this question.

    Best Regards

  • 6. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    sb92075 Guru
    Currently Being Moderated

    core_oracle wrote:

     

    Thanks John and Stefan.

    Yes my question was why there is a limitation that we cannot create global bitmap index on partitioned table.Oracle documentation says that we need to create local index only and that's it. The why is not provided. that's the reason I asked this question.

    Best Regards

    Why does it matter why it is not allowed?

  • 7. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    rp0428 Guru
    Currently Being Moderated

    The rowid includes the data object id and that would be a mess (indexed column values can be spread over all table partitions) in case of a "global partitioned bitmap" (theoretical) on a different column than the table partition key.

    Huh?

     

    Certainly the fact that the start/end rowid range includes the object id and each segment of a table has a different object id  means that there can be a greater number of smaller rowid ranges and that could affect both the storage and the performance of a bitmap index.

     

    But what does 'on a different column than the table partition key' mean or have to do with it? As John already said

    A global partitioned index must be prefixed

    So at least SOME of the leading key columns MUST BE part of the index.

    Quoting Richard Foote again (for the rowid range coverage):

    None of what you quoted is relevant. Those quotes refer to the use of the Hakan factor and there is only ONE Hakan factor for a table whether it is partitioned or not.

  • 8. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    sb92075 Guru
    Currently Being Moderated

    is the limitation Oracle's or ANSI's?

  • 9. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    Richard Foote Employee ACE
    Currently Being Moderated

    It all comes down to the makeup of the bitmap index. An index entry is comprised of the indexed value and a start/end rowid range followed by all the index bits within the rowid range denoting whether each row does or does not contain the indexed value. However, each indexed value can have multiple index entries (or pieces) if the overall index entry were too big in size or if the rowid range needs to be logically split.

     

    For global indexes, the rowid needs to be extended to include the object id of the table partition containing the row as the remaining rowid elements are not unique for partitioned tables. However, the bitmap rowid range can’t span object id ranges as Oracle will have no clear way of determining valid rowid values within such a range. Therefore, each indexed value will need to have at least an index entry piece for each and every table partition. That being the case, there would be no benefit (but all the maintenance disadvantages) in such a global bitmap index over an equivalent local index.

     

    Cheers

     

    Richard Foote

    http://richardfoote.wordpress.com/

  • 10. Re: Why we cannot specify BITMAP when creating a global partitioned index.
    Girish Sharma Guru
    Currently Being Moderated

    Why it is not possible, as it has answered by Richard Foote above by saying that Oracle have no clear way of how to extend the rowids of bitmap index to include the object id of the table partition.  Oracle includes feature requests/enhancements based upon business need i.e. if you can show us how will you get performance / business enhancement if you gets bitmap global partitioned index in compare to bitmap local partitioned index or B-tree index.

     

    Regards

    Girish Sharma

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points