Rebuild an index

The simplest way to rebuild an 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.

-------------------------------------------------------------------------------------------------------------

Obtain information about an index

General details about the index can also be found by:

Analyze index <index-name> compute statistics;Select * from user_indexes
where index_name= ‘<index-name>’;
 To obtain further detail about an index:

Analyze index <index-name> validate structure;
The command:

Validate index <index-name>; 

performs the same function.

This places detailed information about the index in the table INDEX_STATS. This table can only contain one row, describing only the one index. This SQL also verifies the integrity of each data block in the index and checks for block corruption.

For example, to get the size of an index:
validate index <index_name>;

select name "INDEX NAME", blocks * 8192 "BYTES ALLOCATED",
btree_space "BYTES USED",
(btree_space / (blocks * 8192))*100 "PERCENT USED"
from index_stats;
 This assumes a block size of 8K (i.e. 8192 bytes). It shows the number of bytes allocated to the index and the number of bytes actually used.
Note that it does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. To check this:

Analyze table <table-name> validate structure cascade;
-------------------------------------------------------------------------------------------------------------
 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.