Databases Reference
In-Depth Information
A histogram is an object in which values from a column are put in a limited number of
buckets, 75 by default, with a distribution based on ranges—the column values within the
same range are put in the same bucket.
Using the data in histograms, the optimizer can estimate the data distribution better in a
column, so it can choose a better execution plan, due to a greater knowledge of the data.
In step 4, we create a histogram for
FIELD1
, using a size of 10 as the number of buckets
used for the histogram. In our example, this is equivalent to the different values stored in
FIELD1
, so in every bucket there is only one distinct value, as shown by the query in step 5.
In the following screenshot, we can see the results of this query:
The
ENDPOINT_VALUE
shows us the value stored for each bucket, while the meaning of
ENDPOINT_NUMBER
changes due to the kind of histogram. In this example, we have the
number of buckets equal to the number of distinct values, so
ENDPOINT_NUMBER
represents
the cumulative frequency (in the first bucket, the value 0 appears 538 times, in the second
1094-538 = 556 times, and so on).
In step 6, we create a histogram on
FIELD2
, and the query on this histogram gives the
following output:
