分析表与索引

set head off

set pagesize 0

set feedback off

select 'analyze table '||owner||'.'||table_name||' compute statistics for table for all indexes for all indexed columns;' from all_all_tables where owner in ('CP2');

如果有分区表， analyze方法更不适用。

 

exec dbms_stats.gather_table_stats(user,'KB01',cascade=>true);
select 'exec dbms_stats.gather_table_stats('''||owner||''','''||table_name||''',cascade=>true)'||';' from sys.dba_tables t where owner not in ('SYS','SYSTEM','PUBLIC','OUTLN');

select 'exec dbms_stats.gather_table_stats('''||table_owner||''','''||table_name||''',partname=>'''||partition_name||''',cascade=>true)'||';'
from dba_tab_partitions t where table_owner not in ('SYS','SYSTEM','PUBLIC','OUTLN');