Query Optimization

[Former Member]

Hi,

I have read in a Note 825653 point 1, we need to start the where clause with = column for a select to discard unnecessary searches or improving the CBO.

I believ most of the queries will start with the MANDT, which always have a equal sign for the query.

My question is, does the Note talking about the Colums after MANDT ?

Thanks and Regards,

Srikanth.

[Former Member]

Hi Srikanth,

The note basically talks about all index fields. From database perspective MANDT is just another field like any other. Only from SAP application perspective this field has special meaning, as the SAP database interface adds in queries with client specific tables automatically the condition on MANDT.

So for some time I know the first point in OSS note [825653 - Oracle: Common misconceptions|https://service.sap.com/sap/support/notes/825653] was partially also a reason to consider adding MANDT to your index (otherwise in a system with one client a questionable practice). Check out in that OSS note 22 SAP indexes must always begin with the client column.

Then take a look at OSS note [176754 - Problems with CBO and RBO|https://service.sap.com/sap/support/notes/176754] and check out point 18. There you can see that this concern is no longer necessarily valid.

Cheers, harald

[Former Member]

Hi Harald

Ok, so from both the Notes can we conclude that, what ever may be the Query,If MANDT is mentioned then CBO want guess the costs.It will Provide the aprroximate Costs or Estimates.

Suppose we add to a Query which has no MANDT or '=' sign in the first column by mentioning MANDT to take the Client value from T100. Added Condition is I only use 1 client which is a master Client and the Table has only master Client data.

Does the Cost or Estimates will improve now ? Please suggest.

Thanks and Regards,

Srikanth.

[stefan_koehler]

Hello Srikanth,

why not posting the problematic query with all relevant information?

It seems like you don't have any idea how the optimizer is working and you try to analyze an issue without that background.

Run the query, collect the information with the script from sapnote #1257075 and post it here.

If you really want to know how the CBO works - buy this book:

http://www.amazon.com/Cost-Based-Oracle-Fundamentals-Experts-Voice/dp/1590596366

Regards

Stefan

[Former Member]

Unfortunately our database is 9i , where i cannot run this script.My question was just to clear my Doubt, Please dont guess my knowledge just by post.

here i am posting you the query SELECT

"MATNR" , "WERKS" , "VBELN" , "POSNR" , "CLSTA" , "VBTYP"

FROM

"YESTA"

WHERE

"MANDT" = :A0 AND "MATNR" BETWEEN :A1 AND :A2 AND "WERKS" BETWEEN :A3 AND :A4

AND "CLSTA" = :A5#

SELECT STATEMENT ( Estimated Costs = 48 , Estimated #Rows = 55 )

3 FILTER

2 TABLE ACCESS BY INDEX ROWID YESTA

( Estim. Costs = 48 , Estim. #Rows = 55 )

1 INDEX RANGE SCAN YESTA__2

( Estim. Costs = 280 , Estim. #Rows = 78,567 )

Search Columns: 3

Disk reads reads/exec buffgets bgets/exec proces rows Rproc/exec Bgets/row

6,460,597 215,353.2 11,824,787 394,159.6 3,786,871 126,229.0 3.1

please let me know you need any furthur information.

Edited by: srisun on Apr 29, 2010 12:50 PM

[stefan_koehler]

Hello Srikanth,

the query and the execution plan is not enough information.

We don't know which columns the index "YESTA__2" contain, we don't know the statistic, we don't know the column order of the indexes, etc.

In general you could say .. the optimal index order for that specific kind of query would be:

MANDT, CLSTA, MATNR, WERKS (assuming that CLSTA is very selective)

Maybe your problem is not a CBO one (if the correct index is chosen), maybe its a design problem of that table and its corresponding indexes. Depending on the additional columns of table YESTA, maybe an IOT would also be an option (regarding to that specific SELECT clauses).

> My question was just to clear my Doubt, Please dont guess my knowledge just by post.

That's no problem ... please don't misunderstand my previous post .. it is just hard (or nearly impossible) to explain somebody how the CBO works (or in which cases what is great or not) without a certain basic knowledge level on the other side and a specific example. Just talking about silver bullets is not very suggestive.

Regards

Stefan

[Former Member]

Hi Stefan,

thanks for you reply , here are some other stats that would be helpful

index YESTA_2

Column Name #Distinct

MANDT 1

WERKS 166

MATNR 42,454.

could you please put some light in explaing the column order impact on the search criteria or mention me some note to be referred.

Thanks,

Srikanth.