topic Re: Fields Orders in Select .. in Application Development and Automation Discussions

Fields Orders in Select ..

former_member425121 — Fri, 26 Jan 2007 17:27:12 GMT

Can somebody helpme in this doubt. I know is good to put the fields in the WHERE clausule at same order that fields appears in the table.

But, i want to confirm, this not applies if there is an Index with other fields sequence ? i mean if in the table FieldX is first than FieldY (these are not in the primary Key) , but there is an index wich has first FieldY than Fieldx; then i must to use in clausule WHERE the same field sequence that the Index (FieldY, FieldX).

Regards

Frank

Re: Fields Orders in Select ..

Former Member — Fri, 26 Jan 2007 17:34:52 GMT

In your specific case where primary-key fields are not in the where-clause, and index-fields are available, i feel it would not matter what the order of the fields is.

The SAP runtime environment will optimize this anyway, and choose the best available index based on your where-clause fields.

This is the reason why, for a table of fields F1, F2, F3, F4, where F1 and F2 are the primary key, and F3 and F4 are a secondary index, and your where-clause has F3, F4 and then F1, the primary-key will still get used.

In a case where F3 has an index and F4 also has an index, then if your where clause has F3 and F4, neither of the indexes will get used, due to ambiguity while trying to resolve indexes.

A more useful recommendation would be to order the fields in the correct sequence in the select-clause, ie., the fields being fetched should be in the correct order.

Re: Fields Orders in Select ..

Former Member — Fri, 26 Jan 2007 18:04:19 GMT

I find it good to put fields in key field order (primary or secondary) to make the program more readable. It will also point out to you (or the next programmer to look at it) if you are missing a key field.

Rob

Re: Fields Orders in Select ..

former_member183804 — Fri, 26 Jan 2007 18:06:48 GMT

Hello Frank,

as Rob already told keeping a certain order will improve readability of source code. Which index is chosen at runtime is left to the optimizer of the DBMS.

Regards,

Klaus

Re: Fields Orders in Select ..

Former Member — Fri, 26 Jan 2007 18:14:26 GMT

Good point Klaus. What I meant was the key you are <i>trying</i> to use. The EXPLAIN function of ST05 will show which key is actually used. And you may want to check that to make sure that the SELECT is working the way you expect.

Rob