Rebuilding Index online using DB02

[former_member185954]

I read a nice blog last week about How to take care of index corruptions in primary key indexes., here lars.breddemann/blog explains how to rebuild primary indexes using SQLPLUS or similar tool at the Operating System level.

However there is a way to rebuild an index online from the SAP Application Server using transaction DB02.
I'll explain this using an example of unbalanced index problem.

Unbalanced Index problem

Many times we find the Update Statistics job in DB13 showing warnings  about 'unbalanced indexes'.

The warnings appear in this format:

As the message itself suggests, we need to rebuild the affected index in order to get rid of that warning message. The following steps describe how it is done from transaction DB02.

The Solution

1. Prerequisites :
   
   We would need a SAP login with access to run Database maintenance transaction DB02, the BASIS userid or a similar userid generally has the appropriate authorizations.
   
   Also, to perform this activity choose a low system activity period when most users are not working on the system.
   
   
2. Start DB02:
   
   Start transaction DB02 and click on the Current Sizes button as shown in the below screen:
   
   
   
   
3. Find and Select The Index:
   
   On clicking the above mentioned button, we should be able to view a screen with heading Memory Management: Tablespaces, click on the Tab/Ind button as shown below:
   
   
   
   We should get the following popup box, in which we should provide the index name which we wish to rebuild.
   
   
   
   On clicking the 'green' tick, we should be able to see the index(*s) pertaining to the selected pattern, as shown below:
   
   
   
   Select the index that you wish to rebuild by clicking on it and you should be able to see the following screen:
   
   
   
   Select the Index and click on the Detailed Analysis Button.
   
   * in my case I am working on a MCOD system, hence I see 3 indexes belonging to 3 different components, however in your case it should be only a single line.
   
   
4. REBUILD INDEX:
   
   On selecting the index and clicking the Detailed Analysis button, you should be able to see the following screen:
   
   
   
   Check the size of the index, the size (for obvious reasons) would dictate how long the rebuild should take. Over here I see the index is pretty small in size.
   
   So now we simply use the menu item Alter Index -> Rebuild Index as show below:
   
   
   
   After a while we can see the following results if the build is successful:
   
   
   
   
5. Limitations:
   
   The above described method will not work for Partitioned Indexes in BW/BI Systems.
   
   Also, rebuilding larger indexes might take longer time, so we need to be careful about which indexes we choose to rebuild, since due to longer rebuild time we might hit the max wp_runtime parameter.