At 08:00 AM 3/14/2007, Charles Schultz wrote:
>For instance, I was recently trading emails with Wolfgang Breitling,
>and he was patiently clarifying his paper "Joins, Skews and
>Histograms" (with kudos to Alberto Dell'Era). For an extremely
>simple case (join a 20 row table to a 40 row table where half of the
>rows from the first are repeated x4 in the latter), Wolfgang (and
>Alberto) demonstrate that Oracle uses a highly complex algorithm to
>calculate cardinality only when frequency histograms are used (as
>opposed to SIZE AUTO or SIZE SKEW, or no stats). The kicker is that
>the estimated cardinality is really bad (45% error).
A small correction. The optimizer uses that complex algorithm every
time there are histograms on the join columns, not just when there
are frequency histograms. That is one of the main points. The
algorithm doesn't distinguish between frequency and height-balanced
histograms which for the referenced particular case results in the
gross miscalculation.
Regards
Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
