Using Bitmap Access Plans on Regular B-tree Indexes

Posted on 2011-05-02
Last Modified: 2012-05-11
Hi. I have a 10 million row table with b-tree indexes with one column each on columns Column_A, and Column_B. This is an OLTP table.

The SQL that needs to be tuned is causing a B-Tree to Bitmap conversion that is taking 100 seconds.

Using columns A and B together, I can get down to 2% of the table.

I'm considering adding Column_B to the INDEX with Column_A in order to speed up the query. I don't want to adversely affect the performance of other queries that may need to do a B-Tree to Bitmap conversion.

Can a B-Tree to Bitmap conversion be executed on a COMPOSITE INDEX?

Are there other things I should consider before adding the additional column?


Question by:gswitz
    1 Comment
    LVL 73

    Accepted Solution

    The bitmap conversion is because Oracle recognizes that it wants a composite index.

    If you don't want to impact queries that are already using each indvidual column index, then create a new composite index with both columns.

    To test your other queries, you could disable the other index after the composite is in place.  You'll likely see better performance across the board.


    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now