oracle fragmentation report

index_frag_oracle.sql

SELECT  table_name
,       index_name
,       NULL as partition_name
,       num_rows
,       leaf_blocks
,       rows_per_leaf_block
,       avg_key_len
,       rows_per_leaf_block * avg_key_len AS avg_bytes_per_leaf_block
,       db_block_size
,       TRUNC(100 * rows_per_leaf_block * avg_key_len / db_block_size, 2) AS utilisation_pct
FROM   (
         SELECT  i.table_name
         ,       i.index_name
         ,       i.num_rows
         ,       i.leaf_blocks
         ,       i.num_rows / i.leaf_blocks AS rows_per_leaf_block
         ,       kl.avg_key_len
         ,       s.bytes / s.blocks AS db_block_size
         FROM    user_indexes i
         JOIN    user_segments s
         ON      s.segment_name = i.index_name
         AND     s.segment_type = 'INDEX'
         JOIN (
                 SELECT ic.index_name, SUM(c.avg_col_len) AS avg_key_len
                 FROM   user_indexes ii
                 JOIN   user_ind_columns ic
                 ON     ic.index_name = ii.index_name
                 AND    column_position > NVL(ii.prefix_length,0)
                 JOIN   user_tab_columns c
                 ON     ic.table_name = c.table_name
                 AND    ic.column_name = c.column_name
                 GROUP BY ic.index_name
         ) kl
         ON      kl.index_name = i.index_name
         WHERE   i.num_rows >= 50
         AND     i.leaf_blocks > 0
         AND     i.index_type = 'NORMAL'
)
UNION ALL
SELECT  table_name
,       index_name
,       partition_name
,       num_rows
,       leaf_blocks
,       rows_per_leaf_block
,       avg_key_len
,       rows_per_leaf_block * avg_key_len AS avg_bytes_per_leaf_block
,       db_block_size
,       TRUNC(100 * rows_per_leaf_block * avg_key_len / db_block_size, 2) AS utilisation_pct
FROM   (
         SELECT  i.table_name
         ,       i.index_name
         ,       p.partition_name
         ,       p.num_rows
         ,       p.leaf_blocks
         ,       p.num_rows / p.leaf_blocks AS rows_per_leaf_block
         ,       kl.avg_key_len
         ,       s.bytes / s.blocks AS db_block_size
         FROM    user_indexes i
         JOIN    user_ind_partitions p
         ON      p.index_name = i.index_name
         JOIN    user_segments s
         ON      s.segment_name = p.index_name
         AND     s.partition_name = p.partition_name
         AND     s.segment_type = 'INDEX PARTITION'
         JOIN (
                 SELECT ic.index_name, SUM(c.avg_col_len) AS avg_key_len
                 FROM   user_indexes ii
                 JOIN   user_ind_columns ic
                 ON     ic.index_name = ii.index_name
                 AND    column_position > NVL(ii.prefix_length,0)
                 JOIN   user_tab_columns c
                 ON     ic.table_name = c.table_name
                 AND    ic.column_name = c.column_name
                 GROUP BY ic.index_name
         ) kl
         ON      kl.index_name = i.index_name
         WHERE   p.num_rows >= 5000
         AND     p.leaf_blocks > 0
         AND     i.index_type = 'NORMAL'
)
ORDER BY utilisation_pct DESC