• 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

Increasing performance through indexing

 
Ranch Hand
Posts: 258
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Friends,

We have one application which have oracle 10g as a backend. Before somedays ago it became very slow. There was no problem with coding of the application or closing of the connections.

But we did indexing on the database tables to check as a trial. And it again running now fast as it was used to at start. Can you tell me exactly what would have made it to give good performance after indexing?
 
author
Posts: 3285
13
Mac OS X Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Vijay,

Did you add new indexes? Or did you simply as the RDMS to re-index?

I'm assuming the 2nd case. RDMS indexes can degrade over time (especially if they are large and they are indexing data that changes rapidly). Basically Re-indexing performs a job similar to defragmenting your hard drive. I've personally had to get our DBAs to do it on a large real time pricing table.
 
vijay jamadade
Ranch Hand
Posts: 258
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Did you add new indexes? Or did you simply as the RDMS to re-index?



Yes I did the second option.
As per your explanation regarding that this reindexing gets applied to the existing data and I need to repeat it after the table will get more data added. Am I right?

Thanks Martijn.
 
Martijn Verburg
author
Posts: 3285
13
Mac OS X Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd schedule it as a regular job (during a time where users aren't actively using the database). Depending on how often your indexes degrade will depend on how often you have to run the job (we run ours about every 3 months).
 
Rancher
Posts: 4803
7
Mac OS X VI Editor Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by vijay jamadade:
As per your explanation regarding that this reindexing gets applied to the existing data and I need to repeat it after the table will get more data added.



As Martijn wrote, you need to do it periodically, as needed. How often is hard to predict. Clearly as you add more records. And if you change the field values in the rows often, you need to do it more often.

The trick is to figure out how often to do it without having the indexing get in the way of production. Its a non-trivial balancing act.
 
Saloon Keeper
Posts: 27764
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is why enterprise DBMS's require full-time DBAs. Keeping service levels optimized is one of their primary functions.
 
Ranch Hand
Posts: 1327
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
how many number of records are there in your database tables?
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic