jbpm6 with Oracle database - where to create indexes?

[toja]

Hi all,

We use jbpm6 with oracle database and we would like to create some indexes on jbpm tables to improve the dababase performance. Is there any ddl script with recomended queries and instructions where to create indexes to achieve best performance?

The script located in 'jbpm-installer\db\ddl-scripts' doesn't contain any indexes, even for the foreign keys.

Any suggestions where the indexes should be created?

Thanks for help,

Tomek

[swiderski.maciej]

the most important index for oracle is:

CREATE INDEX IDX_EVENTTYPES ON EVENTTYPES (INSTANCEID);

it will prevent deadlocks with concurrent access to process instances mainly affecting completion of the process instance that removes any events attached to it.

When it comes to other indexes it depends on how you're going to utilize the system (like number of process instances, number of tasks, number of queries, type of queries, etc). Based on that you can fine tune your data base to be the most performant for the requirements you have.

Unfortunately there is no complete indexes that should be applied at the moment, as you noticed the DDLs do not come with indexes at all as they are auto generated by hibernate tools to create data base structure only.

HTH