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 >