One index for two select queries.

[Former Member]

Hi,

I have to create index as current index don't satisfy my requirement and I need it to expedite the program execution.

I am planning to create index on BSIS with the below fields

bukrs

hkont

zuonr

belnr

bldat

prctr

Now I have two select queries.

1st query has the fields in where clause as bukrs hkont zuonr bldat and prctr.

2nd query has the fields in where clause as bukrs hkont belnr and bldat.

I would like to know whether this index will be useful for both the select queries? And will it really expedite the program execution ?

Thanks.

[Former Member]

Hi,

I have to create index as current index don't satisfy my requirement and I need it to expedite the program execution.

I am planning to create index on BSIS with the below fields

bukrs

hkont

zuonr

belnr

bldat

prctr

Now I have two select queries.

1st query has the fields in where clause as bukrs hkont zuonr bldat and prctr.

2nd query has the fields in where clause as bukrs hkont belnr and bldat.

I would like to know whether this index will be useful for both the select queries? And will it really expedite the program execution ?

Thanks.

[ThomasZloch]

BSIS primary key starts with BUKRS and HKONT. Normally HKONT is quite selective, unless you have single G/L accounts with millions of items.

Also, there is a standard secondary index starting with BUKRS and BELNR, which should support your 2nd query.

How do your actual where-conditions look like, EQ-operators (good) or IN-ranges (potentially bad)?

If you still need to create that index, you should only need the fields down to ZUONR, or if this isn't selective enough yet (selective = very many distinct values with somewhat even distribution), BELNR. The remaining ones BLDAT and PRCTR just take additional space but do not provide quicker access, because BELNR is very selective already.

If your dev or sandbox has sufficient data, why don't you try and compare the options?

Thomas

[Former Member]

Hi Thomas,

Many thanks for this explaination.

Actually my dev has limited data so can't try options.

Yes, I am using EQ operator only.

So I should go for index creation for the following fields, right ?

zuonr

belnr

bldat

prctr

[former_member182354]

Hi,

     Also note that Index do occupy some memory space and creating an index should be the last option after thinking over all performance improvement possibilities tried after thoroughly reviewing the entire code.

Raghav