This week's book giveaway is in the Kotlin forum.
We're giving away four copies of Kotlin in Action and have Dmitry Jemerov & Svetlana Isakova on-line!
See this thread for details.
Win a copy of Kotlin in Action this week in the Kotlin forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Increasing performance through indexing  RSS feed

 
vijay jamadade
Ranch Hand
Posts: 246
1
  • Mark post as helpful
  • send pies
  • 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?
 
Martijn Verburg
author
Bartender
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • 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: 246
1
  • Mark post as helpful
  • send pies
  • 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
Bartender
Posts: 3275
5
Eclipse IDE Java Mac OS X
  • Mark post as helpful
  • send pies
  • 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).
 
Pat Farrell
Rancher
Posts: 4686
7
Linux Mac OS X VI Editor
  • Mark post as helpful
  • send pies
  • 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.
 
Tim Holloway
Bartender
Posts: 18709
71
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • 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.
 
Billy Tsai
Ranch Hand
Posts: 1307
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
how many number of records are there in your database tables?
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!