Re: Histograms - SIZE clause & num

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Mon, 02 Aug 2004 07:05:00 -0600
Message-Id: <6.1.0.6.2.20040802064744.02f62108@pop.centrexcc.com>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Mon Aug 02 2004 - 08:01:11 CDT

Re: Histograms - SIZE clause & num_buckets anomaly