Databases Reference
In-Depth Information
In this case, the number of buckets (10) is more than the number of distinct values (2) in
the
FIELD2
column, and
ENDPOINT_NUMBER
represents the cumulative frequency as
shown earlier.
There's more...
In this recipe, we have seen how to create histograms to represent our data to help the
optimizer choose the right execution plan for our queries.
We might think that having histograms on all the columns of all the tables is a better solution,
to help the optimizer in choosing the best execution plan, but that's not the case.
Histograms are useful only on indexed columns containing skewed values, because they
help the optimizer to choose whether to use the index or not to access values. Obviously, if
the frequency for a specific value is very high, using the index won't be the best choice. If a
column with these characteristics is also used often in the
WHERE
clause, it's a very good
candidate for a histogram.
Don't use histograms in situations where:
F
The column is not used in the
WHERE
clauses of queries
F
The data in the column is uniformly distributed (like
FIELD1
, in our example)
F
Bind variables are used when comparing against the column (we will see more
on this in the
Introducing
Adaptive
Cursor
Sharing
for
bind
variable
peeking
recipe,
later in this chapter)
Another issue with using histograms is that they need to be updated manually to reflect
changes in column data, using the statements presented in this recipe.
