reorganizing regenerated indexes

Summary

Symptom
You have degenerated Indexes in your Oracle database.

Other terms
INDEX, ORACLE, PERFORMANCE, REBUILD, COALESCE, REORG

Reason and Prerequisites
In Transaction DB02 you see bad values for the Storage Quality of some indexes and want to reorganize these. In order to display the Storage Quality proceed as follows:
DB02 (Oracle) --> Detailed analysis --> <enter table or index> -->Detailed analysis --> Analyze Index --> Storage Quality

Solution
In order to reorganize a degenerated index you have 3 options

1) DROP INDEX ..., and CREATE INDEX ...
           Advantages

                    * none

           Disadvantages

                    * long runtimes

2) ALTER INDEX <index name> REBUILD (ONLINE PARALLEL x NOLOGGING)
           Advantages

                    * Fast storage in a different tablespace possible

                    * Creates a new index tree

                    * Gives the option to change storage parameters without deleting the index

                    * as of Oracle 8i (8.1), you can avoid a lock on the table by specifying the ONLINE option. In this case, Oracle waits until the resource has been released, and then starts the rebuild. The "resource busy" error no longer occurs.

           Disadvantages

                    * Requires additional disk space and/or main memory

3) ALTER INDEX <index name> COALESCE [as of Oracle 8i (8.1) only]
           Advantages

                    * Does not require any additional disk space

                    * The fastest solution

                    * Releases index leaf blocks for further usage

                    * Coalesces leaf blocks in the same branch of the index tree

                    * The table is NOT locked during the entire process

           Disadvantages

                    * Cannot move the index to another tablespace

                    * Cannot solve all problems of poor storage quality (see items under 'Advantages')

Summary
           A possible concept for a defragmentation strategy could read as follows:

                    Monday-Saturday: Variant c) e.g.: alter index sapr3."TRFCQOUT~1" coalesce;

                    Sunday: Variant b) e.g.: alter index sapr3."TRFCQOUT~1" rebuild online parallel 4 nologging;

Caution:
           With variant c), ORA-00600 terminations may occur. Please refer to Note 369123.

           If you use the online option with variant b) in an Oracle Release < 8.1.7.1, this may lead to index corruption (Oracle bug: 1475310). Pleasealso refer to Note 682926.