Re: Wrong cardinality estimates if group by (and possibly a hash join)
- From: jonathan@xxxxxxxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 27 Apr 2012 11:20:36 +0100
Laimutis.Nedzinskas@xxxxxx 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
