Index not being used

[Former Member]

Hi All,

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 Member]

Hi,

Did you include the client (MANDT) field in the index?

regards, Joerg

[Former Member]

No I did not include the MANDT in the index, but do I really need to for secondary index ?

Because there is another

SELECT PERNR BEGDA MASSN FROM PA0000

INTO TABLE IT_DOJ_DOL_DETAILS

FOR ALL ENTRIES IN IT_EMPDETAILS

WHERE PERNR = IT_EMPDETAILS-PERNR

AND ( MASSN = '01' OR MASSN = '10' ).

which does not have MANDT in its secondary index but still used the secondary index as below :

SELECT "PERNR" ,"BEGDA" ,"MASSN" FROM "PA0000",( SELECT k0 = '005' ,k1 = '00000001' ,k2 = '01' ,k3 = '10' UNION ALL SELECT '005' , '00000003' , '01' , '10' UNION ALL SELECT '005' , '00000004' , '01' , '10' UNION ALL SELECT '005' , '00000007' , '01' , '10' UNION ALL SELECT '005' , '00000008' , '01' , '10' ) as q WHERE "MANDT" = q.k0 AND "PERNR" = q.k1 AND ( "MASSN" = q.k2 OR "MASSN" = q.k3 ) /* R3:ZHRR_ITL_01:1950 T:PA0000 */ /*Dynamic SQL statement -- no cursor, upto 0, conn. 0:1*/

SELECT

|--Nested Loops(Inner Join, OUTER REFERENCES:([Union1024], [Union1025], [Union1026]))

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

|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1037], [Expr1038], [Expr1039]))

|--Merge Interval

| |--Sort(TOP 2, ORDER BY:([Expr1040] DESC, [Expr1041] ASC, [Expr1037] ASC, [Expr1042] DESC))

| |--Compute Scalar(DEFINE:([Expr1040]=((4)&[Expr1039]) = (4) AND NULL = [Expr1037], [Expr1041]=(4)&[Expr1039], [Expr1042]=(16)&[Expr1039]))

| |--Concatenation

| |--Compute Scalar(DEFINE:([Union1025]=[Union1025], [Union1025]=[Union1025], [Expr1027]=(62)))

| | |--Constant Scan

| |--Compute Scalar(DEFINE:([Union1026]=[Union1026], [Union1026]=[Union1026], [Expr1030]=(62)))

| |--Constant Scan

|--Index Seek(OBJECT:([DEV].[dbo].[PA0000].[PA0000~ZHI]), SEEK:([DEV].[dbo].[PA0000].[PERNR]=[Union1024] AND [DEV].[dbo].[PA0000].[MASSN] > [Expr1037] AND [DEV].[dbo].[PA0000].[MASSN] < [Expr1038]), WHERE:([DEV].[dbo].[PA0000].[MANDT]='005' AND [DEV].[dbo].[PA0000].[PERNR]>='00000001' AND [DEV].[dbo].[PA0000].[PERNR]<='00000008') ORDERED FORWARD)

[Former Member]

The problem might arise because of using For ALL entries. In order to use the index, you may explicitly specify it in your select statement using Oracle hints.

Since release 4.5, you can provide optimizer hints for SELECT statements using the %_HINTS parameter that enables <b>emergency repairs</b> on the spot. Of course, this parameter only works for those database systems that support optimizer hints. Because optimizer hints are not covered by SQL standards, each database vendor is free to provide them. Note that parameters starting with "%_" are never published in the documentation and should be used with special care only in an emergency. <b>This introduction is very simplified; for a detailed description, please refer to Note 129385 in OSS.</b>

Using the %_HINTS parameter, the example becomes:

SELECT carrid connid cityfrom

FROM spfli INTO table itab_spfli

WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

[Former Member]

Thank you Richa,

But we are using SQLServer as back-end, and i dont think there are hints for SQLServer.