Skip to Content

MANDT

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Apr 10, 2008 at 10:09 AM

    Hi Peter,

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

    Regards

    Martin

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Apr 10, 2008 at 07:19 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Apr 10, 2008 at 08:28 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.