Indexes

[Former Member]

how many indexes can be created for a table?

[Former Member]

as many as u like .

see this documentation.

There are two kinds of indexes. one is primary index and other one is secondary index.

primary index will be automatically created by the system.

but user has to define this secondary index.

Maximum of 15 secondary index is possible.(As per 21 days book).

but keep the secondary index as minimum as possible.

Indexes

You can search a table for data records that satisfy certain search criteria faster using an index.

An index can be considered a copy of a database table that has been reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search. The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can also be read.

The primary index is distinguished from the secondary indexes of a table. 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.

Table SCOUNTER in the flight model contains the assignment of the carrier counters to airports. The primary index on this table therefore consists of the key fields of the table and a pointer to the original data records.

You can also create further indexes on a table in the ABAP Dictionary. These are called secondary indexes. 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.

All the counters of carriers at a certain airport are often searched for flight bookings. The airport ID is used to search for counters for such an access. Sorting the primary index is of no use in speeding up this access. Since table SCOUNTER has a large number of entries, a secondary index on the field AIRPORT (ID of the airport) must be created to support access using the airport ID.

The optimizer of the database system decides whether an index should be used for a concrete table access (see How to Check if an Index is Used?). This means that an index might only result in a gain in performance for certain database systems. You can therefore define the database systems on which an index should be created when you define the index in the ABAP Dictionary (see Creating Secondary Indexes.)

All the indexes existing in the ABAP Dictionary for a table are normally created in the database when the table is created if this was not excluded in the index definition for this database system.

If the index fields have key function, that is if they already uniquely identify each record of the table, an index can be defined as a unique index.

See also:

What to Keep in Mind when Creating Secondary Indexes

What to Keep in Mind for Secondary Indexes

How well an existing index supports data selection from a table largely depends on whether the data selected with the index represents the data that will ultimately be selected. This can best be shown using an example.

An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. This table is accessed with the SELECT statement:

SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4.

Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it will quickly find all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.

The order of the fields in the index is very important for the accessing speed. The first fields should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

Only those fields that significantly restrict the set of results in a selection make sense for an index.

The following selection is frequently made on address file ADRTAB:

SELECT * FROM ADRTAB WHERE TITEL = ‘Prof.’ AND NAME = X AND VORNAME = Y.

The field TITLE would rarely restrict the records specified with NAME and FIRSTNAME in an index on NAME, FIRSTNAME and TITLE, since there are probably not many people with the same name and different titles. This would not make much sense in this index. An index on field TITLE alone would make sense for example if all professors are frequently selected.

Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index therefore slows down the insertion of records in the table.

For this reason, tables in which entries are very frequently written generally should only have a few indexes.

The database system sometimes does not use a suitable index for a selection, even if there is one. The index used depends on the optimizer used for the database system. You should therefore check if the index you created is also used for the selection (see How to Check if an Index is Used).).

Creating an additional index could also have side effects on the performance. This is because an index that was used successfully for selection might not be used any longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the newly created index is more selective.

The indexes on a table should therefore be as disjunct 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.

How to Check if an Index is Used

Procedure

Open a second session and choose System ® Utilities ® Performance trace.

The Trace Requests screen appears.

Select Trace on.

The SQL trace is activated for your user, that is all the database operations under your user are recorded.

In the first window, perform the action in which the index should be used.

If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.

In the second session, choose Trace off and then Trace list.

Result

The format of the generated output depends on the database system used. You can determine the index that the database used for your action with the EXPLAIN function for the critical statements (PREPARE, OPEN, REPOPEN).

Unique Indexes

An entry in an index can refer to several records that have the same values for the index fields. A unique index does not permit these multiple entries. The index fields of a unique index thus have key function, that is they already uniquely identify each record of the table.

The primary index of a table is always a unique index since the index fields form the key of the table, uniquely identifying each data record.

You can define a secondary index as a unique index when you create it. This ensures that there are no double records in the table fields contained in the index. An attempt to maintain an entry violating this condition in the table results in termination due to a database error.

The accessing speed does not depend on whether or not an index is defined as a unique index. A unique index is simply a means of defining that certain field combinations of data records in a table are unique.

A unique index for a client-dependent table must contain the client field.

Index IDs

Several indexes on the same table are distinguished by a three-place index identifier. The index identifier may only contain letters and digits. The ID 0 is reserved for the primary index.

The index name on the database adheres to the convention .

TEST~A is the name of the corresponding database index in the database for table TEST and the secondary index with ID A.

Since the convention for defining the index name in the database has changed several times, some of the indexes in the database might not follow this convention.

Indexes created prior to Release 3.0 can have an 8-place name. The first 7 places (possibly filled with underlining) are for the table names, and the eighth place is for the (one-place) index identifier (for example TEST___A).

Indexes introduced with Release 3.0 can have a 13-place name in the database. The first 10 places (possibly filled with underlining) are for the table names, and the 11th to 13th places are for the three-place index identifier (for example TEST______A).

if it helps it well and good.

reward all helpfull answers

Message was edited by:

sunil kumar

Message was edited by:

sunil kumar

[Former Member]

Hi.....

there is no limitation you can create Any number.....

Reward points if useful......

Suresh......

[Former Member]

as many as u like .

see this documentation.

There are two kinds of indexes. one is primary index and other one is secondary index.

primary index will be automatically created by the system.

but user has to define this secondary index.

Maximum of 15 secondary index is possible.(As per 21 days book).

but keep the secondary index as minimum as possible.

Indexes

You can search a table for data records that satisfy certain search criteria faster using an index.

An index can be considered a copy of a database table that has been reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search. The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can also be read.

The primary index is distinguished from the secondary indexes of a table. 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.

Table SCOUNTER in the flight model contains the assignment of the carrier counters to airports. The primary index on this table therefore consists of the key fields of the table and a pointer to the original data records.

You can also create further indexes on a table in the ABAP Dictionary. These are called secondary indexes. 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.

All the counters of carriers at a certain airport are often searched for flight bookings. The airport ID is used to search for counters for such an access. Sorting the primary index is of no use in speeding up this access. Since table SCOUNTER has a large number of entries, a secondary index on the field AIRPORT (ID of the airport) must be created to support access using the airport ID.

The optimizer of the database system decides whether an index should be used for a concrete table access (see How to Check if an Index is Used?). This means that an index might only result in a gain in performance for certain database systems. You can therefore define the database systems on which an index should be created when you define the index in the ABAP Dictionary (see Creating Secondary Indexes.)

All the indexes existing in the ABAP Dictionary for a table are normally created in the database when the table is created if this was not excluded in the index definition for this database system.

If the index fields have key function, that is if they already uniquely identify each record of the table, an index can be defined as a unique index.

See also:

What to Keep in Mind when Creating Secondary Indexes

What to Keep in Mind for Secondary Indexes

How well an existing index supports data selection from a table largely depends on whether the data selected with the index represents the data that will ultimately be selected. This can best be shown using an example.

An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. This table is accessed with the SELECT statement:

SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4.

Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it will quickly find all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.

The order of the fields in the index is very important for the accessing speed. The first fields should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

Only those fields that significantly restrict the set of results in a selection make sense for an index.

The following selection is frequently made on address file ADRTAB:

SELECT * FROM ADRTAB WHERE TITEL = ‘Prof.’ AND NAME = X AND VORNAME = Y.

The field TITLE would rarely restrict the records specified with NAME and FIRSTNAME in an index on NAME, FIRSTNAME and TITLE, since there are probably not many people with the same name and different titles. This would not make much sense in this index. An index on field TITLE alone would make sense for example if all professors are frequently selected.

Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index therefore slows down the insertion of records in the table.

For this reason, tables in which entries are very frequently written generally should only have a few indexes.

The database system sometimes does not use a suitable index for a selection, even if there is one. The index used depends on the optimizer used for the database system. You should therefore check if the index you created is also used for the selection (see How to Check if an Index is Used).).

Creating an additional index could also have side effects on the performance. This is because an index that was used successfully for selection might not be used any longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the newly created index is more selective.

The indexes on a table should therefore be as disjunct 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.

How to Check if an Index is Used

Procedure

Open a second session and choose System ® Utilities ® Performance trace.

The Trace Requests screen appears.

Select Trace on.

The SQL trace is activated for your user, that is all the database operations under your user are recorded.

In the first window, perform the action in which the index should be used.

If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.

In the second session, choose Trace off and then Trace list.

Result

The format of the generated output depends on the database system used. You can determine the index that the database used for your action with the EXPLAIN function for the critical statements (PREPARE, OPEN, REPOPEN).

Unique Indexes

An entry in an index can refer to several records that have the same values for the index fields. A unique index does not permit these multiple entries. The index fields of a unique index thus have key function, that is they already uniquely identify each record of the table.

The primary index of a table is always a unique index since the index fields form the key of the table, uniquely identifying each data record.

You can define a secondary index as a unique index when you create it. This ensures that there are no double records in the table fields contained in the index. An attempt to maintain an entry violating this condition in the table results in termination due to a database error.

The accessing speed does not depend on whether or not an index is defined as a unique index. A unique index is simply a means of defining that certain field combinations of data records in a table are unique.

A unique index for a client-dependent table must contain the client field.

Index IDs

Several indexes on the same table are distinguished by a three-place index identifier. The index identifier may only contain letters and digits. The ID 0 is reserved for the primary index.

The index name on the database adheres to the convention .

TEST~A is the name of the corresponding database index in the database for table TEST and the secondary index with ID A.

Since the convention for defining the index name in the database has changed several times, some of the indexes in the database might not follow this convention.

Indexes created prior to Release 3.0 can have an 8-place name. The first 7 places (possibly filled with underlining) are for the table names, and the eighth place is for the (one-place) index identifier (for example TEST___A).

Indexes introduced with Release 3.0 can have a 13-place name in the database. The first 10 places (possibly filled with underlining) are for the table names, and the 11th to 13th places are for the three-place index identifier (for example TEST______A).

if it helps it well and good.

reward all helpfull answers

Message was edited by:

sunil kumar

Message was edited by:

sunil kumar

[Former Member]

Hi,

You can create 9 indexes for a table

Please close the duplicate thread

Regards

Sudheer

[Former Member]

Hi Sriram,

We can create one primary index and a maximum of 15 Secondary Indexes.

Hope this resolves your query.

Reward all the helpful answers.

Regards

[Former Member]

hi sriram,

U can create n number of indexes, all those will be secondary indexes . There is only one primary index. But creating a number of indexes will inturn increase the database size, as every time new records are inserted in that table those records are also inserted in that indexing table which stores the index created. So although the dbase access from that table will speedup but the other process (like insertion of records ) wil slow down. So the number of secondary indexes created

should be small. But if it is necessary then you should go for secondary index. Try to make a balance between number of indexes created and frequency of dbase updates.

I hope this will help.

Pls reward if it helps.

Regards

Tomesh