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