Cardinality or full table scans
I'm seeing the execution plans in 10g that have total Estimate #Rows = 0 for full table scans. This doesn't make sense.
"MATNR" , "WERKS" , "BWTAR" , "CHARG"
"MANDT" = :A0#
Explain from gv$sql_plan: Address: C00000057CCCC028 Hash_value: 22
Parse Timestamp: 20071030 14:18:34
SELECT STATEMENT ( Estimated Costs = 3.137 , <b>Estimated #Rows = 0</b> )
1 TABLE ACCESS FULL MCHA
( Estim. Costs = 3.136 , Estim. #Rows = 1.134.670 )
Estim. CPU-Costs = 928.730.441 Estim. IO-Costs = 2.936
How can Estim. #Rows = 1.134.670 for the outer FULL SCAN while at the same time Estimated #Rows = 0 for the SELECT?
Martin Frauendorfer replied
this is nothing else than an Oracle bug. The related V$SQL_PLAN column is not filled - of course also in the first line the estimated rows should be 1134670.
As an SAP employee you have access to SAP customer messages and so you can have a look at message 466760 2007 that discusses this issue. Oracle is already informed about this display bug and I hope we will get a fix mid-term.