Re: Histograms - SIZE clause & num_buckets anomaly

I have noticed that too that Oracle 9 collects a height balanced histogram 
when you gather statistics with size=<num_distinct> instead of a frequency 
histogram as you'd expect (and as Oracle 8 did). At least sometimes. I 
haven't done enough testing to have recognized a pattern or rule. In my 
case, for example, I needed to crank up size to 38 or 39 (forgot exactly 
which one) (~ 50% more) in order to get the frequency histogram.
Btw, if you use "analyze ... for columns owner size 11" you'll get your 
frequency histogram. Analyze hasn't changed from 8 to 9, but 
gather_table_stats has.
Also, in order to delete statistics I still use analyze because 
delete_table_stats doesn't delete index stats. It doesn't matter in your 
case, I just thought I mention it.

At 05:32 AM 8/2/2004, you wrote:
>Hi all,
>
>Version:- 9.2.0.1 Enterprise Edition
>OS: Win2K
>
>
>** Check the number of buckets.
>SQL> select num_distinct, num_buckets
>   2  from user_tab_col_statistics
>   3  where table_name = 'T4' and column_name = 'OWNER';
>
>NUM_DISTINCT NUM_BUCKETS
>------------ -----------
>           11           4
>
>How come there are only four buckets created even though I specified size as
>11? Could it be histogram compression?

Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: