Fragmented Indexes

Since (b-tree) indexes are stored in a sorted order, updating an indexed column usually involves deleting it from one position in the index and inserting into another place. If this happens often enough, the index can become riddled with holes. Range scans on the index will read more blocks than they have to because each block is under-utilised.

This can also happen when many rows are deleted; a common occurrence in a fast-refresh Materialized View.

The only solution is to rebuild the index when it becomes fragmented.

ALTER INDEX my_index REBUILD [PARTITION part_name]

Run the following SQL to list fragmented indexes. If you have fast-refresh materialized views, you should run this SQL regularly.

---

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

---

Note that this query does not take into account block headers (a small space overhead in each block) or the prefix size of compressed and prefixed partitioned indexes. Also, bitmap indexes, bitmap join indexes, function-based indexes, and domain indexes are excluded. Space usage for these index types will differ from the standard calculated here.