Wrong cardinality estimates if group by (and possibly a hash join)
Date: Thu, 26 Apr 2012 15:30:08 +0300
Message-ID: <OF553B0DCF.C87CB126-ONC22579EC.004405E6-C22579EC.0044ADBF_at_seb.lt>
In Oracle 11.2.0.3 and 10.2.0.3 group by produces wrong cardinalities. See the inner hash join cardinality bellow.
Does anyone know a patch/workarround for this particular problem? Tried a few sugestions (_optimizer_improve_selectivity and _optimizer_use_feedback) with no success.
Wrong:
explain plan for
with v as (select * FROM sebim.ip
WHERE
EXISTS (SELECT 1
FROM classif.cl
WHERE cl.cl_id = ip.idv_r_f
)
)
SELECT ip_id from v
group by ip_id
/
select * from table(dbms_xplan.display());
Plan hash value: 958521158
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
| 0 | SELECT STATEMENT | | 53 | 954 | 12342 (1)|
00:02:29 |
| 1 | HASH GROUP BY | | 53 | 954 | 12342 (1)|
00:02:29 |
|* 2 | HASH JOIN | | 53 | 954 | 12341 (1)| 00:02:29 |
| 3 | SORT UNIQUE | | 109K| 640K| 69 (2)|
00:00:01 |
| 4 | INDEX FAST FULL SCAN| I_CL_ID | 109K| 640K| 69 (2)|
00:00:01 |
| 5 | TABLE ACCESS FULL | IP | 1917K| 21M| 11902 (1)|
00:02:23 |
Predicate Information (identified by operation id):
2 - access("CL"."CL_ID"="IP"."IDV_R_F")
Good:
explain plan for
with v as (select /*+materialize */* FROM sebim.ip
WHERE
EXISTS (SELECT 1
FROM classif.cl
WHERE cl.cl_id = ip.idv_r_f
)
)
SELECT ip_id from v
group by ip_id
/
select * from table(dbms_xplan.display());
Plan hash value: 2650865608
------------------------------------------------------------------------------------------------------------------.
| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time |. ------------------------------------------------------------------------------------------------------------------.
| 0 | SELECT STATEMENT | | 1917K|
10M| | 31291 (2)| 00:06:16 |.
| 1 | TEMP TABLE TRANSFORMATION | | |
| | | |.
| 2 | LOAD AS SELECT | | |
| | | |.
|* 3 | HASH JOIN RIGHT SEMI | | 1917K| 334M| | 12038 (1)| 00:02:25 |
| 4 | INDEX FAST FULL SCAN | I_CL_ID | 109K|
640K| | 69 (2)| 00:00:01 |.
| 5 | TABLE ACCESS FULL | IP | 1917K|
323M| | 11957 (1)| 00:02:24 |.
| 6 | HASH GROUP BY | | 1917K|
10M| 44M| 19254 (2)| 00:03:52 |.
| 7 | VIEW | | 1917K|
10M| | 12996 (1)| 00:02:36 |.
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6838_431F294B | 1917K|
334M| | 12996 (1)| 00:02:36 |. ------------------------------------------------------------------------------------------------------------------.
Predicate Information (identified by operation id):
3 - access("CL"."CL_ID"="IP"."IDV_R_F")
Please consider the environment before printing this e-mail
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Apr 26 2012 - 07:30:08 CDT