Re: Better cardinality estimate when dialing optimizer_features_enable back

  • From: Neil Kodner <nkodner@xxxxxxxxx>
  • To: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
  • Date: Tue, 24 Nov 2009 16:54:22 -0500

On Tue, Nov 24, 2009 at 4:45 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> From the 10053 it appears that the different NDV calculation for
> histograms in 10.2.0.4 may be root cause for the flip from NLJ to HJ
> when changing OFE.  If you put these two files in a graphical diff
> tool, it makes it fairly obvious.   Look at the Single Table
> Cardinality Estimation section.
>
> OFE=10.2.0.4
>  Column (#11): PRTY_ID(NUMBER)
>    AvgLen: 4.00 NDV: 232607 Nulls: 1694696 Density: 4.0792e-06 Min: 1
> Max: 1591999
>    Histogram: HtBal  #Bkts: 254  UncompBkts: 254  EndPtVals: 247
>  Using density: 4.0792e-06 of col #11 as selectivity of unpopular value
> pred
>  Table: LETTER_BIN  Alias: LETTER_BIN
>    Card: Original: 4739017  Rounded: 12  Computed: 12.42  Non Adjusted:
> 12.42
>
> OFE=10.1.0
> Column (#11): PRTY_ID(NUMBER)
>    AvgLen: 4.00 NDV: 232607 Nulls: 1694696 Density: 4.8136e-04 Min: 1
> Max: 1591999
>    Histogram: Freq  #Bkts: 247  UncompBkts: 254  EndPtVals: 247
>  Table: LETTER_BIN  Alias: LETTER_BIN
>    Card: Original: 4739017  Rounded: 1465  Computed: 1465.41  Non
> Adjusted: 1465.41
>

I observed this myself using Apple's filemerge but am unsure how to remedy
this.


> What is the difference in execution time of the two queries in
> allstats_both_queries.txt ?
>
>  Anywhere from a 30 seconds to none at all.  That's the puzzling thing.

This may be one of those edge cases that is best dealt with by using
> either a hint or an outline.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
>

Other related posts: