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?
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.