Performance issue in query ot get row of ACT_HI_VARINST

[dharmeshyadav]

Below query has wrong index - 

SELECT *

FROM (

              SELECT a.*

                             ,ROWNUM rnum

              FROM (

                             SELECT 

                                               RES.*

                             FROM ACT_HI_VARINST RES

                             WHERE RES.PROC_INST_ID_ = : 1

                                           AND RES.NAME_ = : 2

                             ORDER BY RES.ID_ ASC

                             ) a

              WHERE ROWNUM < : 3

              )

WHERE rnum >= : 4

To enforce the index, somehow can we add below hint ?

/*+ index(RES, ACT_IDX_HI_PROCVAR_PROC_INST) */

[ryandawson]

You're right this was changed in MyBatis Multi-db vendor support + other simple tweaks · Activiti/Activiti@d5ce081 · GitHub 

So in v6+ of the engine the database-specific parts of queries are handled by those variables like limitBefore that come from the properties file.

In the latest version of Activiti 5 the old approach is still used where db-specific statements are added Activiti/DbSqlSessionFactory.java at 5.x · Activiti/Activiti · GitHub  and they can be used in the xml e.g. Activiti/ProcessDefinition.xml at 5.x · Activiti/Activiti · GitHub 

So for v5 it would be possible to add a db-specific query to Activiti/HistoricVariableInstance.xml at 5.x · Activiti/Activiti · GitHub  and reference it in DbSqlSessionFactory.

For newer versions of the engine to add the oracle-specific hint it would be necessary to define a new variable like limitBefore can come from oracle.properties and register it in ProcessEngineConfigurationImpl.