topic Re: Order in where clause - SQL statement in Application Development and Automation Discussions

Order in where clause - SQL statement

Former Member — Wed, 08 Aug 2007 19:33:20 GMT

Hi,

The order of the fields in Where clause in OpenSQL statements is important to get the right index?

Select a b c from t1

where

d = p_d and

e = p_e

Select a b c from t1

where

e = p_e and

d = p_d

Index:

columns e and d.

Thanks !

Re: Order in where clause - SQL statement

Former Member — Wed, 08 Aug 2007 19:36:03 GMT

Hi SRM,

The indexes should appear in the column index is preferable in the WHERE condition of SELECT statement.

Check this thread which is related you your query

Friendly Note: Don't post duplicate threads

Thanks,

Vinay

Re: Order in where clause - SQL statement

Former Member — Wed, 08 Aug 2007 19:37:24 GMT

There's no need to post duplicate threads accross forums. Please close one.

I think it's good programming practice to specify fields in the index order, but I don't know of any case where it actually has an impact on performance (in Oracle or DB2).

Rob

Re: Order in where clause - SQL statement

Former Member — Wed, 08 Aug 2007 20:06:29 GMT

Thank you for repost!

I already close the other post (I got a timeout and confused if that one was opened).

Ok, we have here two different answers:

Rob believes it don´t impact the performance.

The post Vinaykumar sent suggests it is necessary (http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_StructureOfWhereClause.asp)

So, my question is how can I know, using SQL Trace, which columns of the index are being used (only the MANDT, or the MANDT and the second, or the MANDT, sedond and third column)?

Thanks!

Re: Order in where clause - SQL statement

marcelo_ramos1 — Wed, 08 Aug 2007 20:39:08 GMT

Hi,

To check if an Index is Used do as follow:

1 - Open a second session and choose System -> Utilities -> Performance trace.

The Trace Requests screen appears.

2 - Select Trace on.

The SQL trace is activated for your user, that is all the database operations under your user are recorded.

3 - In the first window, perform the action in which the index should be used.

If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.

4 - In the second session, choose Trace off and then Trace list.

Result

The format of the generated output depends on the database system used. You can determine the index that the database used for your action with the EXPLAIN function for the critical statements (PREPARE, OPEN, REPOPEN).

Regards.

Marcelo Ramos

Re: Order in where clause - SQL statement

Former Member — Wed, 08 Aug 2007 20:40:52 GMT

But how can I know which columns are being used in the index???

Re: Order in where clause - SQL statement

Former Member — Wed, 08 Aug 2007 20:44:00 GMT

Use the explain function of the SQL trace with both SELECTs. I'm betting that it will show exactly the same cost for each.

The EXPLAIN will show you which index is used.

(This is why you should only have one post open. I saw the other post but missed your question here.)

Rob

Message was edited by:

Rob Burbank

Re: Order in where clause - SQL statement

marcelo_ramos1 — Wed, 08 Aug 2007 21:07:03 GMT

Hi,

On the SQL Trace list click on table name that you want to know index fields and then click on DDIC Info button(or press F6).

On the next screen you have a list of Indices of <table>, if the selection is using an index it'll be marked with "X" on Column Unique.

If your selection doesn't uses index the line primary key will be marked in Column Unique.

Click on index or primary key and then you'll see the fields that you want.

Don't forget to close this Thread.

Best Regards.

Marcelo Ramos

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 07:01:41 GMT

The order is not mandatory but recommended to follow the other of the index, there is another thread (not yours) on the same topic.

> This should be obvious otherwise new indexs would oonyl influence new coding,

whcih is not true. That fact has positive and negative impacts.

The explain of the SQL trace shows you which index is used (all database platforms), some even tell you how many fields are used. Usually you can judge by yourself, how many fields of an index can be used.

Siegfried

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 07:41:10 GMT

you can use any one

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 12:29:51 GMT

But it lists all the fields in the index, correct?

I would like to know which index fields are used in my query....

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 13:20:23 GMT

It should use the leftmost fields that you have specified in the where.

For example if the index that a WHERE uses contains (in order) fields d, a, b, g and r and your WHERE contains d, a and r. The optimizer will attempt to use fields d an a only.

However, it also depends on the selectivity of the fields in the index. If field d is say a company code and you have one large company code and a few smaller ones, specifying the large company code won't give as good performance as specifying a smaller one. It will help, particularly since you are also specifying field a.

If field a is say a date and you specify a large date range, that also will not be as selective as specifying a smaller date range.

Hope all of this helps.

Rob

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 13:33:46 GMT

Thanks Rob.

I understand the optimizer would use the leftmost fields that I have specified.

My question is: In SQL Trace or anywhere do I have this information listed (so that I don´t have to check the code) ?

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 14:13:00 GMT

Not that I know of, but we're programmers here. We check code for a living.

Rob

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 19:57:57 GMT

My conclusion:

The order in Where clause is not important.

There is no way to check which columns of the index are really used.

Thank you!

Re: Order in where clause - SQL statement

Former Member — Thu, 09 Aug 2007 19:58:06 GMT

Re: Order in where clause - SQL statement

Former Member — Fri, 10 Aug 2007 08:21:45 GMT

> The explain of the SQL trace shows you which index is used (all database

> platforms), some even tell you how many fields are used. Usually you can

> judge by yourself, how many fields of an index can be used.

Please read answers carefully, check your SQL trace explain,

Some databases tell you how many fields are used !

Others tell you enough to get it implicitly, check selectivity ( 1/ (number of differnt values of field), calculate complete slectivity => how many rows are expected,

compare with number of rows expected by the database !

Order of field is not really important, however simple statements should always follow the order in the index, because statement cache should not be filled with indentical statements.

Siegfried

Re: Order in where clause - SQL statement

Former Member — Fri, 10 Aug 2007 08:25:19 GMT

> If field a is say a date and you specify a large date range, that also will not be

> as selective as specifying a smaller date range.

This is a confusing comment, the database decision which index should be taken, does NOT use the values of the field, i.e. it does not exploit the fact that interval are large or small!

=> all intervals get the selectivity

Only in the processing the difference turns out!

Siegfried