Table scan better than index usage?

[Former Member]

Hi@all,

I have a problem with the following query:

SELECT header."CHANGED_BY",header."CHANGED_DATE",header."CLOSED_HEAD",header."CREATED_BY",header."CREATED_DATE",header."CREATE_IND",header."

DELETE_IND",header."DOC_CAT_R3",header."DOC_GUID",header."DOC_KEY_R3",header."DOC_NUM",header."LOCAL_CLOSED_HEAD",header."MATERIAL",he

ader."ORDER_GUID",header."REQ_REF_KEY",header."REQ_TYPE",header."SERVICE",header."SYNCKEY_MMW",header."ORDERID",header."RESERV_NO",item."SERVICE",item."MATERIAL",item."PSYNCKEY",mattext."MATERIAL",mattext

."MATL_DESC",mattext."SPRAS",matkl."MATERIAL",matkl."MATL_GROUP",servicetxt."ASNUM",servicetxt."SHORT_TEXT",servicetxt."SPRAS",header."pers_ref_id"

FROM M48HEADER header,M49REQPOS item,M125TEXT mattext,M123MATERIAL matkl,M202TEXT servicetxt

WHERE ((((header."SYNCKEY_MMW" = item."PSYNCKEY") AND ((header."MATERIAL" = mattext."MATERIAL"(+)) AND (UPPER

(mattext."SPRAS") LIKE UPPER('de')ESCAPE'\') ) ) AND (header."MATERIAL" = matkl."MATERIAL"()) ) AND (header."SERVICE" = servicetxt."ASNUM"()) )

AND (UPPER(header."CREATED_BY") LIKE UPPER('%')ESCAPE'\')

The interesting Indexes are set on header.SYNCKEY_MMW and on item.PSYNCKEY. When I use EXPLAIN it says that the header-index is used but

the item-index is not used and thats my problem: why does it not use this index? This index is high selective. Maybe the reason for this strategy is that this query

fetch <u>all</u> entries of table "item". Am I right or is there another reason?

Please help me!

Thx and kind regards,

Frank

[lbreddemann]

HI Frank would you mind to give a little more information?

Please post the explain plan you got, the entries of optimizerstatistics for the tables in the query (M48HEADER, M49REQPOS, M125TEXT, M123MATERIAL, M202TEXT) and the exact index-layout.

Also the parmeters of the DB instance might be usefull and if you update the statistics before you get the data above it might even be better.

With the information you send - sorry no answer possible.

KR Lars