Re: Wrong cardinality estimates if group by (and possibly a hash join)
Date: Fri, 27 Apr 2012 11:20:36 +0100
Message-Id: <E1SNiI0-000Iws-0r_at_pr-webmail-2.demon.net>
Laimutis.Nedzinskas_at_seb.lt wrote:
> >This really isn't an issue with the group by cardinality as the output of
> the GBY
>
>
> I pasted the test query and plan again at the end of the email.
>
> Consider those two fragments, same tables, same number of rows, same
> predicates:
>
If you need a detailed analysis of what's going wrong you'll have to raise an SR. Your extract (cut) does suggest that you've found a bug, possibly relating to existence subqueries, but without looking at the trace file in detail I don't think anyone would want to confirm your suspicion,
One critical detail in the basic example you sent: If you materialized "select *" and then select one column from the CTE then it's not surprising if you see a different plan from the case where you don't materialized. In the later case oracle can inline the CTE and use column projection to minimise the select list (henec the index FFS, probably).
If you want to chase this further then add the 'PROJECTION' (or 'ADVANCED' option to the call to dbms_xplan so that you can see the column projection in the materialization.
This difference MAY be indicating an anomaly caused by a difference between the table (or column) stats and the index stats.
Regards
Jonathan Lewis
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 27 2012 - 05:20:36 CDT