Rebuild an index

The simplest way to rebuild and index is:

Alter index <index-name> rebuild tablespace <index-tablespace>;

This also has the advantage of not preventing the index from being used
whilst it is being rebuilt. The tablespace must be specified otherwise the
index will be rebuilt in the current users default tablespace.

Rebuilding the index also allows new storage parameters to be assigned
to the index.

In order to reduce the number of extents, when recreating the index
it would be advisable to set the initial extent to the current index
size. The current index size can be found by:Select sum(bytes) from dba_extents
where segment_name=’<index-name>’;

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: Brian Cryer
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.