How to create secondary index

[Former Member]

Hi All,

Nyone plz let me know how to create a secondary index and how to make use of it.....

Thanks in advance

Amarender Reddy B

[Former Member]

Hi,

Normally key-fields are used to unique identification.

standard or z tables create the index by default for key-fields.

for better perfomance of select statements we have to use the key fileds in where condition.

Suppose there is a situation example you need to get all material information from MARA table by using material group is MATKL, MBRSH . but these fields are non-key fields, when we use in select statement performance wise it causes worst.

in this situation you have to create secondary index, but remember one thing dont create secondary index unless it highly priority to create. its take same memory capcity of what exactly table having, lets suppose mara occupies memory like 10 MB, when u create secondary index it will also require 10 MB memory.

for this also you need to take permission from basis people.

Steps to create Secondary index.

-goto SE11 select the table name and dispay it.

- press the tab '<b>INDEX'</b> then you will get one popup screen here you have to give name of the index and press ok it ask work bench request under that save that.

-it opend the index maintain screen and here you need to fill some attributes like 'short description', non-unique or unique index.

-enter the field names which you want to create index and press enter and save it.

-<b>activate it</b>.

<b>Reward with points if useful.</b>

Regards,

Vijay

[Former Member]

Hello Vijay,

Thanks for the reply.....now i understood almost.....but i am little confused about the memory usage what you have explained.....

apart from this memory, is there any other disadvantages?

Thanks again

Amarender Reddy B

[Former Member]

GO TO TRANSACTION SE11. OPEN A TABLE. oN TOP THERE IS INDEXES PUSHBUTTON. pRESS IT. GIVE A INDEX NAME AND INSERT THE FIELDS TO BE CONTAINED IN THAT INDEX.IF THERE IS MANDT FIELD, PUT THAT INTO THE INDEX IT WILL IMPROVE PERFORMANCE. SAVE IT AND ACTIVATE IT. ACTIVATE THE TABLE.

REGARDS,

MALLICK

[former_member194613]

secondary indices use disk space, not memory space. They are only in memory when they are used. Disk space is roughly width times number of records, can be GBs.

Main disadvantages are:

+ indexes are updated when table is changed, i.e. the additional index makes other operations slower. You must check the importance of your application, if it is much lower than the standard usage than you can not create a secondary index.

+ Secondary indices can confuse the database optimzer, especially if you use field which appear also in other indices. DB calculates usefulness of different indices by some assumptions (check other sources for details), if two indices are similar, than the assumptions can lead to wrong decisions. Your new index can be used for other statements, even when it is not optimal there. Result your index causes problems somewhere else!

You should be quite familiar with indices before you create a new index.

Siegfried

[Former Member]

Hi

Index: Technical key of a database table.

Primary index: The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

Secondary index: Additional indexes could be created considering the most frequently accessed dimensions of the table.

The database optimizer decides which index on the table should be used by the database to access data records.

You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated

An index can be used to speed up the selection of data records from a table.

An index can be considered to be a copy of a database table reduced to certain fields. The data is stored in sorted form in this copy. This sorting permits fast access to the records of the table (for example using a binary search). Not all of the fields of the table are contained in the index. The index also contains a pointer from the index entry to the corresponding table entry to permit all the field contents to be read.

When creating indexes, please note that:

An index can only be used up to the last specified field in the selection! The fields which are specified in the WHERE clause for a large number of selections should be in the first position.

Only those fields whose values significantly restrict the amount of data are meaningful in an index.

When you change a data record of a table, you must adjust the index sorting. Tables whose contents are frequently changed therefore should not have too many indexes.

Make sure that the indexes on a table are as disjunctive as possible.

(That is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.)

reward if usefull

[former_member194613]

Please be aware that

> That is they should contain as few fields in common as possible. If two indexes

> on a table have a large number of common fields, this could make it more

> difficult for the optimizer to choose the most selective index

with the cost based optimizers (used in all databases) this problem start already if one field is common in two indices!

[Former Member]

Thanks all, points are yours......:)