• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Oracle performance hit for 1 million record

 
Ranch Hand
Posts: 112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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"
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic