secondary index

[madan_ullasa]

hi frnds,

i want to know how we use the secondary index in select statements.

Regards,

Madan.

[former_member181962]

Hi Madan,

I don't think there is any change in the way we write the select statements if we use secondary index.

We write the select statements just as we do normally.

Regards,

Ravi

[Former Member]

hi madan,

1.If you cannot use the primary index to determine the result set because, for example, none of the primary index fields occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

2.You specify the fields of secondary indexes using the ABAP Dictionary. You can also determine whether the index is unique or not. However, you should not create secondary indexes to cover all possible combinations of fields.

3.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.

4.If all of the columns in the SELECT clause are contained in the index, the system does not have to search the actual table data after reading from the index. If you have a SELECT clause with very few columns, you can improve performance dramatically by including these columns in a secondary index

<b>reward if useful..</b>

Message was edited by: Ashok Kumar Prithiviraj

[Former Member]

Hai Madan,

Here is an example code..

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.

http://help.sap.com/saphelp_47x200/helpdata/en/cf/21eb2d446011d189700000e8322d00/content.htm

Regards,

Srikanth.

[Former Member]

Hi madan,

I think the way u write select for secondary index is same as primary index.

[Former Member]

Hello Madan,

What database are you using? I know with Oracle you can provide a hint to the optimizer with regards to which index to use. Search google with the following keywords:

abap select oracle hint index

There are some articles referring to this.

Cheers,

Martin

[vinod_gunaware2]

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.

If you cannot use the primary index to determine the result set because, for example, none of the primary index fields occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

You specify the fields of secondary indexes using the ABAP Dictionary. You can also determine whether the index is unique or not. However, you should not create secondary indexes to cover all possible combinations of fields.

Only create one if you select data by fields that are not contained in another index, and the performance is very poor. Furthermore, you should only create secondary indexes for database tables from which you mainly read, since indexes have to be updated each time the database table is changed. As a rule, secondary indexes should not contain more than four fields, and you should not have more than five indexes for a single database table.

If a table has more than five indexes, you run the risk of the optimizer choosing the wrong one for a particular operation. For this reason, you should avoid indexes with overlapping contents.

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.

If all of the columns in the SELECT clause are contained in the index, the system does not have to search the actual table data after reading from the index. If you have a SELECT clause with very few columns, you can improve performance dramatically by including these columns in a secondary index.

regards

vinod

[Former Member]

hi

please go through the following link

<a href="http://cma.zdnet.com/book/abap/ch05/ch05.htm">Secondary Index</a>

regards

anoop