Secondary Index

[Kalyan12]

Hi,

I want to create a Secondary Index on Standard SAP Table and our database in MSSQL. Should I include MANDT as the first field of my index or not? Please give explanation as to why?

Thanks & Regards,

Kalyan.

[Former Member]

Hi,

With SQL Server they recommend that MANDT should not use in Index.

Thanks,

[Kalyan12]

Hi,

I would like to know why it is so. Also, is there any SAP Documentation supporting this activity?

Thanks.

[Former Member]

Mandt is not required , while querying the database interface will prefix the login client and it would fetch the data.

Here they talk about the fields not about mandt while creating secondary index.

What to Keep in Mind for Secondary Indexes

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eb47446011d189700000e8322d00/frameset.htm

[christine_evans]

I think I would always include MANDT as the first column of any secondary index, unless my system had multiple clients and I wanted to do something odd such as use the index together with CLIENT SPECIFED in my SELECT to bypass the automatic inclusion of the client to select data from all clients. An unlikely thing to want to do, since the use of client is intended to provide a logical separation of data.

SAP always seems to use MANDT in its indexes - though the fact that SAP does something does not always mean that you should do the same thing. But including MANDT seems like the natural thing to do because it reinforces the logical separation of data.

If you've got a single client in your system, then I don't think specifying MANDT in an index will make any difference to performance. But if you've got multiple clients, then it could make a difference. For example, there are 10,000 records across 3 clients which all hold the same value in field1; 100 in client 1, 1900 in client 2, 8000 in client 3. If you create an index on field1 without using MANDT, any SELECT using the automatic client handling would have to trawl through 10,000 entries if this field queried in any of the 3 clients. But, if you include MANDT, in client 1 it would only have to trawl through 100 entries and so (hopefully) would be quicker.

We;ve got Oracle, but I don't think the choice of database makes any difference to this.