It is sometimes questioned by consultants whether key fields order is important in standard DSOs. Practice shows that SELECT statements from standard DSO active tables use primary key database table indexes only in cases when first few fields from the key field list are used in the WHERE clause. In the following example SELECT statement populates internal table cond with relevant records (conditions) from the DSO O_EKBZM (table /BIC/AO_EKBZM00). In case source DSO contains millions of records SQL statement below may perform poorly:
SELECT * FROM /BIC/AO_EKBZM00 INTO TABLE cond
WHERE CALMONTH = <RESULT_FIELDS>-CALMONTH AND
MATERIAL = <RESULT_FIELDS>-/BIC/CHARTICLE AND
VENDOR = <RESULT_FIELDS>-VENDOR AND
PLANT = <RESULT_FIELDS>-PLANT AND
/BIC/CHPSTMON = <RESULT_FIELDS>-/BIC/CHPSTMON AND
STOR_LOC = <RESULT_FIELDS>-STOR_LOC AND
RT_VENDP = <RESULT_FIELDS>-RT_VENDP AND
PUR_GROUP = <RESULT_FIELDS>-PUR_GROUP.
Performance very much depends on how DSO O_EKBZM is defined. In the SELECT statement example WHERE clause does not contain the full key from the DSO:
Therefore, if the SELECT statement uses first few fields from the key field list for selections primary key database index is applied. Performance of the SELECT statement is very good.
On the other hand if the same key fields were defined in the DSO in a different order the primary key database index would not have been used. The same SELECT statement’s performance is poor on high data volumes. See example, where “Condition Type” field is placed in between the other key fields:
