This discussion is archived
7 Replies Latest reply: Jun 9, 2011 2:01 AM by Jonathan Lewis RSS

Bitmap indexes

796772 Newbie
Currently Being Moderated
hi everyone,

There is table t
and there are several bitmap one-column indexes
i1 on t(c1)
i2 on t(c2)
i3 on t(c3)

Column C1 is not nullable.
Columns C2 and C3 is nullable.

There is query
select /*+ index_combine(t) */ 
       * 
  from t
 where c1 = :1 
   and c2 in (:2, 'X') 
   and c3 = :3
---------------------------------------
| Id  | Operation                     |
---------------------------------------
|   0 | SELECT STATEMENT              |
|   1 |  TABLE ACCESS BY INDEX ROWID  |
|   2 |   BITMAP CONVERSION TO ROWIDS |
|   3 |    BITMAP AND                 |
|   4 |     BITMAP INDEX SINGLE VALUE |
|   5 |     BITMAP INDEX SINGLE VALUE |
|   6 |     BITMAP OR                 |
|   7 |      BITMAP INDEX SINGLE VALUE|
|   8 |      BITMAP INDEX SINGLE VALUE|
---------------------------------------
Fine.

But if I create following indexes (and drop previous)

create bitmap index idx1 on t(C1, C2);
create bitmap index idx2 on t(C1, C3);

then the plan of the query above is following:
--------------------------------------
| Id  | Operation                    |
--------------------------------------
|   0 | SELECT STATEMENT             |
|   1 |  TABLE ACCESS BY INDEX ROWID |
|   2 |   BITMAP CONVERSION TO ROWIDS|
|   3 |    BITMAP INDEX SINGLE VALUE |
--------------------------------------
And the question:
why the plan does not consist BITMAP AND ?
Is it possible to scan both new indexes in the query with BITMAP AND?


Thanks
  • 1. Re: Bitmap indexes
    796772 Newbie
    Currently Being Moderated
    I have changed hint and somplified the query
    select /*+ index_combine(t idx1 idx2) */
           * 
      from t
     where c1 = :1 
       and c2 = :2
       and c3 = :3
    plan has not changed
    --------------------------------------
    | Id  | Operation                    |
    --------------------------------------
    |   0 | SELECT STATEMENT             |
    |   1 |  TABLE ACCESS BY INDEX ROWID |
    |   2 |   BITMAP CONVERSION TO ROWIDS|
    |   3 |    BITMAP INDEX SINGLE VALUE |
    --------------------------------------
  • 2. Re: Bitmap indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    This is not unreasonable behaviour.

    The index_combine() hint tells Oracle to use a combination of bitmap indexes taken from the supplied list. The hint doesn't tell Oracle it has to find a way to combine every index in the list.

    Presumably the arithmetic has told Oracle that a single index is sufficiently efficient that it doesn't need to use a second one. You may disagree with this strategy because you have a better understanding of the data patterns than the optimizer does.

    Technically you might be able to force the path you want by using the undocument bitmap_tree() hint - but you shouldn't use undocumented options in production.

    *[UPDATE]* I've just spent a few minutes playing with a data set where this (c1,c2) (c1,c3) type of index combination is obviously a good idea - and can't get the bitmap_tree() hint to force the path. I think this means there's a hole in the optimizer's legal strategies that you might have to fill by other methods.

    Regards
    Jonathan Lewis

    Edited by: Jonathan Lewis on Jun 7, 2011 6:24 PM
    See the update note
  • 3. Re: Bitmap indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    Sir,

    Can you please explain that where or in which kind of situation this hint, Index_combine is applicable?

    Regards
    Aman....
  • 4. Re: Bitmap indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Aman.... wrote:
    Sir,

    Can you please explain that where or in which kind of situation this hint, Index_combine is applicable?
    index_combine({table aliase} {list of index descriptors}) tells Oracle to create an access path that uses bitmap conversion on some combination of the listed indexes. Usually people expect to list bitmap indexes, but because Oracle 9i (and later) can do a btree/bitmap conversion, the list can include btree indexes.

    Examples:
    http://jonathanlewis.wordpress.com/2007/02/08/index-combine/
    http://jonathanlewis.wordpress.com/2007/06/05/design/


    As always, you might use index_combine() where you can see a good access path that the optimizer doesn't recognise automatically.

    Regards
    Jonathan Lewis
  • 5. Re: Bitmap indexes
    Aman.... Oracle ACE
    Currently Being Moderated
    Thanks so much sir . I shall read the blog posts now :=) .

    Regards
    Aman....
  • 6. Re: Bitmap indexes
    796772 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    I think this means there's a hole in the optimizer's legal strategies that you might have to fill by other methods.
    Do I right understand that it is impossible to combine bitmap non-one-column indexes?
  • 7. Re: Bitmap indexes
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    793769 wrote:
    Jonathan Lewis wrote:
    I think this means there's a hole in the optimizer's legal strategies that you might have to fill by other methods.
    Do I right understand that it is impossible to combine bitmap non-one-column indexes?
    No, you're generalising from the particular - thus are myths created.

    I have demonstrated a case where two bitmap indexes start with the same column+, and the optimizer therefore refuses to do a "bitmap and" between these two indexes when you have where clause that uses equality on the common first column and equalities on the seperate second columns. This is a very small subset of query patterns involving combinations of "non-one-column" (multi-column) indexes.

    For example - why don't you try recreating your (c1, c3) index as (c3, c1) to see what the optimizer can do ?
    In my example it produced the following path:
    ------------------------------------------------------------------------
    | Id  | Operation                    | Name    | Rows  | Bytes | Cost  |
    ------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |         |    10 |  1250 |     6 |
    |   1 |  TABLE ACCESS BY INDEX ROWID | T1      |    10 |  1250 |     6 |
    |   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |       |
    |   3 |    BITMAP AND                |         |       |       |       |
    |*  4 |     BITMAP INDEX SINGLE VALUE| T1_B1B2 |       |       |       |
    |   5 |     BITMAP MERGE             |         |       |       |       |
    |*  6 |      BITMAP INDEX RANGE SCAN | T1_B3B1 |       |       |       |
    ------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       4 - access("C1"=5 AND "C2"=50)
       6 - access("C3"=50)
           filter("C3"=50)
    So it is combining two multi column indexes - but it doesn't appear to be able to use the common column twice to make the second index access as efficient as possible. (This plan appeared for 10.2.0.3 and 11.2.0.2).

    Regards
    Jonathan Lewis

Legend

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