indexes in data dictionary

[Former Member]

Hi all,

can anyone tell me what is the process of creating a secondary index.?

can we create a secondary index if a field is assigned with primary key?

can we create secondary index for a mara table field?

when we click on INDEXES in data dictionary ---> database table by selecting a field then we get unique index and non-unquie index, what does that mean?

regards,

satish

[Former Member]

Hi

There is a tab called secondary index.

Select that and you can create secondary index.

Primary key field is nothing but primary index and why do you want to create secondary index for that when you have primary index.

A field can be primary and secondary index.

unique index is primary index and non is nothing but non index key.

Thanks

[Former Member]

What is Secondary Index:

first i will tell u little about 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.

You can create further indexes on a table in the ABAP Dictionary. These are called secondary indexes.

PURPOSE:

This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access. Different indexes on the same table are distinguished with a three-place index identifier.

Generally it is used when field which u want is not a primary field .

Advatange:

1. faster access to data when field is not a primary key field.

Disadvatnage:

1. Slower down database table inset, update delete commands.

***************************************************

You go for secondary index when you have any SELECT statement which has a WHERE clause which is not effectively using the existing index(s). But this decision of additional index has to be made in sync with the Basis and Functional team. Every additional index will be a burden on the database and the system resources.

***************************************************

The secondary index is a type of index in database table. Indexes are used to improve the performances of data retrieval from the table. This happens by passing the values to these indices during SELECT query in the WHERE clause.

There are different types of indices. Primary key is default present for all the tables in database. Only ONE Primary key can exist in a database table. It results in unique records in the database table.

The other type of index is secondary index. Multiple fields can be grouped to make a secondary index of table. If all the field values is passed during SELECT query, the performance increases drastically.

Not many fields should be included in secondary index. Large number of fields reduces the performance of INSERT query of database.

***********************************************

Indexes are used to speed up data selection from the database. System chosse and use the index based on the fields given in WHERE and HAVING clause.

The primary index is always created automatically in the R/3 System. It consists of the primary key fields of the database table. This means that for each combination of fields in the index, there is a maximum of one line in the table.

If you are searching on non-key fields, you can create your own secondary index, which can restrict the number of table entries searched to form the result set.

Improper use of Secondary index can slow down the performance. Discuss with Basis team before creating a secondary index.

********************************************************

Check the threds -

***************************

Primary index: the primary index contains key fiels of a table and a pointer to non-key fields of the table. The primary index is created automatically when a table is created in database and moreover you can further define reference to the primary index which are known as Secondary index.

How many indexes can be created for a table? -> 9

When you are selecting the data on non primary key,obviously system will give output with bad performance ,if you create secondary index then it gives better performance

GOTO SE11 -> give ur table name -> click on display button -> click on index button ->click on create button -> give id ( z98 like ) -> give field names MANDT ,then ur field names

save and activate

Regards

Vasu

[paruchuri_nagesh]

hi

Indexes - What are they and how can I use them?

An index helps to speed up selection from the database. An index is a sorted copy of selected database table fields.

The primary index is always automatically created in an ABAP-based SAP system. It consists of the primary key fields of the database table. This means, for each combination of the index fields exists a maximum of one record in the table. This kind of index is called a UNIQUE index.

If the primary index cannot be used to determine selection result, (for example, the WHERE condition does not contain any primary index fields), the system searches the whole table. To prevent this, and determine the selection result by searching through a restricted number of database records, you can create a secondary index.

reward if u find useful

regards

Nagesh.Paruchuri