Andre Spadini
André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Rebuild Indices

Oracle: Rebuild Indices

Andre Spadini's photo
Andre Spadini

Published on Aug 22, 2021

1 min read

Subscribe to my newsletter and never miss my upcoming articles

Analisando todos os indices do banco de dados que precisam de rebuild.

sql> 
spool rebuild_indices.sql
SELECT 'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' REBUILD;'
        FROM
        ( SELECT I.OWNER, SUBSTR(I.INDEX_NAME, 1, 20) INDEX_NAME,
                         TABLESPACE_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 > 100
          GROUP BY I.NUM_ROWS,
                                TABLESPACE_NAME,
                        I.LEAF_BLOCKS,
                        I.INDEX_NAME,
                        I.INI_TRANS,
                        I.PCT_FREE,
                        I.OWNER)
        WHERE
        QUALITY <= 70
        ORDER BY
        QUALITY;

sql> @rebuild_indices.sql

Abs,

 
Share this