How an INDEX of a Table got selected when a SELECT query hits the Database

[Former Member]

Hi All,

How an Index got selected when a SELECT query hits the Database Table.

My SELECT query is as ahown below.

SELECT ebeln ebelp matnr FROM ekpo

APPENDING TABLE i_ebeln

FOR ALL ENTRIES IN i_mara_01

WHERE werks = p_werks AND

matnr = i_mara_01-matnr AND

bstyp EQ 'F' AND

loekz IN (' ' , 'S') AND

elikz = ' ' AND

ebeln IN s_ebeln AND

pstyp IN ('0' , '3') AND

knttp = ' ' AND

ko_prctr IN r_prctr AND

retpo = ''.

The fields in the INDEX of the Table EKPO should be in the same sequence as in the WHERE clasuse?

Regards,

Viji

[Former Member]

Hi,

The INDEX of a table is selected internaly by DATABASE optimiser as per the fields you provide in the WHERE clause of your SELECT query.

Regards,

Anirban

[Former Member]

Hi,

You minimize the size of the result set by using the WHERE and HAVING clauses. To increase the efficiency of these clauses, you should formulate them to fit with the database table indexes.

Database Indexes

Indexes speed up data selection from the database. They consist of selected fields of a table, of which a copy is then made in sorted order. If you specify the index fields correctly in a condition in the WHERE or HAVING clause, the system only searches part of the index (index range scan).

The primary index is always created automatically in the R/3 System. It consists of the primary key fields of the database table. This means that for each combination of fields in the index, there is a maximum of one line in the table. This kind of index is also known as UNIQUE. If you cannot use the primary index to determine the result set because, for example, none of the primary index fields occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

reference : help.sap.com

thanx.

[Former Member]

Thanks alot..