Re: Trying to understand histograms

<BigBoote66_at_hotmail.com> wrote in message news:1116537874.285532.297390_at_z14g2000cwz.googlegroups.com...
> Thanks for your advice. The question I have about setting up our own
> histogram is this: Since the histogram implicitly orders the values
> according to their sorting order (presumably defined by your
> localization settings), how does one "rig" the histogram to only
> contain the values one cares about? For example, if your column
> consists of the numeric values 1 - 4000, and the 400 popular ones are
> scattered throughout that range, you can't really used the
> ENDPOINT_VALUE columns to describe this, because all the other values
> will be adjacent.
>
> If I were able to design a histogram system that used a fixed number of
> buckets, I would first generate the counts, then order the histogram
> columns by height, from greatest to least. Then my buckets would
> report the counts for each of the high values, out to the last few
> buckets (just like a value-based histogram does). All remaining values
> would be lumped into the last bucket and assumed to have a uniform
> distribution among the remaining rows (which would presumably be not so
> much).
>
> However, to implement such a scheme, I'd need a level of indirection -
> there would need to be a lookup table that would translate the actual
> value from the column into it's corresponding bucket location. As far
> as I know, Oracle's histogram feature doesn't allow this.
>

Possibly the best you can do is to
identify the 254 most popular values,
and record their actual occurrences
in a frequency histogram, then leave
all other values to have their cardinality set implicitly by defining the column
density.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005