Monday, May 28, 2012

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!

No comments: