[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

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?


1 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now