secondary index

[Former Member]

Hi Guru's,

my query is

what is the disadvantage of creating multiple secondary index.

and what is unique index and non-unique index.

Regards,

Satish.

[Former Member · Accepted solution]

Hello, Satish,

When the database engine updates/insert/delete a record, it has to update de index as well. This can produce an extra overload on the database system. Also, when there is multiple indexes, the database engine can use a different rule to access one record from the table. As a rule, I never create more than 5 secondary indexes for the same table. Also, I always try to keep thet database optimizer statistics updated (via tcode DB13); this can make a huge difference in database access performance.

Unique index is an index that don't accept duplicate key; non-unique indexes are indexes that accept duplicate keys.

Hope this helps you!

Andre

[Former Member]

thank you for both.

plz clear my doubts .some times it may be silly doubts.

[Former Member]

Hi,

1.Secondary index : Its created as and when required,

based upon other field(s) of the table,

on which search criteria is used in sqls.

Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column’s selectivity. Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.

To Create Secondary Index

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

2.

A unique key would be helpful in determining a single record from the internal table while performing a search using that key.

Whereas, a non-unique key would not be able to distinctly find out a specific single record for the search that is made using that key. In a table which uses a non-unique key Read statement would pick the first record which shall validate the search criteria.

link:

http://help.sap.com/saphelp_nw2004s/helpdata/en/fc/eb35de358411d1829f0000e829fbfe/frameset.htm

Regards,

If helpful reward with points(Don't forget).