Column histograms should be created only when you have highly-skewed values in a column. Histograms tell the CBO when a column's values aren't distributed evenly, and the CBO will then use the literal value in the query's WHERE clause and compare it to the histogram statistics buckets.

SQL Tuning with column histograms

The distribution of values within an index will often affect the cost-based optimizer (CBO) decision whether to use an index or perform a full-table scan to satisfy a query. This can happen whenever the column referenced within a SQL query WHERE clause has a non-uniform distribution of values, making a full-table scan faster than index access.

Column histograms should be created only when you have highly-skewed values in a column. This rarely occurs, and a common mistake that a DBA can make is the unnecessary collection of histograms in the statistics. Histograms tell the CBO when a column's values aren't distributed evenly, and the CBO will then use the literal value in the query's WHERE clause and compare it to the histogram statistics buckets.

Using histograms to improve table join order

Histograms can help the cost-based optimizer estimate the number of rows returned from a table join (called "cardinality") and histograms can help. For example, assume that we have a five-way table join whose result set will be only 10 rows. Oracle will want to join the tables together in such a way as to make the result set cardinality of the first join as small as possible.

By carrying less baggage in the intermediate result sets, the query will run faster. To minimize intermediate results, the optimizer attempts to estimate the cardinality of each result set during the parse phase of SQL execution. Having histograms on skewed column will greatly aid the optimizer in making a proper decision. (Remember, you can create a histogram even if the column does not have an index and does not participate as a join key.)

Oracle 10g has also introduced dynamic sampling to improve the CBO's estimates of inter-table row join results. Even with the best schema statistics, it can be impossible to predict a priori the optimal table-join order (the one that has the smallest intermediate baggage). Reducing the size of the intermediate row-sets can greatly improve the speed of the query.