Table Index

[Former Member]

hi all,

i'm currently doing code optimization in CRM.

i want to ask about table index.

is there any best practise guide for designing or making table index ? in saphelp, it said that a table index is better not contain more than 4 combination fields and a table should have indexes less than 5 indexes.

is there any guid how to design an index based on the needs ?

or anyone wanto to share your experience ?

please help.

regards

eddhie

[Former Member]

In most of the cases, the creation of an index is not recommended because we must be very careful. An index can be created when there is no more solution. Each creation must be studied individually.

The Trace SQL tool permits to check the use of an index.

Reward helpful Answers...

Regards

[Former Member]

hi,

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. <b>The primary index is created automatically when the table is created in the database.</b>

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

<b>Points to keep in mind while creating indexes:</b>

1) The order of the fields in the index is very important for the accessing speed.

2) 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.

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

4) 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. 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.

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

6) 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 <Table name>~<Index ID>.

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

Regards,

Sailaja.

[Former Member]

hi Sailaja,

thanks for the sharing. btw i have another question about the points for creating index, especially : point no 3.

how can we know which fields will significantly restrict the set of results ?

another question about table index, currently i'm working in CRM modul.

in CRM there is table CRMD_ORDERADM_H and the primary key for this table is combination of field 'Client' & 'GUID'.

in this table, an index is created and the field used for indexing is field 'GUID'.

this condition is overlapping index, right ? is it ok for this index conditon or i need to drop this overlapping index ?

please advice

regards

eddhie

[Former Member]

In most of the cases, the creation of an index is not recommended because we must be very careful. An index can be created when there is no more solution. Each creation must be studied individually.

The Trace SQL tool permits to check the use of an index.

Reward helpful Answers...

Regards

[Former Member]

hi Tushar,

some of my partner also same the same thing, that index creation is not recommended.

in your reply you said that each creation must be studied individually. could you explain or guid me how to studied it so the index is the best solution for the worse case solution.

please help

regards

[Former Member]

hi,

There is no need to create further secondary indexes as you have mentioned that in you table CRMD_ORDERADM_H the primary key is 'Client' & 'GUID'. Primary index will be automatically created during the creation of table.

Regards,

Sailaja.

[Former Member]

hi Sailaja,

i'm agree with you that the secundary index for the primary key is not needed.

the problem is how i can analyze if the secundary indexes is droped, there won't be a performance problem ?

please help

regards

[Former Member]

hi,

We can force the select to execute with the specified secondary index. It can be done using HINTS statement. But HINTS vary from one backend to other backend.

Force your select to use a secondary index ( other than the one which you want to test) and compare the performance of the select with and without the secondary index.

Here is an example where oracle is the backend system.

please refer to Note 129385 in OSS.

SELECT carrid connid cityfrom

FROM spfli INTO (xcarrid, xconnid, xcityfrom)

WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

WRITE: / xcarrid, xconnid, xcityfrom.

ENDSELECT.

More than 4 fields should not be specified in secondary index. Creation of multiple secondary indexes increases the load on database.

Regards,

Sailaja.