09-08-2009 10:17 AM
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
09-08-2009 10:48 AM
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
09-08-2009 10:48 AM
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
09-08-2009 11:19 AM
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
09-08-2009 11:44 AM
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
09-08-2009 3:06 PM
Make sure the controling area is completed in the SELECT-OPTIONS.
Rob
09-09-2009 2:16 PM
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