Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Cardinality or full table scans

Hello,

I'm seeing the execution plans in 10g that have total Estimate #Rows = 0 for full table scans. This doesn't make sense.

eg.

SELECT

 "MATNR" , "WERKS" , "BWTAR" , "CHARG"

FROM

"MCHA"

WHERE

"MANDT" = :A0#

Execution Plan

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

Filter Predicates

How can Estim. #Rows = 1.134.670 for the outer FULL SCAN while at the same time Estimated #Rows = 0 for the SELECT?

Kind regards,

Peter

Former Member
Former Member replied

Hello Peter,

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.

Regards

Martin

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question