topic Full Table Scan Happening for the table ACT_HI_PROCINST in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/full-table-scan-happening-for-the-table-act-hi-procinst/m-p/218715#M171845 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><BR /><SPAN>We are using Activiti 5.17</SPAN><BR /><BR /><SPAN>Below Query causing full table scan of ACT_HI_PROCINST resulting in slowing down the performance.</SPAN><BR /><BR /><SPAN>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 &lt; :2 ) where rnum &gt;= :3</SPAN><BR /><BR /><SPAN>Can you please answer below Queries ?</SPAN><BR /><SPAN>1. In What Use Case this Query will run ?</SPAN><BR /><SPAN>2. Can we disable the use case ?</SPAN><BR /><SPAN>3. Is this is defect in Activiti, which requires proper index to be created ?</SPAN><BR /><SPAN>4. Can you suggest any index ?</SPAN><BR /><BR /><SPAN>Query Plan</SPAN><BR /><SPAN>———</SPAN><BR /><BR /><SPAN>1- Original</SPAN><BR /><SPAN>———–</SPAN><BR /><SPAN>Plan hash value: 2339466892</SPAN><BR /><BR /><SPAN>———————————————————————————————</SPAN><BR /><SPAN>| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Rows&nbsp; | Bytes | Cost (%CPU)| Time&nbsp;&nbsp;&nbsp;&nbsp; |</SPAN><BR /><SPAN>———————————————————————————————</SPAN><BR /><SPAN>|&nbsp;&nbsp; 0 | SELECT STATEMENT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 3 | 11580 | 68571&nbsp;&nbsp; (1)| 00:13:43 |</SPAN><BR /><SPAN>|*&nbsp; 1 |&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 3 | 11580 | 68571&nbsp;&nbsp; (1)| 00:13:43 |</SPAN><BR /><SPAN>|*&nbsp; 2 |&nbsp;&nbsp; COUNT STOPKEY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</SPAN><BR /><SPAN>|&nbsp;&nbsp; 3 |&nbsp;&nbsp;&nbsp; VIEW&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 3 | 11541 | 68571&nbsp;&nbsp; (1)| 00:13:43 |</SPAN><BR /><SPAN>|*&nbsp; 4 |&nbsp;&nbsp;&nbsp;&nbsp; SORT ORDER BY STOPKEY|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp; 1002 | 68571&nbsp;&nbsp; (1)| 00:13:43 |</SPAN><BR /><SPAN>|*&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL&nbsp;&nbsp; | ACT_HI_PROCINST_ |&nbsp;&nbsp;&nbsp;&nbsp; 3 |&nbsp; 1002 | 68570&nbsp;&nbsp; (1)| 00:13:43 |</SPAN><BR /><SPAN>———————————————————————————————</SPAN><BR /><BR /><SPAN>Predicate Information (identified by operation id):</SPAN><BR /><SPAN>—————————————————</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp; 1 - filter("RNUM"&gt;=:3)</SPAN><BR /><SPAN>&nbsp;&nbsp; 2 - filter(ROWNUM&lt;:2)</SPAN><BR /><SPAN>&nbsp;&nbsp; 4 - filter(ROWNUM&lt;:2)</SPAN><BR /><SPAN>&nbsp;&nbsp; 5 - filter("RES"."SUPER_PROCESS_INSTANCE_ID_"=SYS_OP_C2C(:1))</SPAN><BR /><BR /><SPAN>Regards,</SPAN><BR /><SPAN>Sakumar</SPAN><BR /><BR /></BODY></HTML> Mon, 07 Dec 2015 14:21:52 GMT sakumar1 2015-12-07T14:21:52Z Full Table Scan Happening for the table ACT_HI_PROCINST https://connect.hyland.com/t5/alfresco-archive/full-table-scan-happening-for-the-table-act-hi-procinst/m-p/218715#M171845 Hi,We are using Activiti 5.17Below 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 &lt; :2 ) Mon, 07 Dec 2015 14:21:52 GMT https://connect.hyland.com/t5/alfresco-archive/full-table-scan-happening-for-the-table-act-hi-procinst/m-p/218715#M171845 sakumar1 2015-12-07T14:21:52Z Re: Full Table Scan Happening for the table ACT_HI_PROCINST https://connect.hyland.com/t5/alfresco-archive/full-table-scan-happening-for-the-table-act-hi-procinst/m-p/218716#M171846 <HTML><HEAD></HEAD><BODY><SPAN>The only place where I could find this, is in the regular HistoricProcessInstanceQuery.</SPAN><BR /><SPAN>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.</SPAN></BODY></HTML> Tue, 08 Dec 2015 19:51:56 GMT https://connect.hyland.com/t5/alfresco-archive/full-table-scan-happening-for-the-table-act-hi-procinst/m-p/218716#M171846 jbarrez 2015-12-08T19:51:56Z