Ask the Expert

Alternative solution to BITMAP index

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: