Wrong cardinality estimates if group by (and possibly a hash join)

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-l

Other related posts: