Secondary Index + client field

[Former Member]

I posted this already as an answer to another thema but since it is very important to me (and since there were some mistakes in my answer) now I make a new theme out of it:

When I look at non-unique secondary indizes in the data dictionary (Release 4.6c) I find that around two of three non-unique secondary Indizes of the standard SAP tables (I looked up by checking the tables dd03l, dd12l and dd17s) have the client field (rollname = mandt) as an entry, one third don't have the client field at all.

I wonder what could be the arguments using the client field in a secondary index or not.

Since we have performance problems in using huge data tables it would be very important for me to know if the usage of the client field as an index field brings more advantages or more disadvantages. We don't have any transactions where the "client specified" clause has to be used.

Kind regards Axel Kiltz

[Former Member]

I can't quite determine if you know what the client field is.  Do you know what it's used for?

The only time you'd specify the client is if there's a need to access data in another client within the same database otherwise the client is always assumed to be the user's currently logged-on client anyway.

Maybe this will help a bit.

Excerpt from --> http://help.sap.com/sapdocu/core/47x200/HELPDATA/EN/83/08443c4a089537e10000000a114084/frameset.htm

Viewed technically, each time a client-specific table is accessed, the database interface implies an additional selection of the active logon client, meaning that the WHERE clause is added to the SQL statement.

"WHERE <client-field> = <login-client>".

This substitution is made dynamically in the database interface at runtime.

[Former Member]

Hello Tim,

I know very well the special meaning of the client field. This special meaning is in the centre of my problem!

You gave the example with the select-condition, which is implicitly added by:

... where ...

Now once again the question: is the select using a secondary index in average faster when the non-unique secondary index contains the client field or will it even be slower?

The other question remains too: why is the client field used in non-unique secondary indizes about 2000 times and is not used about 1000 times?  (There are about 3000 secondary non-unique indizes in standard SAP tables in our actual development system)

[Former Member]

In my experience where the client field is a key field of the SAP table, then it should also be the first field of each of the table's indices.

In my opinion it is an oversight. IIRC, there have been SAP notes in the past about correcting such indices, which can cause performance problems when testing migration programs particularly.

Of course, this will often be unnecessary in a productive environment where there is usually only one client containing application data.

Cheers,

Scott

[Former Member]

Hello Scott,

I did not understand completely what you wanted to say in your topic. What means IIRC? Could you specify more precisely where you found those sap notes?

it is very important to me to find an official SAP statement to that problem.

Kind regards Axel

[Former Member]

IIRC = If I recall correctly

I found those notes in OSS. That's if I recall correctly. 😛

If it's so important, why not just log the problem with SAP?

Scott

[Former Member]

Hello Scott,

I think you're right. I'll open an SAP OSS-message for that problem.

Thank you!

[Former Member]

I say that you should put the client field LAST in the index. It is an extremely unselective field (a constant in your PRD environment), so you are handicapping your index by using it. It's like if, in the Yellow Pages, every entry started with 'The', like

The Luigis Pizza

The Michaels Pizza

The Perfect Pizza

As you can see, 'The' simply wastes space in the Yellow Pages, and the Client wastes index space in the database. But because SAP's indexes all contain it, you'd better put it in yours as well. otherwise your index may not get chosen. But put it LAST!

[Former Member]

I understand and agree with your logic but disagree with putting the client last.

The answer is really to either put the client in as the first index field, or to leave it out altogether. The choice made will be specific to the needs of each installation and is dependent on the number of "real" clients in a system. Undoubtedly the client field will not be necessary in most production systems, where there is only one "real" client (excluding clients like 000 and 066).

However, this must be considered against the impact to other systems (such as QA and Training) where there may be multiple clients as here the client field would be most useful as the first field of the index.

It would be nice if we could define the index as including the client field for certain systems only...

Cheers,

Scott

Message was edited by: Scott Barden

[Former Member]

If you have single client no need to specify client field in the index .