Re: the IN club: Oracle "unpublished" information

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: "Wolfgang Breitling" <breitliw@xxxxxxxxxxxxx>
  • Date: Wed, 14 Mar 2007 10:30:17 -0500

My apologies for missing that. One of these days I will summarize your
thoughts correctly. In the meantime, I appreciate your gracious attitude and
patience with the likes of me.

On 3/14/07, Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:

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.


Wolfgang Breitling
Centrex Consulting Corporation

Charles Schultz

Other related posts: