Need to restrict database index use to a single program

[Former Member]

I have created a secondary index on the FMIOI table that consists of the following fields:

mandt

grant_nbr

hkont

objnrz

refbn

Before adding mandt to the index, another program used the index w/o mandt. Because the 2nd progam used the new index, it ran for an excessively long time. Once I added mandt to the index the 2nd program no longer used the index. I would like to know if there is a way I can insure that other programs do not use the index above.

[Former Member · Accepted solution]

I am afraid there is no way to absolutely deny the use of an index for a given SQL. All we can do is to apply the latest optimizer fixes, set the recommended oracle parameters, gather database statistics and pray.

If you still have a program using the wrong index, then there are ways to manually change that:

- check the existing indexes, maybe you can slightly adapt one of them

- if it is a customer built program, then an index hint could be used, maybe the statement can be changed too

- if you have a skilled dba / database developer you can fake the database stats, to use the correct index

If you have a similar problems again, you could post it in SDN to get more specific help on it.

Best regards, Michael