Skip to Content

I read a nice blog last week about How to take care of index corruptions in primary key indexes., here Lars Breddemann 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:

 BR0986W Index <SAP SCHEMA>.<INDEX NAME> is unbalanced - please rebuild the index

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:

    Start DB02

  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:

    Click on the Tab/Ind box

    We should get the following popup box, in which we should provide the index name which we wish to rebuild.

    Provide the name of the index 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 appropriate Index

    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

    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

    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:

    Rebuild Index

    After a while we can see the following results if the build is successful:

    Index successfully Rebuilt

  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.

To report this post you need to login first.

9 Comments

You must be Logged on to comment or reply to a post.

  1. Muhammad Farooq Ali
    Hi,

    This is a good solution but is not the best one. Actually using this method you need to select each and every index one by one and create them. So, only suitable if the system have only few indexes unbalanced.
    Anyways, nice blog.

    Farooq

    (0) 
    1. Siddhesh Ghag Post author
      Yes that is true, however it does help at times when you don’t have OS access, else you could simply write scripts (if on UNIX) or use SQL scripts.

      I am curious to know if this works for databases other than Oracle.

      (0) 
  2. Lars Breddemann
    Nice Blog and a quite handy supplement to my “hardcore” sqlplus way of handling such indexes.

    I’ve got some remarks to make here.
    1. the ONLINE option for index rebuilds is available in DB02 from 4.6/6.10 on. Earlier releases don’t use the ONLINE option – so one has to be carefull here.

    2. In many cases the BRCONNECT warning about “unbalanced” indexes is not correct and the index shouldn’t really be rebuild.
    Instead a COALESCE will most often do perfectly.

    3. When talking about regular index rebulds (you shouldn’t do that, you know…) one should take a look into these sap notes.
    #771929 FAQ: Index-Fragmentation
    #439783 BR986W Index …
    #444287 Verifikation of the idex Storage quality

    I would definitivly not recommend to rebuild your indexes regularly. Oracle is not THAT bad at maintaining them.

    KR Lars

    (0) 
    1. Siddhesh Ghag Post author
      Interesting.

      Thanks for your comments, rebuilding index is surely not to be done regularly.

      Coalesce, i hadn’t tried that option, let me try it and see what happens.

      Regards,
      Siddhesh

      (0) 
  3. hi !
    that’snice information. But I’got some different warnings than unbalanced indexes as below :

    BR0970W Database administration alert – level: ERROR, type: MISSING_INDEX, object: (table) SAPR3.WSAM_DB_PRICAT#$

    I can not rebuild it as you mentioned. Can you help me out how to sort out this.

    Thanks
    Anju

    (0) 
    1. Siddhesh Ghag Post author
      Hello Anju,

      Please check SAP Note : 483856 – Description of the alerts for Oracle database monitoring

      please post this question in DB & OS forums too.

      Regards,
      Siddhesh

      (0) 
  4. Sri M
    DB02 can handle the prcoessing of smaller indexes of size 1 Gig to 10 Gigs in production, but what if the index size is over 100 Gig do you still use DB02?

    I personally use BRSPACE with multiple threads rather than DB02.

    Regards
    SM

    (0) 

Leave a Reply