Hello, My client is using a product for managing data consisting of millions of records. My problem is related to sorting of data on an alphanumeric column - Product_Name. A non-clustered index is created on it. Product_Id is the primary key. It is taking more than 20 secs to display 100 records. The problem is oracle specific, because the query itself is taking that much time.
The query is something like - "select <column-names....> from Products order by Product_Name"
Shailesh makes an excellent point. You said there are millions of records yet it displays only 100 records. Clearly your where clause is the problem, for once Oracle narrows the result set down to 100 rows it can't atke but a few miilliseconds to sort this at most. Can you provide the whole query and, better still, the plan that Oracle is using to execute the query?
My cat can sort 100 records in under a second -- no doubt Oracle can too.
Joined: Nov 02, 2003
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.