Re: determining usage of histograms

"Ben" <balvey_at_comcast.net> wrote in message news:1142008569.460260.87800_at_j52g2000cwj.googlegroups.com...
>I am running a 9.2.0.5 db with the compatibility ini param set to 8.1.0
> and the optimizer_features_enable is set to 9.2.0.
>
> After reading an article on asktom I found that you can count the
> records for a column in user_histograms to determine if histograms have
> been created for that column.
> I have a schema that all the tables and all columns have two records in
> user_histograms. I believe this means that there is just a hi/lo
> endpoint and no histograms for any columns in the tables. These stats
> were generated using dbms_utility.analyze_schema(compute). My question
> is what method_opt for dbms_stats.gather_schema_stats is equivalent?
> All of the tables I have looked at have had two records for every
> column. So I wouldn't think that 'for all columns' would be the same,
> what about 'for all columns size 2' ??
>

You need 'for all columns size 1'.

This gives a 'histogram' with one bucket, i.e. just the low and high values. If you tried 'for all columns size 2' you would get two buckets - which would need three
values in user_tab_histograms.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html