How to Tell when it’s Time to Rebuild Indexes in Oracle

Jan 15, 2020 by Robert Gravelle

Every so often, we need to rebuild indexes in Oracle, because indexes become fragmented over time. This causes their performance – and by extension – that of your database queries, to degrade. Hence, rebuilding indexes every now and again can be quite beneficial. Having said that, indexes should not be rebuilt to often, because it’s a resource intensive task. Worse, as an index is being rebuilt, locks will be placed on the index, preventing anyone from accessing it while the rebuilding occurs. Any queries trying to access this index in order to return the required results will be temporarily blocked, until the rebuild is complete.

In today’s blog, we’ll learn how often to build indexes and how to determine when an index needs to be rebuilt.

As mentioned in the introduction, rebuilding indexes is both a resource intensive and blocking task. Both these considerations make it ideal as an offline activity, to be run when as few users as possible are accessing a database. In general, this means during a scheduled maintenance window.

It is not really feasible to devise a catch-all plan with regard to when and how often to rebuild indexes. These decisions are highly dependent on the type of data you work with, as well as the indexes and queries that are utilized. With that in mind, here are a few guidelines regarding when to rebuild indexes:

• Rebuilding Indexes Nightly

  If your indexes fragment rapidly, and you have a nightly maintenance window that allows you to run the Rebuild Index task, in addition to all your other maintenance tasks, then, by all means, go ahead.

• Weekly, at minimum

  If you can’t rebuild indexes on a nightly basis, then, it should be done once a week at a minimum. If you wait much longer than a week, you risk hurting your SQL Server’s performance due to the negative impact of wasted empty space and logical fragmentation.

• Alternative scheduling

  If you don’t have a maintenance window accommodate this task at least once a week, then you need to pay close attention to how your indexes are faring.

In Oracle, you can get an idea of the current state of the index by using the ANALYZE INDEX VALIDATE STRUCTURE command. Here’s some sample output from the INDEX_STATS Table:

  SQL> ANALYZE INDEX IDX_GAM_ACCT VALIDATE STRUCTURE;
  Statement processed.
   
  SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
   
  NAME          HEIGHT      LF_ROWS    LF_BLKS    DEL_LF_ROW
  ------------- ----------- ---------- ---------- ----------
  DX_GAM_ACCT        2          1          3          6 
   
  1 row selected.
 

There are two rules of thumb to help determine if the index needs to be rebuilt:

1. If the index has height greater than four, rebuild the index.
2. The deleted leaf rows should be less than 20%.

In Oracle, you can use the Alter Index Rebuild command to rebuild indexes. It rebuilds a spatial index or a specified partition of a partitioned index.

ALTER INDEX REBUILD command has a few forms:

ALTER INDEX [schema.]index REBUILD  
     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ] 
     [{ NOPARALLEL | PARALLEL [ integer ] }] ;

OR

ALTER INDEX [schema.]index REBUILD ONLINE 
     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ] 
     [{ NOPARALLEL | PARALLEL [ integer ] }] ;

OR

ALTER INDEX [schema.]index REBUILD PARTITION partition  
     [PARAMETERS ('rebuild_params [physical_storage_params]' ) ];

Navicat for Oracle‘s Maintain Index facilities has a couple of useful options for handling unusable indexes:

• Rebuild

  To re-create an existing index or one of its partitions or subpartitions. If the index is marked unusable, then a successful rebuild will mark it usable.

• Make Unusable

  To make the index unusable. An unusable index must be rebuilt, or dropped and re-created, before it can be used.

In today’s blog, we learned how often to build indexes and how to determine when an index needs to be rebuilt.

If you’d like to learn more about Navicat for Oracle, visit the product page.