List Analyzed Tables With Un-Analyzed Indexes

Description: Sometimes indexes are re-build for performance and maintenance reasons but the associated table/index is not re-ANALYZED. This can cause severe performance problems. This script will catch out tables with indexes that is not analyzed.

-- select distinct 'analyze table '||i.table_name||
--                ' estimate statistics sample 25 percent;'
SELECT 'Index '||i.index_name||' not analyzed but table '||
       i.table_name||' is.'
  FROM user_tables t, user_indexes i
 WHERE t.table_name    =      i.table_name
   AND t.num_rows      IS NOT NULL
   AND i.distinct_keys IS     NULL
/

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.