Index on table

[Former Member]

Hello All,

i am having one query. How i can find out that what is the current index on the table.Means to say if i am using database table .

How should i identify which is the current index on the table.

And one more. When working on user exit we can go for change sin code in two ways.

1. Either by CMOD Transaction or by going to se37 and then doing the changes in the exit.

But which is better option and why ?

Please help ??

Thanks

Anu

[Former Member]

Hello All,

i am having one query. How i can find out that what is the current index on the table.Means to say if i am using database table .

How should i identify which is the current index on the table.

And one more. When working on user exit we can go for change sin code in two ways.

1. Either by CMOD Transaction or by going to se37 and then doing the changes in the exit.

But which is better option and why ?

Please help ??

Thanks

Anu

[Former Member]

Hi,

There could be multiple indexes on a table at any given point of time. All the data as per the index is stored separately.

Goto SE11, open the table and click on INDEXES. It will display the number of Active indexes for the table.

Regarding , User exits, i think its an individual choice and nothing related to best option.

Best regards,

Prashant

[Former Member]

Hi

1- The index is beeing used depends on the WHERE conditions. The sequence of the fields in where conditions indicates which index has to be used.

For example:

Index 1:

FIELD1,

FIELD2,

FIELD3,

FIELD4.

Index 2:

FIELD3,

FIELD2.

If it writes:

SElECT * FROM <TABLE> WHERE FIELD3 =

AND FIELD2 =

It means it'll use the index-2

If it writes:

SElECT * FROM <TABLE> WHERE FIELD1 =

AND FIELD2 =

AND FIELD3 =

AND FIELD4 =

=> index 1.

2- I think it's better to used CMOD trx, because as well as writing the code in the exit, it has to create a project and it can do it only by CMOD.

After creating and activing the project it's the same to used CMOD or SE37 to update an exit.

Max

[Former Member]

To find out which index is being used by a particular select:

Open two sessions.

In the first session, go to transaction ST05 and activate the SQL trace.

In the other session, execute your SELECT statement. It's best if you can do this in debugging mode. Turn the trace on just before the SELECT.

Go back to the first session, deactivate the trace and then display it. You will likely find a number of traces for different tables. Find the one you are interested in. Put your cursor on the line with PREPARE or OPEN and press the EXPLAIN button at the top of the screen. The list will show you which index was used.

Note that the index used depends on the your WHERE clause. If this is dependent on SELECT-OPTIONS or RANGES, the index used may vary according to the contents of these tables.

Rob

[Former Member]

Hi ,

For better performance to retrive data from batabase tables we should have to use indexes. This is also one of the performance optimization tecnique on data base tables.

Two types of indexs.

1) Primary indexes

2) secondary indexes

<b>Primary indexes :</b>

The primary index is created automatically when the table is created in the database with key fields.

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.

<b>secondary indexes:</b>

Frequently accessed or for more performance we can create sencondary index

secondary indexs are two types

1) Unique

2) Non unique

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.

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.