Cardinality or full table scans

[peter_strauss]

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 · Accepted solution]

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

[peter_strauss · Accepted solution]

Hello Martin,

I did find this message, but just skipped to the end where it sort of peters out.

I see that earlier in the message there is a detailed explanation of what is going on and from this I could have worked out that it's a bug.

It's great to have a definitive answer straight from the horse's mouth.

Kind regards,

Peter