Secondary Index with or without MANDT field

[former_member298409]

HI ABAP Guru's,

What is necessary to add field MANDT while creation of secodary index in DBS.

But i some body my superiors challanged to me with out using MANDT our secoday index won't works.

But i tested few scenarios i am not get differnce.

please advice me exactly which scenarios it is mandatory.

Below are the Time taken with deffrent scnarios i created one test program to get the time with secondary index with out secodary index secondary index with mndat fiels

**&with out creation of seconday index

*1st time -57,103,681

2nd Time-55,388,294

**before creation of seconday index with out mandt

I1st time execution-324,119

2nd time progrm execution--391,134

3rd time progrm execution-327,046

4th time progrm execution336,774

5th time progrm execution359,100

6t time progrm execution-328,027

*before creation of seconday index with mandtiI1st time execution-367,623

2nd time progrm execution365,139

3rd-352,328

4th-369,122

5th-352,236

6th380,590

7th466,810

Thanks In Advance,

Kandula.

Edited by: Thomas Zloch on Nov 18, 2011 1:08 PM

[Former Member]

In client dependent db tables, MANDT is the field that is at the highest level to isolate data belonging to a client, so only client specific records are shown and processed when one logs on into a specific client.

So any secondary index on a table with MANDT should also have MANDT, otherwise the index key records will get clubbed with other client data. So if you are maintaining multiple client data on the same database, then not adding MANDT to index will have undesirable effects as any select based on secondary index may return records that are not belonging to this client and deletes and additions on the index from multiple clients will lead to data integrity issues.

More than about performance the question then becomes of data integrity and consistency.

If the database has a single client, then having or not having MANDT in secondary index won't impact performance or cause integrity issue as aforesaid. But it is always preferable to have MANDT in the secondary index as having multiple clients on a single database is a basic and desirable feature in SAP (especially for dev/test boxes if a company wants to spend less on hardware)

[yuri_ziryukin]

> So any secondary index on a table with MANDT should also have MANDT, otherwise the index key records will get clubbed with other client data. So if you are maintaining multiple client data on the same database, then not adding MANDT to index will have undesirable effects as any select based on secondary index may return records that are not belonging to this client and deletes and additions on the index from multiple clients will lead to data integrity issues. 
> 
> More than about performance the question then becomes of data integrity and consistency.
> 

What a nonsense!

If you have no idea about indexes, please avoid posting in ABAP Performance forum.

To the original question:

MANDT field in client-dependent tables are always a part of the SELECT clause. It is added automatically to the query before it goes to DB. Having MANDT in the index has the following advantage:

- If MANDT is in the index and all selected fields are also in the index, no database access will be necessary -> much better performance.

Disadvantage:

- Additional index field require more space for the index.

For example, in SAP CRM we have a large table CRMD_ORDER_INDEX. It has many fields and many indexes. Practically none of the indexes has client field. Probably to make the indexes looking more unique (and avoid mistakes in cost-based optimizer) and save space.

Regards,

Yuri

[volker_borowski2]

So if you are maintaining multiple client data on the same database, then not adding MANDT to index will have undesirable effects as any select based on secondary index may return records that are not belonging to this client and deletes and additions on the index from multiple clients will lead to data integrity issues. 

Wrong!

WHERE clause decides about data being selected, deleted or what-ever.

Index decides only about HOW data is accessed (if used), not WHAT data is accessed.

If your database returns a different result depending on the indexdefinition,

you should log a call at your DB vendor immediately, because this is a bug.

In general, as the client has usually only a small number of distinct values, it is not a good field,

to convince the database, that this index is a good idea. But on high volume tables it can be very selective

as far as the number of result records is concerned (might cut down 50% when 2 clients!).

In addition it is a very short field, so it should not cost much storage (esp. when compressable).

Szenario:

MANDT+IDX-Field with two clients and lets say 5000 record per client (so that idx access will be interesting),

assuming a given IDX value will return 50 records (25 in each client).

So the select will be

... WHERE MANDT=sy-mandt AND IDX=value

Accesing the index with only IDX will result in stepping down the index-tree (say 3 blocks) and then reading leafblocks

to get the 50 hits for IDX-value (assuming 30 records per leafblock -> 2 leafblocks required to gret the 50 records)

Right now you have accessed 5 blocks to get the address of 50 records that still need to be checked against MANDT.

So there is need to get 50 blocks (may be less, depending on clustering) to do a filter on MANDT

and get the final 25 records for the result.

If you put the MANDT field into the index it might require more space, so that we assume 20 records per leaf block now.

But since you can now filter MANDT already on the index blocks, you will again only need to get 5 blocks and

have the adress of the required 25 target records.

So getting the result is 55 blockinspections without MANDT in index and 30 blockinspections with MANDT in index in this case.

Now you can start pushing around values and statistics an calculate at what amount of data and average

size of resultsets it becomes right or wrong to include MANDT. It may turn out both ways, allthough I think

with MANDT being small, it is usually loss of brain cycles to calculate around for this.

You simply include it, it will cost only little space and it will never be wrong.

You leave it out, you will gain little space, but might end up with performance loss.

If you have only one client in the system, you can safely go with the saving space strategie, as long as you do not need

a UNIQUE secondary index.

Volker

[Former Member]

Hi Volker / Yuri,

Thanks for your explanation. It is very interesting and I think, I should spend more time on this area of indexes and query optimizations work.

I agree, I should read more and speak less on performance forum.

Best regards,

Vishnu T

[former_member194613]

Please be aware that the recommendation not to include the client field in the secondary indexes is outdated! There have been negative effects especially with client dependent joins. So it might still be useful in some older releases with some database types. Many of the secondary indexes created about 5 to 10 years ago have probably no client field and will not be changed, because nobody dares to changes existing indexes.

The current recommendation is to include the client to a secondary key. In most cases it is not selective, but it is always given with an equal condition, i.e. no overhead! It is so short that the space argument is not really valid and has advantages concerning transportation, partitioning and archiving.

This is actually the recommendation for systems, where most of the data are in one client. If there are several substanial clients, then it is anyway a nonbrainer to include the client.

Siegfried

[Former Member]

Siegfried,

Your advice contradicts OSS Note 912620 - FAQ Oracle indexes.pdf

"16. Should indexes always begin with the client column?

In most cases, it is not necessary to include the client in the index."

Can you please update the OSS note?

Thanks,

Jean

Edited by: Jean Mooij on Jan 16, 2012 3:31 PM

[Former Member]

In my experience if you do not use Client in the index the DB optimizer do not take the index into account. So more or less it is obligatory for client dependent tables.

[dave_price]

It does not appear to be true that database cost-based optimizers require MANDT, I have added indexes without MANDT in ECC and seen enormous performance benefits using both SQLServer and Oracle DBs.

FWIW I am also OCP certified as an Oracle DBA and have worked as DBA/developer in several other DBs, and I'd be surprised if any CBO post-2000 for an enterprise DB could be so bad at calculating optimization that leaving out MANDT could cause an otherwise high-value index to be ignored.

I tend to believe that using MANDT is generally not a good idea, since production environments tend to be single-client, and even in multiple client situations MANDT cardinality is usually so low relative to the other fields you're using that MANDT would tend to be a wasted hop in the index tree anyway.  Of course, in most applications the difference is going to be so trivial as to be undetectable either way (like in Siva kandula's original post), but maybe there's a white paper somewhere.

(I realize this thread is old, but this is a valuable discussion that appears in current search results.)

[rodrigo_jordodemagalhesro]

In production systems is not necessary to include the field MANDT in secondary indexes, because you have only one client with business data!
So , the optimizer will not take it into account.

Text from note 912620 - FAQ: Oracle indexes

"16. Should indexes always begin with the client column?

              For historical reasons, many SAP indexes begin with the client column (MANDT, CLIENT, MANDANT, ...). In many cases, this column only increases overhead by increasing the size of the index. The client column is only useful or advantageous in a limited number of situations:

• There are selections that can be completely covered by the index if the index includes the client, which makes a processing-intensive table search unnecessary.
• The client is used in a unique index to guarantee application consistency.
• A short-term work-around is required for the CBO (Cost-Based Optimizer) problem described in Note 176754 (18).
• The table contains several filled clients. Therefore, the client condition is selective in this context.

              In most cases, it is not necessary to include the client in the index.