topic Full Table Scan of ACT_RU_JOB RES in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/full-table-scan-of-act-ru-job-res/m-p/209071#M162201 <HTML><HEAD></HEAD><BODY><SPAN>In our performance setup below query is making full table scan we see this could be the issue with index on.Can you please&nbsp; let us know we can create the composite&nbsp; index on table ACT_RU_JOB&nbsp; columns (EXCLUSIVE_,PROCESS_INSTANCE_ID_,RETRIES_)?</SPAN><BR /><BR /><SPAN>select * from ( select a.*, ROWNUM rnum from (&nbsp;&nbsp;&nbsp;&nbsp; select RES.*</SPAN><BR /><SPAN>from ACT_RU_JOB RES&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; where (RETRIES_ &gt; 0)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and (DUEDATE_ is</SPAN><BR /><SPAN>null or DUEDATE_ &lt; :1 )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and (LOCK_OWNER_ is null or</SPAN><BR /><SPAN>LOCK_EXP_TIME_ &lt; :2 )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and (EXCLUSIVE_ = 1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; and</SPAN><BR /><SPAN>(PROCESS_INSTANCE_ID_ = :3 )&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) a where ROWNUM &lt; :4 ) where rnum</SPAN><BR /><SPAN> &gt;= :5</SPAN><BR /><BR /><SPAN>Plan hash value: 1684977460</SPAN><BR /><BR /><SPAN>———————————————————————————-</SPAN><BR /><SPAN>| Id&nbsp; | Operation&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Name&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; 44 (100)|&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; |</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; 1 |&nbsp; 5440 |&nbsp;&nbsp;&nbsp; 44&nbsp;&nbsp; (0)| 00:00:01 |</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; |</SPAN><BR /><SPAN>|*&nbsp; 3 |&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL| ACT_RU_JOB |&nbsp;&nbsp;&nbsp;&nbsp; 1 |&nbsp; 5427 |&nbsp;&nbsp;&nbsp; 44&nbsp;&nbsp; (0)| 00:00:01 |</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;=:5)</SPAN><BR /><SPAN>&nbsp;&nbsp; 2 - filter(ROWNUM&lt;:4)</SPAN><BR /><SPAN>&nbsp;&nbsp; 3 - filter(("EXCLUSIVE_"=1 AND "RETRIES_"&gt;0 AND ("DUEDATE_" IS NULL OR</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "DUEDATE_"&lt;:1) AND ("LOCK_OWNER_" IS NULL OR "LOCK_EXP_TIME_"&lt;:2) AND</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "PROCESS_INSTANCE_ID_"=SYS_OP_C2C(:3)))</SPAN><BR /><BR /><SPAN>Regards,</SPAN><BR /><SPAN>Sakumar</SPAN><BR /><BR /></BODY></HTML> Wed, 25 Feb 2015 07:17:29 GMT sakumar1 2015-02-25T07:17:29Z Full Table Scan of ACT_RU_JOB RES https://connect.hyland.com/t5/alfresco-archive/full-table-scan-of-act-ru-job-res/m-p/209071#M162201 In our performance setup below query is making full table scan we see this could be the issue with index on.Can you please&nbsp; let us know we can create the composite&nbsp; index on table ACT_RU_JOB&nbsp; columns (EXCLUSIVE_,PROCESS_INSTANCE_ID_,RETRIES_)?select * from ( select a.*, ROWNUM rnum from (&nbsp;&nbsp;&nbsp;&nbsp; select Wed, 25 Feb 2015 07:17:29 GMT https://connect.hyland.com/t5/alfresco-archive/full-table-scan-of-act-ru-job-res/m-p/209071#M162201 sakumar1 2015-02-25T07:17:29Z Re: Full Table Scan of ACT_RU_JOB RES https://connect.hyland.com/t5/alfresco-archive/full-table-scan-of-act-ru-job-res/m-p/209072#M162202 <HTML><HEAD></HEAD><BODY><SPAN>You can add whatever indices you want. However, it surprises me RETRIES and EXCLUSIVE is part of your index. They typically are true/false and 1/2/3 … so not very good candidate for an index …</SPAN></BODY></HTML> Sat, 28 Feb 2015 09:28:32 GMT https://connect.hyland.com/t5/alfresco-archive/full-table-scan-of-act-ru-job-res/m-p/209072#M162202 jbarrez 2015-02-28T09:28:32Z