Skip to Content
0
Former Member
May 06, 2011 at 03:02 AM

How to improve performance query to table WLK1 ?

137 Views

Hi,

I have table WLK1 with around 1.2 Million Data, currently SAP standard program for PLU Outbound download utilize this table very heavly. Even though only 1.2 Million but the performance is not that good.

This is below the standard Query from SAP. not sure why SAP like to use select * in stead of only required field.

SELECT * FROM "WLK1"
WHERE "MANDT" = :A0 AND "FILIA" = :A1 AND "ARTNR" IN ( :A2 , :A3 , :A4 , :A5 , :A6 ) 
  AND "DATBI" >= :A7 AND "DATAB" <= :A8 

I am planning to create secondary index base on FILIA, ARTNR, DATBI and DATAB to improve performance, because current base on the explain plan Oracle database is using index WLK12 (FILIA and DATAB) in stead of WLK10.

Please advise.

Thank You and Best Regards

Fernand

There is 2 standard Index for WLK1

UNIQUE Index WLK1~0

Column Name #Distinct

MANDT 1

FILIA 1,836

ARTNR 64,796

VRKME 1

DATBI 124

LFDNR 9

Last statistics date 15.04.2011

Analyze Method Sample 143,591 Rows

Levels of B-Tree 2

Number of leaf blocks 12,962

Number of distinct keys 1,575,970

Average leaf blocks per key 1

Average data blocks per key 1

Clustering factor 322,655

NONUNIQUE Index WLK1~2

Column Name #Distinct

MANDT 1

FILIA 1,836

DATAB 107

Last statistics date 15.04.2011

Analyze Method Sample 229,844 Rows

Levels of B-Tree 2

Number of leaf blocks 7,194

Number of distinct keys 1,836

Average leaf blocks per key 7

Average data blocks per key 161

Clustering factor 152,308

Explain Plan :

SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 0 )

2 TABLE ACCESS BY INDEX ROWID WLK1

( Estim. Costs = 1 , Estim. #Rows = 1 )

Estim. CPU-Costs = 6,928 Estim. IO-Costs = 1

Filter Predicates

1 INDEX RANGE SCAN WLK1~2

( Estim. Costs = 1 , Estim. #Rows = 8 )

Search Columns: 3

Estim. CPU-Costs = 4,593 Estim. IO-Costs = 1

Access Predicates