SOLVED: histogram oddity: Height-balanced histograms where NDV < num_buckets

Oracle Support has finally acknowledged that this is an unpublished internal bug fixed in v11. As no other customers have reported the issue, much less a performance issue, there are no plans for a backport.

The good news is that even though the buckets are mismarked (HEIGHT BALANCED), the density of the column matches that of a FREQUENCY histogram. At least for our situation it does.

On 8/11/06, Charles Schultz <sacrophyte_at_gmail.com> wrote:
>
> Oracle 10.2.0.2, Solaris 8
>
> Wondering if anyone could explain this. We have 105 SYS-owned tables where
> num_distinct < num_buckets according to dba_tab_col_statistics (see
> following query). Even weirder, some columns have 217 buckets for only 1
> distinct value (verified in dba_tab_histograms). What kind of sense does
> that make? Similarly, we have one application partition that exhibits the
> same symptom.
>
> select distinct table_name, column_name, num_distinct, num_buckets
> from dba_tab_col_statistics where histogram = 'HEIGHT BALANCED' and
> num_distinct < num_buckets
> order by num_distinct,num_buckets
> /
>
> -mystified
>
> --
> Charles Schultz

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l