MANDT

[peter_strauss]

Hello,

A noob question for you.

Why do most indexes on client specific tables start with MANDT?

Having MANDT at all often seems strange because it nearly always has very low cardinality.

I've read things like "if a table begins with MANDT and the index doesn't then there is a high chance that the index won't be used by the CBO" and "the CBO favours indexes that has the same column order as the fields in the where clause".

Is this true? If so, why?

Is it a mistake to create an index (on a client specific table) that does not have MANDT as the first field?

Regards,

Peter

[Former Member · Accepted solution]

Hi Peter,

I think note 825653 (22) should be the perfect answer to this thread.

Regards

Martin

[peter_strauss · Accepted solution]

Thank you all for your help.

[stefan_koehler]

Hello Peter,

>> Why do most indexes on client specific tables start with MANDT?

This is designed by SAP, if the table is client specific the database engine automatically adds the mandt field in the select statement. If you have more clients you can filter out many entries for client specific tables from the beginning.

>> "if a table begins with MANDT and the index doesn't then there is a high chance that the index won't be used by the CBO"

That is not correct. It depends on the query and the statistics.

>> the CBO favours indexes that has the same column order as the fields in the where clause

That is wrong and maybe a misinterpretation:

- It is only "important" that all index columns are in the where clause, because of the index tree can only be accessed "with full path", if all fields are specified

- The order of index columns is taking an important part, if non-selective columns are not specified with "=" in the select statement

There are also some nice sapnotes #825653 and #176754 which are describing the behaviour of the CBO in some cases.

Regards

Stefan

[Former Member]

if a table begins with MANDT and the index doesn't then there is a high chance that the index won't be used by the CBO

-> not true

the CBO favours indexes that has the same column order as the fields in the where clause

-> plain wrong

Is it a mistake to create an index (on a client specific table) that does not have MANDT as the first field?

-> if you have only one client, and you create another index you can savely obmit the MANDT column

Index access is about selectivitiy of the indexed columns. It is important, that your where clause is selective when you use an index.

Regards

Michael