Which index you need to rebuild?

– This script shows the weak indexes I mean fragmentated index in your oracle database.
– Shows those indexes who are having more than 500 leaves and storage quality is less than 50%
– This accuracy of this script depended on the DB Statistics are fresh and almost accurate

Column index_name format 99999999999999999999
Column rowlen format 99999999999999999999
Column leaves format 99999999999999999999
Column net_mb format 999999999999
Column gross_mb format 999999999999

Spool index_stat.txt
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 > 500 — Change this as per your requirement.
GROUP BY I.NUM_ROWS, I.LEAF_BLOCKS, I.INDEX_NAME, I.INI_TRANS, I.PCT_FREE
)
WHERE
QUALITY <= 50 — You can change this as per your requirement
ORDER BY QUALITY;
spool off.