topic Re: Indexes in Application Development and Automation Discussions

Indexes

Former Member — Fri, 01 Sep 2006 06:35:51 GMT

How do we make use of indexes in a table?

Re: Indexes

Former Member — Fri, 01 Sep 2006 06:37:17 GMT

put ur Where Clause based on the Index Fields.

Regards

Prabhu

Re: Indexes

Former Member — Fri, 01 Sep 2006 06:37:59 GMT

Hi kantheri,

<b>1. SECONDARY INDEX.

They are generally used for faster access.</b>

EG.

In a table there are 10 fields.

1,2 are primary fields (primary index)

2. But the table is queried many times

on field number 6 (eg).

So we can create a NEW Index

(Secondary index)

only on that 6th field.

3. Due to this,

the sql will become faster

because NOW

the database will search on the

basis of secnodary index (made on 6th field)

regards,

amit m.

Re: Indexes

Former Member — Fri, 01 Sep 2006 06:39:15 GMT

Hello,

Indexes are used to speed up the search in a table.

you can create index for fields you pass it in the where clause.

Indexes will be failed in a select query with logical operators OR and NOT.

Reward if helps.

Thanks,

krishnakumar

Re: Indexes

Former Member — Fri, 01 Sep 2006 06:39:27 GMT

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.

Re: Indexes

dani_mn — Fri, 01 Sep 2006 06:41:51 GMT

If you need to access your table by means that are not part of the key, then an index on the those fields would help the speed of the select statement. Be warned, that addtional indexes could have a negative effect on the performance when doing database updates. In my experience, I have never really seen this occur, not enough to notice anyway.

For example, say you have a custom table like so.

MANDT Key Field

VBELN Key Field

POSNR

MATNR

Say that you need to access it like this.

Select * into corresponding fields of table itab

from ztable

where matnr in s_matnr.

In this case, you are trying to access the table by MATNR which is not part of the primary key. You can create an index to help.

MANDT Key Field

MATNR Key Field

The performace of the select statement above will be greatly increased. Especially over a large number of records.

Regards,

Re: Indexes

Former Member — Fri, 01 Sep 2006 06:43:11 GMT

The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

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

you can use the indexes with READ statement

READ TABLE tab1 INDEX sy-index INTO wa_tab.

Re: Indexes

Former Member — Fri, 01 Sep 2006 06:44:46 GMT

The indexes are used in the selects if the WHERE conditions are made in according with the indexes.

So if you have an index with following fields:

- FIELD1

- FIELD2

- FIELD3

It'll be used in a select if the WHERE condition is like:

SELECT * FROM <TABLE> WHERE FIELD1 =

AND FIELD2 =

AND FIELD3 = .

An index can partially used in select like this:

SELECT * FROM <TABLE> WHERE FIELD1 =

AND FIELD2 = .

Max

Re: Indexes

aris_hidalgo — Fri, 01 Sep 2006 06:46:48 GMT

Hi S Kantheri,

You can use indexes in the where clause of your select statements. For example, some of your fields in the where clause are not primary keys so what you can do is to check first in SE11 of a given table if it has an existing index that you can use. If not, you can create one.

For example, I need to use SHKZG, BLART and MONAT in the where clause of my select statement. These fields are not primary key fields of table BSIS. So using them without indexes will give you a poor performance.

Hope this helps...

P.S. Please award points for useful answers.