Histogram change

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 * num_rows) in the data dictionary (see comments 21 and 22 of this note on dbms_xplan).

In 10.2.0.4, the cardinality will change in one of two possible ways. If the value you supply does not appear in the histogram, but is inside the low/high range of the histogram then the cardinality will be half the cardinality of the least frequently occurring value that is in the histogram. (A change first brought to my attention by Alberto Dell’Era).

If the value you supply is outside the low/high range of the histogram Oracle starts with the “half the least popular” value, then applies the normal (for 10g) linear decay estimate so that the cardinality drops the further outside the known range your requested value falls.

For example, consider a column with these five values and the following frequencies:

        99             10M rows
        88              1M rows
        81            400K rows
        91             20  rows
        75             20  rows

Imagine you gathered table stats using the automatic sample size and Oracle created a frequency histogram that failed to capture any information about the rather rare values 75 and 91.

In 10.2.0.3  a query for 75 or 91 would result in the optimizer saying “only one row” and it would probably produce a reasonable execution plan.

In 10.2.0.4 the optimizer would say 200K rows (half of 400K) for the value 91 which is “missing” but inside the (captured) range 81 to 99 – and this could easily lead to a bad execution plan. 

For the value 75  the cardinality would be about 140,000 (roughly two-thirds of 200,000) because it is missing – hence the starting point of 200,000 which then has to be scaled down because the value is outside the observed range  by a gap that is one-third of the size of that range. [This is an approximation, I don't have a 10.2.0.4 on hand to check the exact arithmetic]. 

The upshot of this is that if you have frequency histograms with some very extreme behaviour (like this example) where ‘rare but important’ values could be missed when you use a sample to gather stats, you should be very keen to write a little program using dbms_stats.set_column_stats() to create a fake but realistic and complete frequency histogram rather than letting Oracle work one out for itself.

[Further reading on Histograms]