DBMS_STATS.SET_PARAM

The example below worked as shown in Oracle 10. First create a test table with an index:

 

SQL> create table andrews_table (col1 number)

  2  /

 

Table created.

 

SQL> create index andrews_index

  2  on andrews_table(col1)

  3  /

 

Index created.

SQL>

Next run dbms_stats.set_param and set cascade to false:

 

SQL> exec dbms_stats.set_param('cascade','false');

 

PL/SQL procedure successfully completed.

SQL>

Then, if you analyze the table using gather_table_stats:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'andrews_table');

 

PL/SQL procedure successfully completed.

SQL>

The table is analyzed:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

SQL>

... but the index isn't:

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

NULL

SQL>

However, if you run dbms_stats.set_param and set cascade to true:

SQL> exec dbms_stats.set_param('cascade','true');

 

PL/SQL procedure successfully completed.

SQL>

When you analyze the table, the index is analyzed too:

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname=>'oracle', tabname=>'andrews_table');

 

PL/SQL procedure successfully completed.

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_tables

  3  where table_name = 'ANDREWS_TABLE'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL> select nvl(to_char(last_analyzed),'NULL') analyzed

  2  from user_indexes

  3  where index_name = 'ANDREWS_INDEX'

  4  /

 

ANALYZED

---------

15-MAY-12

 

SQL>

 

In Oracle 11, dbms_stats.set_param still runs but it has no effect. You can run

dbms_stats.set_param('cascade','false'); but the index might still be analyzed!