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?

3 Answers 3


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:


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:


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


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.

  • 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. Commented Sep 13, 2011 at 20:33
  • @Justin Cave: very good point. Thanks for mentioning that
    – user1822
    Commented Sep 13, 2011 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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