Using Bitmap Access Plans on Regular B-tree Indexes

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?

Thanks,

G
gswitzAsked:
Who is Participating?
 
sdstuberCommented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.