Rebuilding SAP Indexes

When an indexed field in a table is changed repeatedly, the data is deleted from the index and re-inserted at a new block in the index. The new entries are always added to the right side of the index tree and therefor deletion is relatively to the left.

When the index elements are deleted and added repeatedly, the left side of the index tree tends to be sparse and the right side keeps growing. Depending on the size of index, there might be thousands of sparse blocks, which are being checked during execution.

Fragmented index results in increased usage of database space and more blocks being read into the buffer. This can be avoided by rebuilding the index.

One can measure index fragmentation using the report RSORATAD or using DB02 --> Detailed Analysis --> Enter Index --> Detailed Analysis --> Analyze Index --> Storage Quality.

If storage quality is less than 50% you may need to reorg the index. If you wish to run an analysis on all the indexes, run the report RSORAISQN.

Check SAP note 970538 for more details on the restriction with using this report. Do not run the report without reading the note.

You can also get an idea on the amount of fragmentation by comparing the size of the table and the index. If the size of the index is larger than that of the table, the index is heavily fragmented.

To rebuild index, you can use one the following methods:
1. brspace -f idrebuild -i <index_list>
2. Using DB02
DB02 --> Detailed Analysis (in the "Tables and Indexes" section) --> Object Name: <index> --> Detailed Analysis --> Alter Index --> Coalesce / Rebuild
3. Using RSORAISQ report
   Call SAP transaction SE38 and execute the report RSORAISQ.
   Input the tablespace name and the index name in Object Selection section.
   Check rebuild option in Function Code section.
   A report will be generated with details on the index.
   Click on the Index Rebuild button.
   You will be prompted to feed the name of the index and tablespace again, enter the details and execute.
4. Rebuild indexes offline using the report RSANAORA
5. Mass rebuild of indexes using the report RSORAISQN

If you find this useful, please consider sharing the post with your friends and colleagues on facebook,twitter, google+, linkedin (or any other medium you like!)

Would you like to read more on indexes? The linked post discusses guidelines to creating an Oracle index based on table size.