query improvement

nicola_blasi — Wed, 16 Feb 2011 10:50:30 GMT

Hello

I have this query with an high estimated cost. The index seems correct .

I think the problem could be the order by.

How can i improve the performances of this query?

thanks

Nicola

SELECT

"MANDT" , "MESTYPE" , "CPIDENT" , "PROCESS" , "TABNAME" , "TABKEY" , "FLDNAME" , "CRETIME" ,

"ACTTIME" , "USRNAME" , "CDOBJCL" , "CDOBJID" , "CDCHGNO" , "CDCHGID"

FROM

"BDCP2"

WHERE

"MANDT" = :A0 AND "MESTYPE" = :A1 AND "PROCESS" = :A2

ORDER BY

"MANDT" , "CPIDENT"#

Execution Plan

Explain from v$sql_plan: Address: 0000000417A7B140 Hash_value: 4212140235 Child_number: 0 Sql_id: 287w0agxj0a6b

Parse Timestamp: 20110216 11:23:47

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

3 SORT ORDER BY

( Estim. Costs = 32.486 , Estim. #Rows = 372.147 )

Estim. CPU-Costs = 694.565.717 Estim. IO-Costs = 32.383

2 TABLE ACCESS BY INDEX ROWID BDCP2

( Estim. Costs = 23.271 , Estim. #Rows = 372.147 )

Estim. CPU-Costs = 210.335.152 Estim. IO-Costs = 23.240

1 INDEX RANGE SCAN BDCP2~001

( Estim. Costs = 1.334 , Estim. #Rows = 372.147 )

Search Columns: 3

Estim. CPU-Costs = 24.173.204 Estim. IO-Costs = 1.330

Access Predicates

Last statistics date 27.01.2011

Analyze Method Sample 357.261 Rows

Number of rows 35.726.100

Number of blocks allocated 662.674

Number of empty blocks 240

Average space 913

Chain count 0

Average row length 126

Partitioned NO

index 0

MANDT 1

MESTYPE 48

CPIDENT 11.392.466

index 001

MANDT 1

MESTYPE 48

PROCESS 2

CRETIME 57.406

CPIDENT 11.392.466

Re: query improvement

Rui_Dantas — Wed, 16 Feb 2011 13:07:00 GMT

Hi,

The query is already as good as it gets, though you might want to remove the ORDER BY and instead SORT in abap.

If the statistics are correct your BDCP2 table is too big (35 million entries!), and that's where your problem lies.

Make sure you keep it small by cleaning regularly with transaction BD22 / report RBDCPCLR.

Regards,

Rui Dantas

topic Re: query improvement in Application Development and Automation Discussions

query improvement

Re: query improvement