Re: Wrong cardinality estimates if group by (and possibly a hash join)
Date: Fri, 27 Apr 2012 14:51:43 +0300
Message-ID: <OF6D04DA3B.BAC69780-ONC22579ED.00401BD4-C22579ED.00412966_at_seb.lt>
>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).
ok, I've added the materialize hint to illustrate the point: apparently the problem happens if view transformation and group by are involved. As for the hint effect on the plans cardinalities then the innner plan dealings can differ of course. But the output, the return rows of both querries (which differ only in materialize hint) can not vary that wildly:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 53 | 954 | 12342 (1)|00:02:29 |
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1917K| 10M| | 31291 (2)| 00:06:16 | ---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
From: jonathan_at_jlcomp.demon.co.uk
To: oracle-l_at_freelists.org
Date: 2012.04.27 13:21
Subject: Re: Wrong cardinality estimates if group by (and possibly a hash join)
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-l -- http://www.freelists.org/webpage/oracle-lReceived on Fri Apr 27 2012 - 06:51:43 CDT