which table index is used while actual running

[Madhurivs23]

Hi,

How do I know that which index is used by the sql query to know whether index is degrading the performance or not at all used.

rgds

Madhuri

[Former Member]

Hi

By default the Primary Index(with primary keys) is used

when you created a secondary index for the fields used in the where clause and when you are fetching data by using those fields then the secondary index is considered.

Primary index : Its the index which is automatically created for the PRIMARY KEY FIELD(S) of the table.

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. This kind of index is also known as UNIQUE.

Secondary index : Its created as and when required,

based upon other field(s) of the table,

on which search criteria is used in sqls.

Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column’s selectivity. Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.

Regards

Anji

[Former Member]

Hi madhuri

You can use SQL TRACE ( ST05 ) to identify that your query is using

which Index.

You can activate the trace and run your program in another session and

after completion of your program press display trace which will show the sql

statement.

Regards

Harish

[Madhurivs23]

yes,

But where can I see that for particular table, this particular index has been used.

I searched in each option but didn't get that

rgds,

Madhuri

[Former Member]

Hi,

by defalt in select statements PRIMARY INDEX will participate while fetching the data from d/b and SECONDORY INDEX will partcipate if filed used in WHERE condition which they defined in secodory index.

<b>NOTE :</b> select statement always consider ONLY ONE index whether it is PRIMARY OR SECONDARY and this depend on fileds which you will be going to use in WHERE condition.

<b>Reward with points if helpful.</b>

Regards,

Vijay.

[former_member194613]

I would not really say, that ther primary index is used by default, it is possible that no index is used at all (full table scan, which is better if more than 10% of the table come back)

Which index is used is determined by the total selectivity of the index (check index fields from the first whether they are in the WHERE condition with an equal, selectivity is 1/ (number of different values), see also postings above. Index with highest selectivity is used.

More about SQL trace can be found here:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

See last section about the explain, the explain tells you which index is used.

Siegfried