Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Rebuild of Indexes or Reorganization of Tables

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

Tags:
Former Member
replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question