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:- 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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
