index in BW chain

[nicola_blasi]

Hello i have a problem about a BW chain that collect data in an R3 46C system.

It goes in table access full in table BPBK . This table has only the primary index

UNIQUE     Index   BPBK~0                         
                                                  
Column Name                     #Distinct         
                                                  
MANDT                                          1  
BELNR                                  1.175.508  

which index could be better for this query?

thanks

Nicola

SELECT                                                                                
"BELNR" , "BLDAT" , "BUZEI" , "GJAHR" , "LEDNR" , "OBJNR" , "SGTEXT" , "TWAER" , "VERSN" ,            
  "VORGA" , "WLGES" , "WRTTP" , "WTGES"                                                                 
FROM                                                                                
"BPVG"                                                                                
WHERE                                                                                
( ( "OBJNR" BETWEEN :A0 AND :A1 OR "OBJNR" BETWEEN :A2 AND :A3 OR "OBJNR" BETWEEN :A4 AND :A5 OR      
  "OBJNR" BETWEEN :A6 AND :A7 OR "OBJNR" BETWEEN :A8 AND :A9 OR "OBJNR" BETWEEN :A10 AND :A11 OR        
  "OBJNR" BETWEEN :A12 AND :A13 OR "OBJNR" BETWEEN :A14 AND :A15 OR "OBJNR" BETWEEN :A16 AND :A17       
  OR "OBJNR" BETWEEN :A18 AND :A19 OR "OBJNR" BETWEEN :A20 AND :A21 OR "OBJNR" BETWEEN :A22 AND         
  :A23 OR "OBJNR" BETWEEN :A24 AND :A25 OR "OBJNR" BETWEEN :A26 AND :A27 OR "OBJNR" BETWEEN :A28        
  AND :A29 OR "OBJNR" BETWEEN :A30 AND :A31 OR "OBJNR" BETWEEN :A32 AND :A33 OR "OBJNR" BETWEEN         
  :A34 AND :A35 OR "OBJNR" BETWEEN :A36 AND :A37 OR "OBJNR" BETWEEN :A38 AND :A39 OR "OBJNR"            
  BETWEEN :A40 AND :A41 OR "OBJNR" BETWEEN :A42 AND :A43 OR "OBJNR" BETWEEN :A44 AND :A45 ) OR          
  "OBJNR" IN ( :A46 , :A47 , :A48 , :A49 , :A50 , :A51 , :A52 , :A53 , :A54 , :A55 , :A56 , :A57 ,      
  :A58 , :A59 , :A60 , :A61 , :A62 , :A63 , :A64 , :A65 , :A66 , :A67 , :A68 , :A69 ) ) AND "WRTTP"     
  IN ( :A70 , :A71 ) AND "CPUDT" BETWEEN :A72 AND :A73 AND "MANDT" = :A74 AND "CPUDT" BETWEEN :A75      
  AND :A76# 
    

                                                                                Execution Plan                                                                                
SELECT STATEMENT ( Estimated Costs = 1.132 , Estimated #Rows = 8 )                                                                                
5  FILTER                                                                                
5  NESTED LOOPS                                                                                
TABLE ACCESS FULL BPBK                                                                                
5  TABLE ACCESS BY INDEX ROWID BPEG                                                                                
INDEX RANGE SCAN BPEG~0 

[former_member192616]

Hi,

since you use ranges check in SE16

which of your where conditions limit the result set most.

Consider creating an index for these.

Kind regards,

Hermann

[former_member182566]

Hello Nicola,

BW often uses a date/time field to determine which records must be transferred.

To improve performance it is often recommended to have an index by this field (in your case CPUDT).

Ru Dantas