Q

Alternative solution to BITMAP index

I have two Oracle queries. When I do Explain Plan on these queries, they both use Index on col2 and not col1. Query1 is fast. Query2 takes over three minutes! This is the problem. I created BITMAP Index on col1 and both queries started working fast, but this solution is NOT acceptable by our DBA, as BITMAP Index will lock records during Insert/Update. Can anybody suggest some other solution?

I have the following Oracle queries:

Query1:
Select /*+ FIRST_ROWS */ col1, col2
From table1
Where col1 = 'value1'
Order By col2

Query2: Select /*+ FIRST_ROWS */ col1, col2 From table1 Where col1 = 'value2' Order By col2

Here are some facts:
Table1 has around 1 million rows.
Query1 returns around 1 million rows.
Query2 returns only two rows.
There is Index on col1 and col2.

When I do Explain Plan on these queries, they both use Index on col2 and not col1.

Query1 is fast. Query2 takes over three minutes! This is the problem.

I created BITMAP Index on col1 and both queries started working fast, but this solution is NOT acceptable by our DBA, as BITMAP Index will lock records during Insert/Update.

Can anybody suggest some other solution? Ideally Oracle should use Index on col2 for Query1 (which it does), and for Query2 it should use Index on col1 (which it does not). How can I force the optimizer to do this?

It would be helpful to know the version of Oracle you're using.

Here's what I'd suggest. First, get a baseline: Make sure you have up-to-date statistics on the table and its indexes. Remove the first_rows hint. Run the queries again to see what the optimizer does. If the results are what you want, great. If they're not, then since the distribution of values in col1 is certainly not uniform, you should create a histogram on col1 by using dbms_stats:

dbms_stats.gather_table_stats(null,'TABLE1',method_opt=>'for columns
col1 size 10')

Then test your queries again.

The "size" value specifies the number of buckets in the histogram; you will probably want to experiment with different values until you get the performance results you want.

This was first published in July 2005

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close