Skip to Content
0
Feb 16, 2011 at 10:50 AM

query improvement

114 Views

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