Skip to Content
0
Jun 03, 2008 at 01:10 PM

nested loops MSEG MKPF

119 Views

Good afternoon

from a BW system , a user try to extract data in a R3 46C system .

this loading hangs during the access of table mseg for many hours.

the select in hang is the following:

SELECT

T_00 . "MBLNR" , T_00 . "MJAHR" , T_00 . "ZEILE"

FROM

"MSEG" T_00 , "MKPF" T_01

WHERE

( T_01 . "MANDT" = :A0 AND T_01 . "MBLNR" = T_00 . "MBLNR" AND T_01 . "MJAHR" = T_00 . "MJAHR" )

AND T_00 . "MANDT" = :A1 AND T_01 . "CPUDT" >= :A2 AND T_00 . "MATNR" BETWEEN :A3 AND :A4 AND

T_00 . "XAUTO" <> :A5#

The index used is the following:

NONUNIQUE Index MSEG~M

MANDT 1

MATNR 11.877

WERKS 142

LGORT 556

BWART 174

SOBKZ 4

Last statistics date 06.05.2008

Analyze Method Sample 612.175 Rows

Levels of B-Tree 3

Number of leaf blocks 674.400

Number of distinct keys 25.071

Average leaf blocks per key 26

Average data blocks per key 1.635

Clustering factor 40.992.900

I've created a new index to have a better performance:

NONUNIQUE Index MSEG~Z07

Column Name #Distinct

MANDT 1

MBLNR 7.521.784

MJAHR 8

MATNR 11.877

XAUTO 2

Last statistics date 03.06.2008

Analyze Method mple 63.678.689 Rows

Levels of B-Tree 3

Number of leaf blocks 449.133

Number of distinct keys 59.182.326

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 17.885.740

Anyway the query doesn't take this index.

Any suggestion about this query? from sm51 i have the wp running with MSEG.

SAP 46C

Oracle 10.2.0.2

thanks

Nick