Description of problem: Hisao wrote in [1] that there are still missing database indexes for columns I18NText.Notification_Names_Id and BAMTaskSummary.OPTLOCK. Index for column I18NText.Notification_Names_Id should be probably there (for all DB systems, not only Oracle), because it will be consistent with currently indexed columns from that table. However we don't have any OPTLOCK column indexed now and I don't know how it will behave in terms of performance if we create such index. Because of this and because of that 6.2 release is very close, I'm creating this new BZ so it can be also decided if this should be part of 6.2.0 or some later release. Version-Release number of selected component (if applicable): 6.2.0.ER5 Additional info: [1] https://bugzilla.redhat.com/show_bug.cgi?id=1049238
Is there any indication why BAMTaskSummary.OPTLOCK should be indexed? Afaik, this is a version field that is checked on commit when using optimistic locking, not sure why adding an index there would be recommended? Also, we don't have indexes on any of the other OPTLOCK columns for other entities either.
Added index for I18NText.Notification_Names_Id master: https://github.com/droolsjbpm/jbpm/commit/f0a9fa7d6 6.3.x: http://github.com/droolsjbpm/jbpm/commit/e45daad3a
Hello, Kris. Our official document[1] guides "In order to get good response times while querying the database the following JBoss BPM Suite tables need to be indexed: processinstancelog and bamtasksummary. Note that ALL the columns in these two tables need to be indexed and not just the primary and foreign keys." Since BAMTaskSummary.OPTLOCK is not FK and the last comment from Kris, we would not need to add index for it. May I open a document bug ticket for this? Thx [1] https://access.redhat.com/documentation/en-US/Red_Hat_JBoss_BPM_Suite/6.2/html/Administration_And_Configuration_Guide/Indexing_Foreign_Keys.html
Hisao, Correct, the documentation is correct although not 100% precise imho, as all _queryable_ columns should be indexed, not just the keys. The OPTLOCK column however is not used in queries, so afaik has no need for an index either. I'm therefore setting this BZ to MODIFIED, the missing index for I18NText.Notification_Names_Id was added, the index for OPTLOCK columns was not added as we believe it's not required.
Hi, Kris. I understand and agree with you. I'll write a knowledge article about OPTLOCK columns. Thank you
Verified all the DDL scripts contains index definition for a column I18NText.Notification_Names_Id. This however does not apply to Sybase DB, as there are no indexes defined yet bug 1300598.