Re: dbms_stat.delete_schema_stats and gather_schema_stats ignore nested tables

On 23 May 2005 06:50:17 -0700, flavio_at_tin.it wrote:

>Hi,
>I've made some attempts to find any information regarding this problem
>with no success.
>
>Oracle EE 9.2.0.1.0, Windows 2000:
>
>dbms_stat.gather_schema_stats doesn't work for nested tables and their
>associated indexes, no matter what the CASCADE option is set to. Also
>specifying 'FOR ALL COLUMNS' for method_opt parameter doesn't help.
>Also, dbms_stat.delete_schema_stats doesn't clear statistics collected
>on nested table indexes.
>
>On the other hand, it is possible to collect statistics on nested
>tables and their indexes, by directly calling
>dbms_stats.gather_table_stats and passing the nested table name with
>cascade => true. Likewise, it possible to delete the statistics by
>issuing dbms_stat.delete_table_stats against the nested table name.
>
>I wonder why this doesn't happen automatically with
>dbms_stat.gather_schema_stats though.
>
>Bye,
>Flavio

As you are running the 9.2 base release, Oracle will tell you to upgrade to 9.2.0.6. I didn't check it, but this very much looks like a typical issue for a base release.

--
Sybrand Bakker, Senior Oracle DBA