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 >