index

[Former Member]

hi there,

secondary index is as follows..(in the same order)

MANDT, WERKS, MATNR, RSKZG

query is:

SELECT * INTO TABLE L_MDBPN_TAB FROM MDBPN

WHERE KDAUF IN I_KDAUF_RANGE

AND KDPOS IN I_KDAUF_RANGE

AND MATNR IN I_MATNR_RANGE

AND WERKS IN I_WERKS_RANGE

AND RSKZG = CHAR1.

the order in where clause is different from secondary index..

does it effect the performance of the querry

[Former Member]

hi there,

secondary index is as follows..(in the same order)

MANDT, WERKS, MATNR, RSKZG

query is:

SELECT * INTO TABLE L_MDBPN_TAB FROM MDBPN

WHERE KDAUF IN I_KDAUF_RANGE

AND KDPOS IN I_KDAUF_RANGE

AND MATNR IN I_MATNR_RANGE

AND WERKS IN I_WERKS_RANGE

AND RSKZG = CHAR1.

the order in where clause is different from secondary index..

does it effect the performance of the querry

[paruchuri_nagesh]

hi

when ur using non primary key fields in where clause u have to create secondary indexes to speed up data fetching indexes can be assigned by the system it self developer has nothing to do with that

but secondary indexes are mandatory when u r using non primary key fields in where clause

use sql tracer if u want to measure performance tcode is ST05

reward if u find useful

regards

Nagesh.Paruchuri

[Former Member]

HI

YOU NEED TO CREAT SECONDARY INDEXES

THE PROCEDURE IS AS FOLOWS

<b>reward if usefull</b>

<b>Primary and secondary indexes</b>

<u>Index:</u> Technical key of a database table.

<u>Primary index</u>: 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.

<u>Secondary index</u>: Additional indexes could be created considering the most frequently accessed dimensions of the table.

<u>An index can be used to speed up the selection of data records from a table.</u>

An index can be considered to be a copy of a database table reduced to certain fields. The data is stored in sorted form in this copy. This sorting permits fast access to the records of the table (for example using a binary search). Not all of the fields of the table are contained in the index. The index also contains a pointer from the index entry to the corresponding table entry to permit all the field contents to be read.

<u>When creating indexes, please note that:</u>

An index can only be used up to the last specified field in the selection! The fields which are specified in the WHERE clause for a large number of selections should be in the first position.

Only those fields whose values significantly restrict the amount of data are meaningful in an index.

When you change a data record of a table, you must adjust the index sorting. Tables whose contents are frequently changed therefore should not have too many indexes.

Make sure that the indexes on a table are as disjunctive as possible.

(That is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.)

The database optimizer decides which index on the table should be used by the database to access data records.

You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated

[Former Member]

HI

YOU NEED TO CREAT SECONDARY INDEXES

THE PROCEDURE IS AS FOLOWS

<b>reward if usefull</b>

<b>Primary and secondary indexes</b>

<u>Index:</u> Technical key of a database table.

<u>Primary index</u>: 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.

<u>Secondary index</u>: Additional indexes could be created considering the most frequently accessed dimensions of the table.

<u>An index can be used to speed up the selection of data records from a table.</u>

An index can be considered to be a copy of a database table reduced to certain fields. The data is stored in sorted form in this copy. This sorting permits fast access to the records of the table (for example using a binary search). Not all of the fields of the table are contained in the index. The index also contains a pointer from the index entry to the corresponding table entry to permit all the field contents to be read.

<u>When creating indexes, please note that:</u>

An index can only be used up to the last specified field in the selection! The fields which are specified in the WHERE clause for a large number of selections should be in the first position.

Only those fields whose values significantly restrict the amount of data are meaningful in an index.

When you change a data record of a table, you must adjust the index sorting. Tables whose contents are frequently changed therefore should not have too many indexes.

Make sure that the indexes on a table are as disjunctive as possible.

(That is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.)

The database optimizer decides which index on the table should be used by the database to access data records.

You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated

[Former Member]

Hi,

Definitely it cause the performance issue bec fields using in where condition is not in order as you created secondary index as well as some fields are not in secondary index are kdpos, kdauf.

you can try to create secondary index how ever you r going to use where condition if not you can sortit out the where condition like

SELECT *

INTO TABLE L_MDBPN_TAB

FROM MDBPN

WHERE WERKS IN I_WERKS_RANGE

AND MATNR IN I_MATNR_RANGE

AND RSKZG = CHAR1.

AND KDAUF IN I_KDAUF_RANGE

AND KDPOS IN I_KDAUF_RANGE.

The above statement is little bit better that your select statement... here i written fields sequence as in the sequence of secondary index, still it causes performance isuue bec kdauf and kdpos r not part of secondary index

<b>Reward with points if helpful.</b>

Regards,

Vijay

[Former Member]

> the order in where clause is different from secondary index..

no this is irrelevant

But you should check the SQL trace.And check also the details of the statement, which of the ranges is actually filled. An empty range will not help at all!

The Expalin of the SQL trace will tell you which index is used.

What are the fields of the primary key?

Siegfried

[Former Member]

The order is not important, but I think it's good programming practice to put them in the order if the index you expect to be used. It may help whoever comes along next to see how it's working.

rob

[Former Member]

> The order is not important, but I think it's good programming practice to put

> them in the order if the index you expect to be used. It may help whoever

> comes along next to see how it's working.

That is true. ... in normal statements.

But here we have ranges which probaly come from a selection screen. In this case it is important to check which ranges are actually filled and used. So here the order of the fields will probably follow the layout of the selection screen. There can be secondary indices supporting the different searches (usually not all possible combinations but the most important ones). So it can even be impossible to write a where condition which follows the order of all secondary keys.

Siegfried

[Former Member]

Siegfried - a lot of selects use ranges that are dynamically determined and even if they come from the selections screen, I would put them in the index order I would expect to be used.

It's not always clear which index will actually be used by the database. Putting them in the order I expect helps me if I see something else in the explain.

But It's more a matter of style. You put it in selection screen order and I'll put it in index order (and I suspect that a lot of people just won't care - but that's a different story).

Rob