普元 BPS 6.7 有条查询WF_H_WIParticipant表的SQL语句占用资源较高

【问题】

BPS 6.7 使用JNDI数据源，发现如下SQL被调用时，占用资源非常高：

SELECT WF_H_WIParticipant.WIParticID AS WIParticID,

WF_H_WIParticipant.workItemID AS workItemID,

WF_H_WIParticipant.participantType AS participantType,

WF_H_WIParticipant.participantID AS participantID,

WF_H_WIParticipant.participantName AS participantName,

WF_H_WIParticipant.partiInType AS partiInType,

WF_H_WIParticipant.delegateType AS delegateType,

WF_H_WIParticipant.participantIndex AS participantIndex,

WF_H_WIParticipant.globalID AS globalID,

WF_H_WIParticipant.catalogUUID AS catalogUUID,

WF_H_WIParticipant.catalogName AS catalogName,

WF_H_WIParticipant.dealOpinion AS dealOpinion,

WF_H_WIParticipant.dealResult AS dealResult,

WF_H_WIParticipant.tenant_id AS tenant_id

FROM WF_H_WIParticipant

WHERE (WF_H_WIParticipant.workItemID = ?)

ORDER BY WF_H_WIParticipant.WIParticID DESC fetch first 2147483646 rows only WITH UR

实际WF_H_WIParticipant并没有2147483646那么多数据量，该值是Integer类型数据的最大值。底层动态传递的查询范围出问题或者为空时才会如此，正常情况下是不会这样的。

项目中解决办法：在workitemid字段上新增索引解决了；

现有其他索引如下图，供参考：