Index on FEBEP - Negative impacts?

[Former Member]

Hi All,

After a performance analysis, we found that 80% of the database time was for the table FEBEP for one of our application. We have decided now to go forward for a secondary index on this table, since the application that we are talking about, is used constantly by multiple users on a daily basis. And the result of the addition of index has brought in quite considerable reduction in the time taken. (QA Test result )

Please let me know if creating this index would have a negative impact on any other transactions. I have searched for notes on this - couldn't find relevant ones, nor the where used list of the table is hinting any major issues. Please let me know if there are any transactions which I need to be watchful of, after creating this index. Or if at all any one has come across any problem after creating an index on FEBEP. Feedback much appreciated, and Thanks in advance.

[PankajJain777]

Hi Soumya,

There is no problem in creating secondary index, as you are creating it for 'WHERE' clause in your query it will help in speeding query execution without effecting any other process or transaction.

Regards,

Pankaj

[Former Member]

Thanks for the prompt reply, Pankaj.

The secondary indexes of the table does affect the insert and modify operations on it. So what I wanted to know, is that, the inserts by SAP standard transactions on this table, ( possibly from a standard batch job.. ) does not have a considerable increase in execution time. Please let me know if you are aware of any such jobs / report?

[Former Member]

Hi Soumya,

Can you please provide your SQL statement/query, so that I can suggest you how to proceed. And also I need some more information from you.

1) How many secondary indices for the table FEBEP?

2) What is the table size?

3) How many fields you wanted to create secondary index.

Pros:

Will be faster execution

Cons:

Each and every DML of the table FEBEP will be synchronized with the index so may take additional milliseconds.

Regards,

Vadamalai A.

[ThomasZloch]

I can only give general recommendations.

Since you describe a large benefit of the new index, the additional, marginal system load to maintain the index should be taken into account.

Have your system admin monitor the load for a while after the import in production.

Make sure the fields of your new index are not similar to already existing indexes for the table, in rare cases this might lead the CBO to choose not the optimal one for a query.

Thomas

[Former Member]

Yes, will ask the admin to monitor the load. Thanks for the valid suggestion.

There are no other secondary indices on the table, and the fields chosen for the secondary index does not have anything in common with the Primary index either.

I'm keeping the thread open for some more time, in case some one has any specific issue related to this.