QUESTION POSED ON: 25 July 2005
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?
|
