Re: dbms_stats set_table_stats, set_index_stats and set_columns_stats

I have been communicating with Dias directly and he (why do I assume he's a he?) provided me with the 10053 traces and more details on the dbms_stats calls used. From that I could create a testcase and reproduce the problem. It turns out it has nothing to do with the fact that the tables are partitioned. I could get the same behaviour using just one non-partitioned table. The cause for the problem lies with the omission of a reference to a filled-in srec structure in the set_column_stats call to provide the min and max values for the column, if not an entire histogram array. The Oracle 8i optimizer obviously didn't mind, but the Oracle 9i cbo does.

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in news:bpe6d7$11r$1 $830fa7a5_at_news.demon.co.uk:

>
> I think there was a change of strategy between 8 and 9
> on multi-partition queries.
>
> In Oracle 8, Oracle would examine the statistics of
> all relevant partitions. On Oracle 9, it uses the stats
> of the base table.
>
> Are you setting the base table stats, and are you
> running multi-partition queries, or single partition
> queries.
>

-- 
What lies behind us and what lies before us are small matters when compared 
to what lies within us.

Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP