The 10053 with the default parameters (ofe 10.2.0.4) can be found at http://www.neilkodner.com/10053_default.txt The 10053 with the altered session (ofe 10.1.0) is at http://www.neilkodner.com/10053_10_1_0.txt On Tue, Nov 24, 2009 at 3:32 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote: > Can you put up 10053 traces from > select * from letter_bin, where prty_id=102985 > one with the default OFE, and one with 10.1 or 9.2? > > > On Tue, Nov 24, 2009 at 11:33 AM, Neil Kodner <nkodner@xxxxxxxxx> wrote: > > If I simply select * from letter_bin, where prty_id=102985 with all > default > > session parameters, I get cardinatlity of 12, which is the number of not > > null values / NDV. > > If I change OFE to 10.1.0 or 9.2.0, I get a correct value for > cardinality. > > > > On Tue, Nov 24, 2009 at 2:28 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> > wrote: > >> > >> So then it appears the predicate ("LETTER_BIN"."PRTY_ID"=102985) via > >> LETTER_BIN_PRTY_ID_I is row source with the issue. In the first query > >> it looks to be off by 2 orders of magnitude, 1692 vs 14, Thats > >> usually not so good in an NL plan. > >> > >> Are the stats on the table and index both using 100% sample? > >> Is there a histogram on LETTER_BIN. PRTY_ID? > >> If you switch OFE to 9.2 just for the test query, do you get a > >> different cardinality estimate, or is it that OFE=9.2 just yields an > >> overall better plan for the full query? > >> > >> On Tue, Nov 24, 2009 at 11:19 AM, Neil Kodner <nkodner@xxxxxxxxx> > wrote: > >> > For what its worth, if I just explain plan on select * from letter_bin > >> > where > >> > prty_id=12345, I get an incorrect cardinality estimate. For that > >> > reason, I > >> > have not tried isolating other fields. > >> > >> -- > >> Regards, > >> Greg Rahn > >> http://structureddata.org > > > > > > > > -- > Regards, > Greg Rahn > http://structureddata.org >