Hello All,
I got the solution.
The problem was Oracle was not using the index created on my order by column (Product_Name). There were 10 more indexes on other columns. Due to this oracle was using the default index which was on the primary key.
I added a hint to my select query forcing oracle to use the index on the above column ... something like this -
Select /*+ INDEX(a, IDX_PROD_NAME) */ * from Products a order by Product_Name; It reduced the query execution time from 120 secs to 0.2 secs.
Thank you.
- Ashish Agrawal.
[ February 04, 2005: Message edited by: Ashish Agrawal ]