Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question is common for all the database products I use, viz. SQL Server, MySQL, Oracle and PostgreSQL.

I have created a fresh database and the tables, at present, have no records. I have created indexes on all such columns of tables which are needed in queries often. I want to know how indexes are physically (clustered) and logically (non-clustered) updated when I keep on adding records in tables? Is the index updated automatically or do I need to update the indexes manually, from time-to-time?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

The values in the index itself will update automatically. I can only speak for SQL Server but indexes can often need rebuilding or reorganising due to fragmentation occurring - particularly if it is on a table which is regularly updated. Index fragmentation can be monitored through the sys.dm_db_index_physical_stats DMV:

http://msdn.microsoft.com/en-us/library/ms188917.aspx

The best strategy is normally to use a script which looks for indexes over a certain level of fragmentation and then rebuilds or reorganizes based on this value. I know a lot of people use Ola Hallengren's scripts:

http://ola.hallengren.com/

In our production databases we run scripts similar to this every night to check for fragmentation (although this is possibly overkill).

share|improve this answer

Indexes are automatically updated regarding the what is stored in them (the column values of the rows that are indexed).

However some DBMS require regular maintenance (aka "rebuild") of them in order to optimize the storage of the index values.

Older versions of Microsoft SQL Server required an index rebuild on a regular basis to keep them effecient. But I don't know if that is still true with newer versions (2005,2008)

There is no need to recreate indexes in Oracle.

The (auto)vacuum process of PostgreSQL should take care of reclaiming space, but they sometimes (rarely though) require a rebuild as well.

share|improve this answer
3  
A minor nitpick. Assuming that the indexes are common, everyday b*-tree indexes, you're absolutely right that they'll be updated automatically in Oracle. But there are many types of indexes in Oracle some of which may not automatically refresh. Oracle Text indexes, for example, often need to be explicitly refreshed after new documents are loaded. –  Justin Cave Sep 13 '11 at 20:33
    
@Justin Cave: very good point. Thanks for mentioning that –  a_horse_with_no_name Sep 13 '11 at 20:58

The indexes that you have created are automatically mangaged by the database server.

Now, from the time you need to login and do some maintenance

  1. make sure old records really got removed
  2. make sure all the new ones really got added in the correct place

In PostgreSQL, I know you need to vacuum on indexes to clean up junk.

In MySQL, I know you need to run optimize to make sure the indexes are sorted correctly and have there correct sub indexes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.