No first field of table index in where condition

[vijay_kumar134]

Hi

I have got the following error when i am running code inspector,

"Large table STPO: No first field of a table index in WHERE Condition"

My code is like below:

SELECT stlnr  idnrk 

FROM stpo

INTO TABLE  git_stpo

FOR ALL ENTRIES IN git_mast

WHERE stlnr = git_mast-stlnr.

Please guid me how to remove this error and how to use index in where clause?

Thanks.

Vijay.

[Former Member]

Hi

select stlnr idnrk from stpo into table git_stpo %_hints ORACLE 'INDEX("STPO" "STPO~A")' WHERE FIELD = <cond>...

Regards

Sabyasachi

[former_member184455]

Hi,

Why should it help to tell the database to use index STPO~A (fields MANDT + AENNR) when the WHERE-clause contains the field STLNR?

Regards, Randolf

[former_member192616]

Hi Sabyasachi,

not only your advice is incorrect, the syntax is it as well.

Forcing the optimizer to use the index on MANDT and AENNR will

not influence the SCI message nor will it improve the run time. Most

likely run time will be worse!

Kind regards,

Hermann

[Former Member]

Hi Vijay,

STPO  is large table, so you have to put 1st key field (STLTY) in where condition to remove code inspector error,  please refer the code.

SELECT stlnr  idnrk

FROM stpo

INTO TABLE  git_stpo

FOR ALL ENTRIES IN git_mast

WHERE  STLTY  = 'E'  and             

                stlnr = git_mast-stlnr.

Regards,

Deepak

[former_member184455]

Hi,

Yes, that's correct, and this what also the check documentation says: "Check whether you can formulate the WHERE condition to suit a table index."

The first fields of the primary key of table STPO are: MANDT, STLTY, STLNR

Some database platforms might be able to add the missing (non-selective) field STLTY and do an index skip scan, but maybe not always. Therefore the  left-justified fields of an index have to be in the WHERE-clause - that is at least the first field of an index.

If there is no information about the 'BOM category' STLTY, it would even be better to put all possible values into an IN-clause:

SELECT stlnr  idnrk

FROM stpo

INTO TABLE  git_stpo

FOR ALL ENTRIES IN git_mast

WHERE  STLTY IN ('D', 'E', 'K', 'M')  and             

                stlnr = git_mast-stlnr.

Best Regards,

Randolf

[vijay_kumar134]

Thank you Deepak and Radolf.

My issue has been solved.

Thank you for everyone.