RCOPCA02 GLPCA

[nicola_blasi]

Hello

I have a problem using report RCOPCA02,

when i double click a line to have an output list it takes a long time.

The problem could be the raise of table GLPCA and through the note 212964 the only solution , i suppose, is

to create another index for this report.

The query is the following:

SELECT                                                                                
"RCLNT" , "RLDNR" , "KOKRS" , "RYEAR" , "RBUKRS" , "RRCTY" , "RVERS" , "POPER" , "RTCUR" ,        
  "RUNIT" , "DRCRK" , "DOCCT" , "DOCNR" , "DOCLN" , "RPRCTR" , "RHOART" , "RFAREA" , "RACCT" ,      
  "HRKFT" , "RASSC" , "EPRCTR" , "ACTIV" , "AFABE" , "SBUKRS" , "SPRCTR" , "SHOART" , "SFAREA" ,    
  "TSL" , "HSL" , "KSL" , "MSL" , "CPUDT" , "CPUTM" , "USNAM" , "SGTXT" , "AUTOM" , "DOCTY" ,       
  "BLDAT" , "BUDAT" , "WSDAT" , "REFDOCNR" , "REFRYEAR" , "REFDOCLN" , "REFDOCCT" , "REFACTIV" ,    
  "AWTYP" , "AWORG" , "AWSYS" , "WERKS" , "GSBER" , "KOSTL" , "LSTAR" , "AUFNR" , "AUFPL" ,         
  "ANLN1" , "ANLN2" , "MATNR" , "BWKEY" , "BWTAR" , "ANBWA" , "KUNNR" , "LIFNR" , "RMVCT" ,         
  "EBELN" , "EBELP" , "KSTRG" , "ERKRS" , "PS_PSP_PNR" , "KDAUF" , "KDPOS" , "FKART" , "VKORG" ,    
  "VTWEG" , "AUBEL" , "AUPOS" , "SPART" , "VBELN" , "POSNR" , "VKGRP" , "VKBUR" , "LOGSYS" ,        
  "VERSA" , "STFLG" , "STOKZ" , "STAGR" , "GRTYP" , "REP_MATNR" , "CO_PRZNR" , "IMKEY" , "DABRZ" ,  
  "RSCOPE" , "BWART" , "BLART"                                                                      
FROM                                                                                
"GLPCA"                                                                                
WHERE                                                                                
"RLDNR" = :A0 AND "RRCTY" IN ( :A1 , :A2 ) AND "RVERS" = :A3 AND "KOKRS" = :A4 AND "RYEAR" = :A5  
  AND "RACCT" = :A6 AND "POPER" BETWEEN :A7 AND :A8 AND ( ( "ACTIV" BETWEEN :A9 AND :A10 OR "ACTIV" 
  BETWEEN :A11 AND :A12 OR "ACTIV" BETWEEN :A13 AND :A14 OR "ACTIV" BETWEEN :A15 AND :A16 ) OR      
  "ACTIV" IN ( :A17 , :A18 ) ) AND "RCLNT" = :A19#       

and it uses this index:

RYEAR

POPER

RBUKRS

RACCT

it has an estimate cost too high. i'd like to reduce it. which index do you suggest for this activity? my doubt is about the field RCLNT that is at the end of the query...it sounds strange....anyway any help is appeciated...

thanks a lot

Nick

[HermannGahm]

Hi Nicola,

how big is the result set of your query?

Generally speaking, those colums that limit the result set most should be indexed.

You can check with SE16 which of your variables limit your result set most.

Kind regards,

Hermann

[nicola_blasi]

Hello

this is the estimated cost:

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

5 FILTER

5 TABLE ACCESS BY INDEX ROWID GLPCA

INDEX RANGE SCAN GLPCA~YBA

any ideas?

thanks

Nick

[HermannGahm]

Hi,

what fiels does the YBA index consist of? (RYEAR POPER RBUKRS RACCT )?

WHERE "RLDNR" = :A0

AND "RRCTY" IN ( :A1 , :A2 )

AND "RVERS" = :A3

AND "KOKRS" = :A4

AND "RYEAR" = :A5

AND "RACCT" = :A6

AND "POPER" BETWEEN :A7 AND :A8

AND ( ( "ACTIV" BETWEEN :A9 AND :A10

OR "ACTIV" BETWEEN :A11 AND :A12

OR "ACTIV" BETWEEN :A13 AND :A14

OR "ACTIV" BETWEEN :A15 AND :A16 )

OR "ACTIV" IN ( :A17 , :A18 ) )

AND "RCLNT" = :A19

In SE16 you can check which of the above variables limit the resultset most.

Normally these coloumns should be indexed.

Again: How big is the result set of your query? (What is the time per record?)

Kind regards,

Hermann

[Former Member]

Make sure the controling area is completed in the SELECT-OPTIONS.

Rob

[Former Member]

I don't know if you have this field, but you can add field RPRCTR : with this supplementary field, you will use index GLPCA~1 of table GLPCA.

Regards,

Samuel