topic Re: indexes in Application Development and Automation Discussions

indexes

Former Member — Wed, 26 Sep 2007 05:56:51 GMT

Hi,

Can anyone give me an example how to use indexes in select statement in where condition?

Regards,

Hema

Re: indexes

Former Member — Wed, 26 Sep 2007 06:01:18 GMT

HI,

see this example

DATA: BEGIN OF LINE,

COL1 TYPE I,

COL2 TYPE I,

END OF LINE.

DATA ITAB LIKE TABLE OF LINE.

DO 2 TIMES.

LINE-COL1 = SY-INDEX.

LINE-COL2 = SY-INDEX ** 2.

APPEND LINE TO ITAB.

ENDDO.

LINE-COL1 = 11. LINE-COL2 = 22.

INSERT LINE INTO ITAB INDEX 2.

INSERT INITIAL LINE INTO ITAB INDEX 1.

LOOP AT ITAB INTO LINE.

WRITE: / SY-TABIX, LINE-COL1, LINE-COL2.

ENDLOOP.

follow this link for more info.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3612358411d1829f0000e829fbfe/content.htm

rgds,

bharat.

Re: indexes

Former Member — Wed, 26 Sep 2007 06:01:32 GMT

good

go through this code

Examples:

%_HINTS ORACLE 'INDEX("&TABLE&"BTCV~I00")'

%_HINTS ORACLE 'ORDERED INDEX("&TABLE 1&","COVA~I03")'

ORACLE 'FIRST_ROWS'.

%_HINTS ORACLE 'FIRST_ROWS INDEX("&TABLE 2&",COVA~I03")'

ORACLE 'ORDERED'

table names are faked here; should give you the direction. Google for "oracle hints", specify in the WHERE clause as rhis:

SELECT * UP TO 10 ROWS FROM csks

WHERE kokrs <> space AND

kostl <> space

%_HINTS ORACLE 'index(csks"J")'.

thanks

mrutyun^

Re: indexes

Former Member — Wed, 26 Sep 2007 06:02:25 GMT

Here is your SELECT statement with INDEX usage -

SELECT bukrs hkont gjahr belnr buzei

monat dmbtr kostl prctr xref3 shkzg budat bldat

FROM bsis

INTO CORRESPONDING FIELDS OF TABLE itab

WHERE bukrs IN bukrs

AND hkont IN hkont

AND gjahr EQ gjahr

AND monat EQ monat

AND prctr IN prctr

AND kostl IN s_kostl

%_hints oracle 'INDEX("BSIS~ZXX")'.

Where ZXX is a custom index in BSIS. You can mention appropriate index name here.

Re: indexes

Former Member — Wed, 26 Sep 2007 06:03:17 GMT

Hi hema,

We cannot select records from database table using index.

We can select records by using upto n rows addition

Pls reward if useful.

Re: indexes

kiran_k8 — Wed, 26 Sep 2007 06:04:25 GMT

Hema,

Same as how we will mention the other fields in the where clause.The thing is that it is always advisable to use either primary key fields or index fields in the where clause of a select statement to enhance performance.

Suppose there is a table say bkpf.

if it having an indexes a,b,c.

we can use only fields from one index in the where clause.We can't take a field from index a and another field from index b at the same time in a where clause.

K.Kiran.

Re: indexes

paruchuri_nagesh — Wed, 26 Sep 2007 06:09:08 GMT

whenever u r using non primary key fields in where condition of a select staement u have to create secondary indexes for that fields in se11

optimizer will assign the indexes

user has nothing to do with that

just write a simple select statement using non primary key fields in where condition

without secondary indexes it wont fetch data once u craete index for particular fields of select statement it will fetch data

reward for use ful points

regards

Nagesh.Paruchuri

Re: indexes

Former Member — Wed, 26 Sep 2007 06:27:42 GMT

Hi,

Can I use indexes while joining 2 tables?

Re: indexes

Former Member — Wed, 26 Sep 2007 06:31:49 GMT

When a base table has multiple indices, the where clause should be in the order of the index, either a primary or a secondary index.

To choose an index, the optimizer checks the field names specified in the where clause and then uses an index that has the same order of the fields . In certain scenarios, it is advisable to check whether a new index can speed up the performance of a program. This will come handy in programs that access data from the finance tables.

indexes

INDEXES help to speed up selection from the database. The primary index is always created automatically in the SAP System. It consists of the primary key fields of the database table. If you cannot use the primary index to determine a selection result (for example, WHERE condition may not contain any primary index fields), you can create a secondary index.

Use Select Single if all primary key fields are supplied in the Where condition .

If all primary key fields are supplied in the Where condition you can even use Select Single. Select Single requires one communication with the database system, whereas Select-Endselect needs two.

To read data from several logically connected tables use a join instead of nested Select statements. Joins are preferred only if all the primary key are available in WHERE clause for the tables that are joined. If the primary keys are not provided in join the Joining of tables itself takes time.

SELECT * FROM EKKO INTO EKKO_WA.

SELECT * FROM EKAN INTO EKAN_WA

WHERE EBELN = EKKO_WA-EBELN.

ENDSELECT.

The above code can be much more optimized by the code written below.

SELECT P_{F1 P}F2 F_{F3 F}F4 INTO TABLE ITAB

FROM EKKO AS P INNER JOIN EKAN AS F

ON P_{EBELN = F}EBELN.

reward if usefull

Re: indexes

paruchuri_nagesh — Wed, 26 Sep 2007 06:33:01 GMT

hema

plz go through this

Indexes - What are they and how can I use them?

An index helps to speed up selection from the database. An index is a sorted copy of selected database table fields.

The primary index is always automatically created in an ABAP-based SAP system. It consists of the primary key fields of the database table. This means, for each combination of the index fields exists a maximum of one record in the table. This kind of index is called a UNIQUE index.

If the primary index cannot be used to determine selection result, (for example, the WHERE condition does not contain any primary index fields), the system searches the whole table. To prevent this, and determine the selection result by searching through a restricted number of database records, you can create a secondary index.

However, you should not define an index for all possible fields in the WHERE condition.

Creating a secondary index

You can use the transaction ABAP Dictionary Change → Indexes... → Create to create an index. To make the index unique, select UNIQUE. To specify the fields that will comprise the index, choose "Choose fields". Then save and activate the index.

When to create an index

It is worth creating a secondary index when:

You want to select table entries based on fields that are not contained in an index, and the response times are very slow.

The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on → Execute action → Trace off → List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.

The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.

The database table is accessed mainly for reading entries.

reward for useful replies

regards

Nagesh.Paruchuri