Bug 1279532 - [GSS](6.2.z)Missing database indexes - I18NText.Notification_Names_Id, BAMTaskSummary.OPTLOCK
[GSS](6.2.z)Missing database indexes - I18NText.Notification_Names_Id, BAMTas...
Status: VERIFIED
Product: JBoss BPMS Platform 6
Classification: JBoss
Component: jBPM Core (Show other bugs)
6.2.0
Unspecified Unspecified
high Severity high
: CR1
: 6.2.1
Assigned To: Kris Verlaenen
Tibor Zimanyi
:
Depends On:
Blocks: 1288023 1295498
  Show dependency treegraph
 
Reported: 2015-11-09 11:33 EST by Tibor Zimanyi
Modified: 2016-01-21 04:25 EST (History)
6 users (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
: 1295498 (view as bug list)
Environment:
Last Closed:
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Tibor Zimanyi 2015-11-09 11:33:31 EST
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
Comment 1 Kris Verlaenen 2016-01-05 08:15:26 EST
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.
Comment 2 Kris Verlaenen 2016-01-05 08:54:13 EST
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
Comment 3 Hisao Furuichi 2016-01-05 20:02:19 EST
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
Comment 4 Kris Verlaenen 2016-01-07 08:58:00 EST
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.
Comment 5 Hisao Furuichi 2016-01-07 19:29:49 EST
Hi, Kris.
I understand and agree with you.
I'll write a knowledge article about OPTLOCK columns.

Thank you
Comment 6 Radovan Synek 2016-01-21 04:25:49 EST
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.

Note You need to log in before you can comment on or make changes to this bug.