indexes

Former Member — Fri, 05 Oct 2007 10:43:58 GMT

Hi,

I am working on a report,where in I have to select the data from rbkp table.So I have coded a select statement which selects some fields from rbkp table using for all entries from gt_output(internal table).In the where conditons if I use key fields from rbkp table,values are not getting stored for these fields in gt_output table.In this case can I use indexes in where condition?if so,how can we know which is the suitable index to be used?

Regards,

Hema

Re: indexes

Former Member — Fri, 05 Oct 2007 10:48:04 GMT

Hi,

In Se11 do to RBKP table..... On the application toolbar u have a button Index...clk....u will see all the available indexes.....

if there r no indexes...u can create ur own....

Re: indexes

paruchuri_nagesh — Fri, 05 Oct 2007 10:50:33 GMT

user has nothing to do when assiging secondary indexes

abap optimizer take care of those things

if ther is no index for non primary key fields .fields which u r using in where condition of select statement u shoud create secondary indexes in the sequence as they appear in where condition

regards

nagesh.Paruchuri

Re: indexes

Former Member — Fri, 05 Oct 2007 10:51:53 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

******************

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.

******************

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.

Regards

Vasu

Re: indexes

Former Member — Fri, 05 Oct 2007 11:17:00 GMT

Hi,

How can we see both primary and secondary indexes of a table?

Re: indexes

Former Member — Fri, 05 Oct 2007 13:27:25 GMT

In SE11, choose your table and press the indexes button.

Rob

topic indexes in Application Development and Automation Discussions

indexes

Re: indexes

Re: indexes

Re: indexes

Re: indexes

Re: indexes