Full Table Scan Happening for the table ACT_HI_PROCINST

[sakumar1]

Hi,

We are using Activiti 5.17

Below Query causing full table scan of ACT_HI_PROCINST resulting in slowing down the performance.

select * from ( select a.*, ROWNUM rnum from ( select RES.* from ACT_HI_PROCINST RES WHERE RES.SUPER_PROCESS_INSTANCE_ID_ = :1 order by RES.ID_ asc ) a where ROWNUM < :2 ) where rnum >= :3

Can you please answer below Queries ?
1. In What Use Case this Query will run ?
2. Can we disable the use case ?
3. Is this is defect in Activiti, which requires proper index to be created ?
4. Can you suggest any index ?

Query Plan
———

1- Original
———–
Plan hash value: 2339466892

———————————————————————————————
| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————————
|   0 | SELECT STATEMENT         |                  |     3 | 11580 | 68571   (1)| 00:13:43 |
|*  1 |  VIEW                    |                  |     3 | 11580 | 68571   (1)| 00:13:43 |
|*  2 |   COUNT STOPKEY          |                  |       |       |            |          |
|   3 |    VIEW                  |                  |     3 | 11541 | 68571   (1)| 00:13:43 |
|*  4 |     SORT ORDER BY STOPKEY|                  |     3 |  1002 | 68571   (1)| 00:13:43 |
|*  5 |      TABLE ACCESS FULL   | ACT_HI_PROCINST_ |     3 |  1002 | 68570   (1)| 00:13:43 |
———————————————————————————————

Predicate Information (identified by operation id):
—————————————————

   1 - filter("RNUM">=:3)
   2 - filter(ROWNUM<:2)
   4 - filter(ROWNUM<:2)
   5 - filter("RES"."SUPER_PROCESS_INSTANCE_ID_"=SYS_OP_C2C(:1))

Regards,
Sakumar

[jbarrez]

The only place where I could find this, is in the regular HistoricProcessInstanceQuery.
The history tables have by default no indices applied (for insert speed), so adding one on SUPER_PROCESS_INSTANCE_ID_ might solve it in this case.