Adding an Index to a System Entity.


We have an application that has been using BPT processes for about 4-5 years now. 

The records in the OSSYS_BPM_ACTIVITY (Activity Table) have drastically increased to about 4 million records and the queries we have been executing against that table are slower than we would like. 

We will be working on purging of old processes that are no longer required in the future which will delete the unnecessary records from the Activity table.

But in the short term, our DBA has created some indexes on the System tables to help with the query performance and I would like to get some clarification on possible problematic scenarios that may arise from adding the indexes on the System Entities directly in the Database.

  •  After the next application deployment(full solution publish) , will the DBA need to recreate these Indexes?

Your thoughts are much appreciated!

Yes, it could happen especially if you update your platform server. 

What I could suggest is to use the same trick that Entity Track Changes uses to create the triggers in the table you want to track the changes. The script creates a set of triggers that will be executed during the publication of a module. So, the idea is every time you publish any module, the trigger will execute the SQL to recreate the indexes.

But there are some limitations that you should consider (read the doc).

Hope it helps.