Oracle NOT using BITMAP index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Oracle NOT using BITMAP index

Hybrid View

  1. #1
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    I recently created a bitmap index on a table, with a 19 million rows, for a column that had only 120 distinct values (low cardinality). However, when I query the table (using the column in the where clause), the explain plan still returns a full table scan.

    I've analyzed the table (using compute), although that shouldn't matter since we are in Rule Based Optimizer mode. I end up having to use a hint to get Oracle to use the index.

    Can someone explain why this is happening?

    I've flushed the shared pool, thinking that it may be still using the previously parsed execution plan, but that still doesn't get it to use the index.

    I'm using 8.1.6, BTW.

  2. #2
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    some of the restrictions of Bitmapped indexes

    - Bitmapped indexes are not supported for trusted oracle

    - Not used by teh rule based optimiser

    - Cannot be used on a partitioned table as a global index
    siva prakash
    DBA

  3. #3
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Is that true????!!! When init.ora's OPTIMIZER_MODE=RULE, bitmap indexes are not used, and therefore would require a hint to use the index. Is this what you are saying?

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I tested in 8.1.7.4.
    Even with RULE based Optimizer, the query execution plan shows the bit map index being used when I added the hint like,
    SELECT /*+ INDEX(A TEST_BIT_IDX) */ *
    FROM TEST A
    WHERE A.STATE = 'NJ' ;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=RULE (Cost=105 Card=5002 Bytes=85
    034)

    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=105 Card=50
    02 Bytes=85034)

    2 1 BITMAP CONVERSION (TO ROWIDS)
    3 2 BITMAP INDEX (SINGLE VALUE) OF 'TEST_BIT_IDX'
    Post your init.ora file here.


    [Edited by tamilselvan on 09-11-2002 at 05:21 PM]

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by dbbyleo
    Is that true????!!! When init.ora's OPTIMIZER_MODE=RULE, bitmap indexes are not used, and therefore would require a hint to use the index. Is this what you are saying?
    Yes, exactly. With OPTIMIZER_MODE=RULE the only way for the bitmap index to be used is to provide a hint in your query. Without a hint (which in effect activates cost based optimizer instead of RBO), RBO will not even know about your bitmap index.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Thanks you guys for confirming that.

    So sorry for such a late reply from me. For some reason, this forum is not notifying me by email when a post is made to my threads.

    Anyhow, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width