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 14:33:56 -0500

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
>

Other related posts: