This week's book giveaway is in the Design forum.
We're giving away four copies of Design for the Mind and have Victor S. Yocco on-line!
See this thread for details.
Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle performance hit for 1 million record

 
Ashish Agrawal
Ranch Hand
Posts: 112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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

thanks
Shailesh
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic