Oracle does not choose proper Index

I have following 2 Oracle Queries:

Query1:Select col1, col2 from table1 where col1 = :value1 order by col2 Query2:Select col1, col2 from table1 where col1 = :value2 order by col2

Here are some facts:
table1 has around 1 million rows.
col1 has Index.
col2 has Index.
Query1 returns 1 Million rows.
Query2 returns only 2 rows.
Explain Plan shows that Optimizer uses Index on col2 for both queries.

Query1 is fast but Query2 takes over a minute to run ! This is the problem.

If I remove order by clause, both queries are fast.

How can make Oracle do the smart decision to use Index on col1 for Query2?

I can not put Hints since SQL is dynamically generated and while generating the Query I would not know which value (value1/value2) has less no. of rows.

Also creating BITMAP Index does fix the problem but this solution is not acceptable by our DBA since it will lock the rows during Insert/Update.

Ideal solution I am looking for is some change on database side that will force Oracle to use the right Index.

Any help will be appreciated.

Thanks,
Pritam (Pritam.Raut_at_marsh.com) Received on Thu Jul 21 2005 - 09:17:08 CDT