Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 1279532

Summary:	[GSS](6.2.z)Missing database indexes - I18NText.Notification_Names_Id, BAMTaskSummary.OPTLOCK
Product:	[Retired] JBoss BPMS Platform 6	Reporter:	Tibor Zimanyi <tzimanyi>
Component:	jBPM Core	Assignee:	Kris Verlaenen <kverlaen>
Status:	CLOSED EOL	QA Contact:	Tibor Zimanyi <tzimanyi>
Severity:	high	Docs Contact:
Priority:	high
Version:	6.2.0	CC:	alazarot, hfuruich, ksuzumur, kverlaen, lpetrovi, rsynek
Target Milestone:	CR1
Target Release:	6.2.1
Hardware:	Unspecified
OS:	Unspecified
Whiteboard:
Fixed In Version:		Doc Type:	Bug Fix
Doc Text:		Story Points:	---
Clone Of:
Clones:	1295498 (view as bug list)		Environment:
Last Closed:	2020-03-27 19:03:15 UTC	Type:	Bug
Regression:	---	Mount Type:	---
Documentation:	---	CRM:
Verified Versions:		Category:	---
oVirt Team:	---	RHEL 7.3 requirements from Atomic Host:
Cloudforms Team:	---	Target Upstream Version:
Embargoed:
Bug Depends On:
Bug Blocks:	1288023, 1295498

Description Tibor Zimanyi 2015-11-09 16:33:31 UTC

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 13:15:26 UTC

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 13:54:13 UTC

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-06 01:02:19 UTC

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 13:58:00 UTC

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-08 00:29:49 UTC

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 09:25:49 UTC

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.