Estimated Costs in ST05 transaction

[Former Member]

Hi,

I have asked to analyze whether to apply OSS Note 791479 or not. As per the note "...The ORACLE cost optimizer selects an unsuitable

database index." so by applying the note we forcefully select the suitable database index. I wrote a simple select statement and analyzed through ST05 transaction. Currently it selects MARC_{WRK index and after modifying the code as per OSS note it selects MARA}STD index. For me it looked like selecting MARA_{STD index is correct since it hit the first field in where clause. But I am getting confused by "Estimated Costs" field in ST05 transaction. When it hits MARA}WRK index ST05 is returning "Estimated Costs" = 2 & "Estimated Rows" = 331, whereas when it hits MARA~STD index it's returning "Estimated Costs"=6 & "Estimated Rows" = 1487. Could you explain Estimated Costs and should I implement the OSS note or not?

Current code is:

SELECT COUNT(*) UP TO 1 ROWS FROM MARC WHERE stdpd = lv_matnr

AND werks = iv_plant

AND cuobj NE space.

After applying the OSS note, it will be,

SELECT COUNT(*) UP TO 1 ROWS FROM MARC WHERE stdpd = lv_matnr

AND werks = iv_plant

AND cuobj NE space

%_HINTS ORACLE 'INDEX("MARC" "MARC~STD" "MARC^STD" "MARC______STD"

"MARC______STDX")'.

Thanks in advance.

Regards,

Balaji Viswanath.

[Former Member]

Hi,

I got the below answer from Fidel Vales:

It is a bit difficult to explain how the CBO works in few words.

I'll try to make it simple.

CBO works with statistics. Using those statistics and the query conditions it "calculates" how much "cost" to retrieve the data using ALL possible access methods.

It gives a value to each of those possible access path ( having into consideration not only the table/index statistics, but also other things like the parameters and some internal rules ) Afterwards it chooses the access path with the lowest value.

In your case

index MARA~WRK -> cost 2

index MARA~STD -> cost 6

In normal conditions the CBO will choose the index WRK.

That does not means that the access is fastest, what it indicates is that ( with the current information ) the CBO thinks it will be better using that index.

Some times we know better.

Imagine a table with one million rows. And one field has two different values.

If you use that field Oracle will think that the data distribution is uniform and it will think that 1/2 million rows will be returned and perhaps does not use an index on the mentioned field.

But we know "better" and we know that the data distribution is unbalanced and the query we use returns few rows.

Your case is the same.

Oracle thinks index MARA_{WRK is better, but SAP thinks that, for this specific query, the index MARC}STD is better. It is necessary to provide more information to the CBO to help him to choose the correct index.

That is what the mentioned change does.