Re: Trying to understand histograms

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. Received on Thu May 19 2005 - 16:24:34 CDT