index ............

[former_member375669]

hi experts

can u plz tell me ...how many indexes can be created for a table?

plz explane u'r answer....

thanks

[seshatalpasai_madala]

Hi,

There will be one Priamry index that is created automatically.

And you can create upto 9 secondary indices.

But you should not create more than 5 as it confuses the Database optmizer in most of the cases and none of the indices might be used in the end for your query.

<b>SAP Documentation</b>

<b>Optimal number of indexes for a table</b>

You should not create more than five indexes for any one table because:

Whenever you change table fields that occur in the index, the index itself is also updated.

The amount of data increases.

The optimizer has too many chances to make mistakes by using the 'wrong' index.

If you are using more than one index for a database table, ensure that they do not overlap.

Regards,

Sesh

[former_member375669]

hi sesh,,

thanks but can u give me any documentation which can describe this concept briefly

[seshatalpasai_madala]

Hi,

Here is the SAP Documentation for Indexes..

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.

However, you should not define an index for all possible fields in the WHERE condition.

Creating a secondary index

You can use the transaction ABAP Dictionary Change &#8594; Indexes... &#8594; Create to create an index. To make the index unique, select UNIQUE. To specify the fields that will comprise the index, choose "Choose fields". Then save and activate the index.

When to create an index

It is worth creating a secondary index when:

You want to select table entries based on fields that are not contained in an index, and the response times are very slow.

The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on &#8594; Execute action &#8594; Trace off &#8594; List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.

The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.

The database table is accessed mainly for reading entries.

Using an index consisting of several fields

Even if an index consists of several fields, you can still use it when only a few of the fields actually appear in the WHERE clause. The sequence in which the fields are specified in the index is important. You can only use a field in the index if all of the preceding fields in the index definition are included in the WHERE condition.

An index can only support search criteria which describe the search value positively, such as EQ or LIKE. The response time of conditions including NEQ is not improved by an index.

Optimal number of fields for an index

An index should only consist of a few fields; as a rule, no more than four. This is because the index has to be updated each time you change its fields in a database operation.

Fields to include in an index

Include fields that are often selected and have a high selectivity. In other words, you need to check the proportion of the table entries that can be selected with this field. The smaller the proportion, the more selective the field. You should place the most selective fields at the beginning of the index.

If all of the fields in a SELECT statement are contained in the index, the system does not access the data a second time following the index access. If there are only a few fields in the SELECT statmeent, you can improve performance significantly by including all of these fields in the index.

You should not include a field in an index if its value is initial for most of the table entries.

Optimal number of indexes for a table

You should not create more than five indexes for any one table because:

Whenever you change table fields that occur in the index, the index itself is also updated.

The amount of data increases.

The optimizer has too many chances to make mistakes by using the 'wrong' index.

If you are using more than one index for a database table, ensure that they do not overlap.

Avoiding OR conditions

The optimizer generally stops if the WHERE condition contains an OR expression. In other words, it does not evaluate the fields in the OR expression with reference to the index.

An exception to this are OR statements standing on their own. Try to reformulate conditions containing an OR expression for one of the indexed fields. For example, replace:

SELECT * FROM SPFLI

WHERE CARRID = 'LH'

AND (CITYFROM = 'FRANKFURT' OR CITYFROM = 'NEW YORK').

with:

SELECT * FROM SPFLI

WHERE (CARRID = 'LH' AND CITYFROM = 'FRANKFURT')

OR (CARRID = 'LH' AND CITYFROM = 'NEW YORK').

Problems with IS NULL

The value NULL is not stored in the index structure of some database systems. The consequence of this is that the index is not used for that field.

Regards,

Sesh

[Former Member]

Hi

there can be only one primary index which is automatically created for all tables

Maximun number of secondry indexes for a table can be 9 , so there is a limit for that

check this FAQ

<a href="http://www.sap-img.com/abap/important-abap-faq.htm">http://www.sap-img.com/abap/important-abap-faq.htm</a>

regards

Gaurav

*reward points for useful answers

[Former Member]

Hi,

Primary indexes are existing indexes we can use directly those indexes. we need to create the secondary indexes. In tables we will use both primary and secondary indexes.

Create secondary indexes step by step procedure.

1. Secondary index is created to enhance the performance. If you create wrongly defined index, it will decreases the performance.

Follow the below steps to create secondary index.

A.Go to t_code (se11) display your table name.

B.To create new index, click on index button on tool bar.

C.Give the index name.

D.Pass short text description for your index.

E.Bottom of the table pass field names on which you want index.

F.Save and activate.

2. You can create up to 16 indexes. But 5 is recommended...

Reward points if useful.

Thanks & Regards,

Vasudeva Rao

[Former Member]

Hi

primary and secondary

reward if usefull

[Former Member]

Hi Ashish,

You can crate a maximim of 9 Index on a database table. But it is recomended to have 5 indexes.

You can create a index on table through transaction se11.Few points are:

You generally create a index when in the where clause there is no primary key.

Put only those fields in index which are acessed repeadetly.

Dont keep those fields which are blank.

Index will fail withi select query with logical operators OR and NOT.

Index entries should be 5% of the database entries then only it is recomended to create an Index.

Rewards points if helpful.

Thanks and Regards

Sourabh Verma

[Former Member]

HI ashish,

u can create one primary and several secondary indexes....

reward points to all helpful answers

kiran.M

[former_member375669]

thanks sesh,

its really helpfull to me ..

[Former Member]

Hi ashish guptha,

You can create more than 9 index , but it will effect the performance of the table.

Regards

nagaraju

pls reward if it is useful

[Former Member]

Do not be misled by ABAP FAQ.

Theorotically there is no limit.In fact you can test it by creating as many indexes ( of course at the cost of the performance ) as you want.

As per ABAP21 days book, u can able to create 15 secondary indexes.

[Former Member]

Do not be misled by ABAP FAQ.

Theorotically there is no limit.In fact you can test it by creating as many indexes ( of course at the cost of the performance ) as you want.

As per ABAP21 days book, u can able to create 15 secondary indexes.