Re: Histograms - SIZE clause & num_buckets anomaly

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 02 Aug 2004 07:05:00 -0600

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:- 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';
>------------ -----------
>           11           4
>How come there are only four buckets created even though I specified size as
>11? Could it be histogram compression?


Wolfgang Breitling
Centrex Consulting Corporation 

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
Archives are at
FAQ is at

Other related posts: