Re: Wrong cardinality estimates if group by (and possibly a hash join)
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 27 Apr 2012 14:51:43 +0300
>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@xxxxxxxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Date: 2012.04.27 13:21
Subject: Re: Wrong cardinality estimates if group by (and possibly a hash
join)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
