When is a secondary index used (in select or where)

[Former Member]

HI All,

We are confused (and new to ABAP) on the various postings on SDN concerning this topic. Replies seem to vary.

We have the following select statement:

SELECT date1 FROM table2

WHERE material (from a stored internal table) = material-table2

AND order (from a stored internal table) = order-table2

AND delivery method (from a stored internal table) = specific value of table2.

For a second index to be created, would we create the index for only date1 (since it's the only field we are selecting from table 2) or an index with date1, material, order, delivery method or just what's in the WHERE clause of material, order and delivery method?

How can you be sure that an index will be used?

Thank you!

[ThomasZloch]

Suppose you want to find the phone number of Mr. Charlie Brown in the phone book of Los Angeles. Would you want that phone book to be sorted by last name or by phone number?

Thomas

[Former Member]

a simple rule would be index are picked based on WHERE clause.

don't just create index for one-of-scenario, just think usage of the index before creating one as this can slow down your INSERT/UPDATEs.

[ravi_lanjewar]

Hi,

How to create the secondary index is probaly on where condition depends.

Index take storage space, When you create index on table specified fields first check re-usability of index in all program.

Ttable open in se11 apply where used in program, clasess and function module. Depending on usability of table where condition you can create index on most posible used fields in where condition. After creating the index you can check where your program is using proper index or not in st05. If it is not access approprite index you can hardcode index in your select condition using hint%.

Rgds

Ravi Lanjewar

[former_member194613]

> We are confused (and new to ABAP)

seem you are very very new.

Your question is actually not ABAP indexes etc are SQL and can be found in many textbooks also on Wiki pages. ABAP uses SQL, but does not change much.

The use index is determined nearly only by the WHERE clause. You want to know the phone number of somebody and you know the name and the address. The Index uses the name and the address as a telephone book. The phone number comes from the table not the index! This is not the secondary index, but the primary index.

If you search in the internet for phone number, then you can use also a secondary index, i.e. you know only the complete address but not the name. A phone book would not help here.

Which index is used, if often very simple as in the example, but can become very difficult. That is done by a sophisticated program of the database, the optimizer. Check other resoucres for details.

Siegfried