Cry How To...

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.

If the index is corrupt, it must be dropped and recreated.

1. Firstly, identify the original creation parameters:

   SELECT COLUMN_NAME FROM USER_IND_COLUMNS
   WHERE INDEX_NAME = 'index_name';

   SELECT TABLE_NAME, UNIQUENESS, TABLESPACE_NAME,
       INITIAL_EXENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS,
       PCT_INCREASE, PCT_FREE
   FROM USER_INDEXES WHERE INDEX_NAME = 'index_name';

   The above will give you the columns on which the index is based.

2. And then drop the index and recreate it:

   Drop index <index-name>;

   Create [UNIQUE] index <index-name>
   On <table-name>
   ( <column-1> [ , <column-n> ] )
   tablespace <tablespace>
   PCTFREE <pct-free>
   STORAGE (
   NEXT <size>K
   INITIAL <size>K
   PCTINCREASE <pct_increase>
   MIN_EXTENTS <min_extents>
   MAX_EXTENTS <max_extents> );

Note:

1. 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>’;
2. Alternately see ‘Obtain information about an index’ for a way of determining the exact index size.
3. The primary key cannot be dropped or recreated in this way.