Wrong cardinality estimation for hash group by
Hi All,
My DB is 11.1.
I have a exectuion plan like this:
Please note that before hash group by, the row count estimation is accurate. But after group by, it's totally messed up.
My DB is 11.1.
I have a exectuion plan like this:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 46 | 95726 | 1443 (27)| 00:00:07 | | |
| 1 | NESTED LOOPS OUTER | | 46 | 95726 | 1443 (27)| 00:00:07 | | |
| 2 | NESTED LOOPS OUTER | | 46 | 94668 | 1350 (29)| 00:00:06 | | |
| 3 | VIEW | | 46 | 93012 | 657 (59)| 00:00:03 | | |
| 4 | HASH GROUP BY | | 46 | 93012 | 657 (59)| 00:00:03 | | |
| 5 | VIEW | | 689K| 1328M| 382 (29)| 00:00:02 | | |
|* 6 | CONNECT BY WITHOUT FILTERING | | | | | | | |
| 7 | TABLE ACCESS FULL | O_PAE | 689K| 24M| 382 (29)| 00:00:02 | | |
| 8 | PARTITION LIST ALL | | 1 | 36 | 15 (0)| 00:00:01 | 1 | 14 |
| 9 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_PROD_DIM | 1 | 36 | 15 (0)| 00:00:01 | 1 | 14 |
|* 10 | INDEX RANGE SCAN | O_PD_IDX1 | 1 | | 14 (0)| 00:00:01 | 1 | 14 |
|* 11 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PROD_DIM | 1 | 23 | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 12 | INDEX RANGE SCAN | O_PD_PKN | 1 | | 1 (0)| 00:00:01 | | |
Please note that before hash group by, the row count estimation is accurate. But after group by, it's totally messed up.
0