It is important to periodically examine your indexes to determine if they have become skewed and might need to be rebuilt. When an index is skewed, parts of an index are accessed more frequently than others. As a result, disk contention may occur, creating a bottleneck in performance.
The key column to decide index skewed is blevel
. You must estimate statistics
for the index or analyze index validate structure
If the BLEVEL were to be more than 4, it is recommended to rebuild the index.
SELECT OWNER, INDEX_NAME, TABLE_NAME, LAST_ANALYZED, BLEVEL
WHERE OWNER NOT IN ('SYS', 'SYSTEM')
AND BLEVEL >= 4
ORDER BY BLEVEL DESC;