Index selection

[Former Member]

Hi,

I wrote one select statement like below.

select lifnr from lfm1 where ekorg in s_ekorg.

Here primary key is "lifnr","ekorg".

Is it consider the primary key, If I use only ekorg in the selections with out "lifnr" ?

Please comment on this.

[HermannGahm]

Hi Paluri,

this depens on many factors:

- which database?

- table size?

- are there other indexes available?

- if there are others, how is their clustering compared to the primary key?

- # distinct keys for lifnr?

- # distinct keys for ekorg?

- how is s_ekorg filled?

- ... many, many more...e.g. statistics, parameters...

Kind regards,

Hermann

[Former Member]

with above all relevant conditions placed by hermann i just have a line to add.

it may fetch the records.. but try fetching both lifnr and ekorg.

[Former Member]

Hi,

There is no other index available other than primary index.

i.e lifnr,ekorg.

[Former Member]

fetch both of them...

[HermannGahm]

Hi Paluri,

then, assuming you have the MANDT column in the index as well, which is the case for primary key:

- SAP MaxDB and MS SQL it will use the index by scanning the whole index, because they have no other chance

- ORACLE , DB2, DB4, DB6... it will depent on mentioned factors

in any case: for big tables perforamcne will be bad... except for a skip scan (ORACLE only) which depends on selectivty of lifnr ... . Performance will be bad since a large amount of pages have to be read... regardless of using the index or not. Maybe not using it could be faster than using it... this again depends again... sorry...

Kind regards,

Hermann

[Former Member]

Hello,

The primary key of LFM1 is made up of MANDT, LIFNR, EKORG. The condition on MANDT is implicitly added to your select by the SAP database interface, but since the WHERE clause does not refer to LIFNR, the primary key index will not be used efficiently here. Oracle will either do a serial read of the table or use only the MANDT field of the primary key (which is non-selective so won't help performance at all).

If this is a realistic case (return all vendor accounts for one or more selected purchasing orgs) then you could consider creating a secondary index on EKORG (or MANDT, EKORG); in the SAP standard version of LFM1 no such index is provided. However, you should be very careful here because EKORG itself might very well be a non-selective field (few distinct values) - I'm not a functional specialist but I don't suppose there are thousands of purchasing organisations. Before you decide on a new index, you should therefore always perform a selectivity analysis on the fields you want to put in the index. You can do that with transaction DB05 or, if you have access to the interactive SQL tool of your database, also with a simple query like the following:

SELECT COUNT(DISTINCT EKORG) FROM SAPR3.LFM1 WHERE MANDT='...';

Incidentally, there is a feature in Oracle that makes it possible to "jump" over leading index fields not specified in the WHERE condition. This is called "skip scan". However, skip scan is beneficial only if the skipped fields are non-selective. I very much doubt that it would be of any use in your example.

Hope this clarifies things a bit,

Mark

[Former Member]

but the simple answer is 'no'

> select lifnr from lfm1 where ekorg in s_ekorg.

> Here primary key is "lifnr","ekorg".

in most cases indexes can only be used in order of their fields up to a gap, i.e. the field you don't know.

Here the gap is the first field, lifnr. So you should not expect any index support.

Hermann has pointed out exceptions which can help, if you are lucky.

Siegfried

P.S.: I did not see the 3 messages in between.

[Former Member]

YK added a comment to my blog [Performance - what will kill you and what will leave you with only a flesh wound|/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound] that said in part:

ORACLE 9i introduced index skip scan to allow index lookups if you don't provide the first column in your WHERE clause.

It works well if and only if the leading edge of the index has very few discrete values and the optimizer understands that.

So it's not a silver bullet against a flawed WHERE clause like in your examples

Developers should have more knowledge about the data distributions than any piece of optimizer can "read" from it's statistics.

Rob

[Former Member]

As I pointed out in my first reply, Oracle skip scan will be of no help here because LIFNR is a highly selective field. I mainly mentioned it because I didn't want anyone to tell you "sure, nothing to worry, the skip scan will handle this". And after all we don't even know which DBMS you are using anyway...

So it's back to the basics:

- Your query is not selective (likely to return a large amount of data on average)

- It cannot adequately be served by an index

- Creating an index is probably not a good idea because the WHERE condition only tests on a non-selective field (unless selectivity analysis disproves that - see my earlier reply)

... all of which means that this is a case where long response times are to be expected and users just may have to live with them.

Regards,

Mark

[Former Member]

>

> As I pointed out in my first reply, Oracle skip scan will be of no help here ...

Sorry Mark, I saw your earlier thread but missed the part about the Oracle skip scan. I included it only for completeness.

Rob

[Former Member]

By the way (sorry, I forgot to mention this): there is another pitfall in your SELECT statement and that is the "IN S_EKORG". I don't know how much experience you have with ABAP programming, so I apologize if I tell you things you've known for years, but when a range table (like your S_EKORG, the one you specify with SELECT-OPTIONS in ABAP) is empty, then this is treated as *, so everything is selected. This is a trap many beginning ABAP developers have walked into. If you don't want to let users accidentally select everything then the AT SELECTION-SCREEN part of your program should contain something like

if s_ekorg is initial.
  message e002(sy) with 'Please select at least one purchasing organization".
endif.

[Former Member]

> You can do that with transaction DB05 or, if you have access to the interactive SQL tool of your

> database, also with a simple query like the following:

with an Oracle or DB2 database you should simply make an SQL trace of one call of the transaction SE16, shows you the statistics.

the COUNT( DISTINCT ) is newer than the statistics ... but the statistics count, if outdated, then better update them. MSSQL is not see easy to read. And Max DB shows you the statistcs only in the newer versions with the DB02.

Coming bach to the statement, maybe the FULL TABLE SCAN is actually appropriate if not more information is available.

Siegfried