Maintaining Indexes - Expert Indexing in Oracle Database 11g - page 167

When collecting table statistics:
SQL> execute dbms_stats.gather_table_stats(ownname=>'HR', tabname=>'EMPLOYEES',
cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL>
Specifying cascade=>true will result in the database always collecting statistics for all indexes on a
table. If you don't specify cascade=>true , Oracle may or may not collect statistics for the indexes. By
default, the constant DBMS_STATS.AUTO_CASCADE determines whether the database must collect index
statistics. Of course, you can change the default value of the CASCADE parameter at the table, schema, or
database level. Here's an example of how to do so at the database level:
SQL> exec dbms_stats.set_database_prefs('CASCADE','TRUE');
PL/SQL procedure successfully completed.
SQL>
Setting the CASCADE parameter to TRUE is the same as executing the GATHER_INDEX_STATS procedure to
gather index statistics. Alternatively, you can just run the GATHER_INDEX_STATS procedure to gather index
statistics, as shown here:
SQL> execute dbms_stats.gather_index_stats ('HR','EMP_EMP_ID_PK');
PL/SQL procedure successfully completed.
SQL>
The following is a simple script that lets you generate all the necessary commands to generate
statistics collection commands for all indexes in a schema:
set serveroutput on
begin
for ind in
(select object_name
from user_objects
where object_type='INDEX')
loop
dbms_output.put_line(
'Gathering Index Statistics for'||ind.object_name||'.....');
dbms_stats.gather_index_stats(user, ind.object_name
, estimate_percent=>100);
dbms_output.put_line('Gathering Index Statistics for '
||ind.object_name||' is Complete!');
end loop;
end;
/