Cry about...
Oracle How To ...

Rebuilding indexes

Periodically, and typically after large deletes or inserts, it is worth rebuilding indexes. The SQL for this is:

Alter index <index_name> rebuild;

Alternatively, the following performs the same, but avoids writing to the redo logs and thus speeds up the index rebuild:

Alter index <index_name> rebuild unrecoverable;

Note: If performing this under Oracle 7.3 then be sure to specify the destination tablespace, ie:

Alter index <index_name> rebuild tablespace <tablespace>;

Otherwise the index will be moved to the temporary tablespace.

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.

About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.