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