File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Oracle performance hit for 1 million record Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Oracle performance hit for 1 million record" Watch "Oracle performance hit for 1 million record" New topic

Oracle performance hit for 1 million record

Ashish Agrawal
Ranch Hand

Joined: Nov 02, 2003
Posts: 112
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 Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1082

Is there any where clause in your query !!!

If yes !! Then I recommend to have index based on you where clause.
also provide timings without order by clause


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
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.
Ashish Agrawal
Ranch Hand

Joined: Nov 02, 2003
Posts: 112
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 ]
I agree. Here's the link:
subject: Oracle performance hit for 1 million record
It's not a secret anymore!