Rebuild of Indexes or Reorganization of Tables

[Former Member]

Hi Everyone,

I have a doubt regarding the rebuilding of indexes or reorganization of tables.

I want to analyze and learn, when we should rebuild a index or reorg a table ?

How we can know that for a particular table or index that there are many dead leaf nodes and so we have to rebuild them ?

I am in the learning stage of DBA. I will be every thankful if anyone can help me to get a clear understanding of my doubts.

Regards

Sudhir Sadhu

[former_member206552 · Accepted solution]

Hi Sudhir

Here is a query to start off with that will give you the top 20 tables with the most fragmented space where you could gain the most space

SELECT * FROM
(SELECT
    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
    NUM_ROWS,
    AVG_ROW_LEN ROWLEN,
    BLOCKS,
    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
    ROUND(BLOCKS * (8000 - 23 * INI_TRANS) *
      (1 - PCT_FREE / 100) / 1000000, 0) GROSS_MB,
    ROUND((BLOCKS * (8000 - 23 * INI_TRANS) * (1 - PCT_FREE / 100) -
      (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) "WASTED_MB"
  FROM DBA_TABLES
  WHERE
    NUM_ROWS IS NOT NULL AND
    OWNER LIKE 'SAP%' AND
    PARTITIONED = 'NO' AND
    (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
  ORDER BY 7 DESC)
WHERE ROWNUM <=20;

Once the list has been obtained you can reorg the table using BRTOOLS 

http://help.sap.com/saphelp_nw04/helpdata/en/1b/4e8f0d38a8f4419436d608a36b6581/content.htm

You can also do some research on oracle shrink command

http://www.oracle.com/technetwork/issue-archive/2005/05-may/o35tuning-096075.html

Hope this helps

Best Regards
Marius

[Former Member · Accepted solution]

Hi Marius,

Thank you for your quick response.

Yes, I had run the command and I got the top 20 tables and the waste_mb highest value is 4092 for the table RFBLG.

Through this way, I will be getting the list of tables highly fragmented.

Now, as you said to defragment those tables there are 3 methods.

1.) Offline reorg

2.) Online reorg

3.) Shrink method.

We have to follow any of the methods according to our needs.

Is it correct ?

[former_member206552 · Accepted solution]

1.) offline reorg, you can do an export import (this is an old method but still used for very big tables)

2.) online reorg , you can use brtools to do this

brspace -u / -c force -p initSID.sap -s 20 -l E -f tbreorg -o sapsr3 -e 2 -t "RFBLG"

keep in mind this is done during low peak period as it causes a lock on the table

3.) the shrink methos i only use when i get no results from reorg

foot note: remember to update the statistics of the table when you are done.

[former_member206552 · Accepted solution]

just to add, here you can find all the required option for brspace

http://help.sap.com/saphelp_nw70/helpdata/en/cb/376c1d44ec274daa70e5e07cfa09c6/content.htm

[Former Member · Accepted solution]

Hi Marius,

Finally, I got the clarity of reorg of tables. Practically, I have to check it.

Coming to rebuilding of indexes,  How can we get the list of top fragmented indexes ?

Is it possible to get the list of top fragmented indexes ?

Regards

Sudhir Sadhu

[former_member206552 · Accepted solution]

Hi Sudhir

Please see below

SELECT

  INDEX_NAME,

  NUM_ROWS,

  ROWLEN,

  LEAVES,

  NET_MB,

  GROSS_MB,

  QUALITY

FROM

( SELECT

    SUBSTR(I.INDEX_NAME, 1, 20) INDEX_NAME,

    I.NUM_ROWS NUM_ROWS,

    SUM(TC.AVG_COL_LEN + 1) + 7 ROWLEN,

    I.LEAF_BLOCKS LEAVES,

    ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /

      1000000, 0) NET_MB,

    ROUND(I.LEAF_BLOCKS * (8079 - 23 * NVL(I.INI_TRANS, 2)) *

      (1 - NVL(I.PCT_FREE, 10) / 100) / 1000000, 0) GROSS_MB,

    ROUND((SUM(TC.AVG_COL_LEN + 1) + 7) * I.NUM_ROWS /

      (I.LEAF_BLOCKS * (8079 - 23 * NVL(I.INI_TRANS, 2)) *

      (1 - NVL(I.PCT_FREE, 10) / 100)) * 100, 0) QUALITY

  FROM

    DBA_INDEXES I,

    DBA_IND_COLUMNS IC,

    DBA_TAB_COLUMNS TC

  WHERE

    I.INDEX_NAME = IC.INDEX_NAME AND

    I.OWNER = IC.INDEX_OWNER AND

    TC.TABLE_NAME = IC.TABLE_NAME AND

    TC.OWNER = IC.INDEX_OWNER AND

    TC.COLUMN_NAME = IC.COLUMN_NAME AND

    I.INDEX_TYPE = 'NORMAL' AND

    I.LEAF_BLOCKS > 1000

  GROUP BY

    I.NUM_ROWS,

    I.LEAF_BLOCKS,

    I.INDEX_NAME,

    I.INI_TRANS,

    I.PCT_FREE

)

WHERE

  QUALITY <= 30

ORDER BY

  QUALITY;

Best Regards

Marius

[Former Member · Accepted solution]

Hi Marius,

You have clarified my doubts.

I have to learn a lot and lot.......

It is a vast ocean. Still I am in the beginning stage.

Anyways, Thank you very much for your valuable information.

Regards

Sudhir Sadhu

[former_member206552 · Accepted solution]

Hi Sudhir,

this is how we all learn, you share knowledge and learn everytime

glad i could help

cheers

Marius

[ashish_vikas]

I give you few notes to start with. 771929 should answer your question on index.

771929 - FAQ: Index fragmentation

Now, in sap you can do indexes built by different means - running report RSORAISQN, RSORAISQN or with BRTOOLS and also directly with sql command alter index ...

712098  - RSORAISQ: Index Storage Quality Management

444287 - Checking the index storage quality

On Table reorganization:

http://help.sap.com/saphelp_nw70/helpdata/en/75/95536a63b44b4a9e7f278c7d9c2b67/content.htm

https://help.sap.com/saphelp_nw70/helpdata/en/1b/4e8f0d38a8f4419436d608a36b6581/content.htm

http://help.sap.com/saphelp_nw70/helpdata/en/32/0d0c888839164ba4245b3ff7969c59/content.htm

best regards

ashish