Is there a need to keep refreshing the indexes from time-to-time?

Question

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?

Answer 1

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).

Answer 2

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.

Answer 3

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.