Snippet Name: 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.
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009
|
-- 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
/
|
