AnsweredAssumed Answered

Full Table Scan Happening for the table ACT_HI_PROCINST

Question asked by sakumar1 on Dec 7, 2015
Latest reply on Dec 8, 2015 by jbarrez
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

Outcomes