Rebuild of Indexes or Reorganization of Tables
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.
Marius Burger replied
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
SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
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"
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
You can also do some research on oracle shrink command
Hope this helps