Index not being used

[Former Member]

there is a secondary index on the table PA9001 on PERNR, BEGDA and ENDDA,

but the below statement is not using the same.

SELECT PERNR ZROLE_CD FROM PA9001

INTO TABLE IT_ROLECD

FOR ALL ENTRIES IN IT_EMPDETAILS

WHERE PERNR = IT_EMPDETAILS-PERNR

AND BEGDA <= P_DATE

AND ENDDA >= P_DATE.

the below statement is what i could see in ST05

SELECT "PERNR" ,"ZROLE_CD" FROM "PA9001",( SELECT k0 = '005' ,k1 = '00000001' UNION ALL SELECT '005' , '00000003' UNION ALL SELECT '005' , '00000004' UNION ALL SELECT '005' , '00000007' UNION ALL SELECT '005' , '00000008' ) as q WHERE "MANDT" = q.k0 AND "PERNR" = q.k1 /* R3:ZHRR_ITL_01:1973 T:PA9001 */ /*Dynamic SQL statement -- no cursor, upto 0, conn. 0:1*/

SELECT

|--Nested Loops(Inner Join, OUTER REFERENCES:([Union1014]))

|--Constant Scan(VALUES:(('00000001'),('00000003'),('00000004'),('00000007'),('00000008')))

|--Clustered Index Seek(OBJECT:([DEV].[dbo].[PA9001].[PA9001~0]), SEEK:([DEV].[dbo].[PA9001].[MANDT]='005' AND [DEV].[dbo].[PA9001].[PERNR]=[Union1014]), WHERE:([DEV].[dbo].[PA9001].[PERNR]>='00000001' AND [DEV].[dbo].[PA9001].[PERNR]<='00000008') ORDERED FORWARD)

any ideas, how to make this use the index or why the table index is not being used.

Thanks in Advance,

Mohan

[former_member194613]

what is the primary key on PA9001 ?

It seems to use PA9001~0, because there is also PERNR and the BEGDA and ENDDA are not of much use because of the smaller equal and larger equal.

Check the view (summarize by SQL statement in the SQL Trace).

What is the total duration and the minimal time/record for your statement?

Siegfried

[Former Member]

Thank you Siegfried,

Could you give some more explanation, the primary key on PA9001 is there on multiple fields including the below three fields, and the index depth of the Clustred key is '0' and that of the secondary index that i created is '2'.

Does that mean, the clustered index is more effective than the secondary index that i created?

Regards,

Mohan