ST05 Estimated Costs/Rows - Performance Improvement

[Former Member]

Hey All,

I am trying to use the ST05 Explain SQL statement tool to optimize a select. I am a little confused on the result of the analysis. Can anyone guide me on on what exactly these estimated cost and rows indicate and how to find the best select. The only thing I know is that the cost should be as low as possible... what about the rows?.

I have a select which i am trying to optimize. The table has six primary keys. I have data for all the primary keys except the third one.

The current coding uses only the first primary key which gives.

Original Code:

SELECT STATEMENT ( Estimated Costs = 16 , Estimated #Rows = 267 )

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID ZITM

( Estim. Costs = 16 , Estim. #Rows = 267 )

Estim. CPU-Costs = 256,019 Estim. IO-Costs = 16

1 INDEX RANGE SCAN ZITM~006

( Estim. Costs = 1 , Estim. #Rows = 267 )

Search Columns: 2

Estim. CPU-Costs = 17,121 Estim. IO-Costs = 1

Access Predicates

1st modification.

If I add the second primary key in the where clause I get:

SELECT STATEMENT ( Estimated Costs = 6 , Estimated #Rows = 3 )

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID ZITM

( Estim. Costs = 5 , Estim. #Rows = 3 )

Estim. CPU-Costs = 38,753 Estim. IO-Costs = 5

1 INDEX RANGE SCAN ZITM~006

( Estim. Costs = 5 , Estim. #Rows = 3 )

Search Columns: 3

Estim. CPU-Costs = 35,727 Estim. IO-Costs = 5

Access Predicates

Now the confusing part is if i use all the primary keys except the third one I get:

(there is also a secondry index with these 5 fields.

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

3 INLIST ITERATOR

2 TABLE ACCESS BY INDEX ROWID ZITM

( Estim. Costs = 25 , Estim. #Rows = 1 )

Estim. CPU-Costs = 180,264 Estim. IO-Costs = 25

1 INDEX RANGE SCAN ZITM~0

( Estim. Costs = 25 , Estim. #Rows = 1 )

Search Columns: 4

Estim. CPU-Costs = 178,306 Estim. IO-Costs = 25

Access Predicates Filter Predicates

I am now confused if i should chose all the five keys or only two keys. Why did the cost increase when i used more primary keys. Also in the last select it did not pick the secondary index, rather it is looking at the primary index. Which of the two is a better select? and what do these cost and rows

Any suggestions??

[Former Member]

hi using all the keys is better even there is no use ...for that fields ..it will improve the performance at all ...

[vinod_vemuru2]

Hi Aparna,

We too faced this issue. When we tried with all primary keys select was taking more time. At the same time we have secondary INDEX with few fields in different order. When we try that select was taking half of the time.

So it is upto database optimizer on which optimum way to use. So if u got performance issue like this then best way is to analyze and go ahead with the least cost one.

Make sure that u are passing first primary key to avoid full table scan.

Not sure of what exactly mean estimated cost n rows. If u find some thing please share the same.

Thanks,

Vinod.

[Former Member]

Any other suggestions on how to analyse the Estimated cost\rows?

[former_member194669]

Higher the "estimated costs" will take less time to retrieve the data

a®