regarding indexes

[Former Member]

Hi.....

good evening....

how to create secondary index and how to use that index in our select statement?

thanks and regards,

k.swaminath,

[Former Member]

Hi.....

good evening....

how to create secondary index and how to use that index in our select statement?

thanks and regards,

k.swaminath,

[Former Member]

Hi,

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.

CREATION of secondary index

go to SE11

enter table name

press display

from menu GOTO-> indexes

press create button

enter the fields for which you need the index

save and activate

use those fields in the Where condition of the select statement and see

regards,

Omkar.

[Former Member]

CREATION of secondary index

go to SE11

enter table name

press display

from menu GOTO-> indexes

press create button

enter the fields for which you need the index

save and activate

use those fields in the Where condition of the select statement and see

<b>SPFLI is the DDIC table and its secondary index is SPFLI~001</b>

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.

[Former Member]

You can create further indexes on a table in the ABAP Dictionary. These are called secondary indexes.

PURPOSE:

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.

Generally it is used when field which u want is not a primary field .

Advatange:

1. faster access to data when field is not a primary key field.

Disadvatnage:

1. Slower down database table inset, update delete commands.

Few secondary indexes, don't create any problem,

in fact they are very good,

if we use those fields in search queryes, often.

If there are many secondary indexes,

the only overhead (very little overhead)

is felt by the database,

when ever it INSERTS a new record , in the table.

(bcos it has to update the index also , all indexes)

If we use secondary index it takes more time to fetch data than using primary index.

Secondary indexes are used to enhancement performance when retrieving data. For example, say you have a table AFKO where the key to the table is AUFNR. You can select data from this table using AUFNR, the performance will be great because you are using the key. Say you need to hit this table with GLTRP(finish date), it is not part of the key, so the performance would be poor. If you created a secondard index using this field GLTRP, the performance would be improved.

http://help.sap.com/saphelp_470/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm

regards,

prabhu

reward if it is helpful.

[Former Member]

CREATION of secondary index

go to SE11

enter table name

press display

from menu GOTO-> indexes

press create button

enter the fields for which you need the index

save and activate

use those fields in the Where condition of the select statement and database in background automatically use index which is you created.

This is general rule of any database system.

[Former Member]

Hi Swami,

Creating Secondary Indexes can be done in SE11 Transaction (Dictionary: Display Table).

1. Go to SE11 Tcode.

2. Give any Z*Table

3. Select "Index" button

4. Here you can create Secondary Indexes to the table.

5. A 3 character name should be given to the Secondary Index and select the fields thru which you want to access the Database.

After creating the Secondary Index, you can use them like any other field in your WHERE clause.

<b>Reward Points if useful</b>

Harish