Here’s the output I get from querying * dba_tab_histograms* for a column that has been given a frequency histogram by a call to

*.*

**dbms_stats.gather_table_stats()**(more…)

Here’s the output I get from querying * dba_tab_histograms* for a column that has been given a frequency histogram by a call to

(more…)

I find it convenient occasionally to “translate” a **frequency histogram** into a report of the underlying data (sometimes to see how closely the histogram matches the real data). To demonstrate the type of query I use I’ve created a data set with a small number of distinct values and generated a frequency histogram on the data set. This is what the data and histogram look like:

(more…)

I got an email a few days ago asking me how to create a *“height-balanced”* histogram using the * dbms_stats.set_column_stats()* procedure.

I posted * an example of creating a frequency histogram* a few months ago – and it doesn’t take much to turn this into an example for the height-balanced type; the key change apppears in three critical lines.

In a recent article on changes to the calculation of ** cardinality with frequency histograms** I made a comment about writing programs to create fake but realistic and complete frequency histograms to work around the problems of dramatic changes in execution plan that can occur if you let Oracle create the histograms by gathering stats.

As a follow-up to that comment, here’s a little script to demonstrate the method (for a numeric column), and a few points to remember when using this method.

When you upgrade from 10.2.0.3 to 10.2.0.4 or 11g, watch out for SQL that depends on the existence of ** frequency histograms**.

In 10.2.0.3 (and earlier versions of Oracle) if you have a ** frequency histogram** on a column, and then use a predicate of the form:

where colX = {value not found in histogram}

then the optimizer would calculate a cardinality of one for this predicate (having recorded a * density* of 1/(2 *

Here’s an interesting point that’s just been raised on the Oracle-L list-server:

Metalink bug: 5483301.8

If a column has a frequency histogram and a query uses an equality predicate over the column looking for a value that does not exist in the histogram then the CBO was estimating a cardinality of 1. This could favor Nested Loops too much.

The fix introduces a notable change in behavior: instead of estimating a cardinality of 1 then with this fix CBO estimates a cardinality of (0.5 * the lowest cardinality found in the histogram). This can result in plan changes.