topic Full Table Scan for ACT_HI_PROCINST in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/full-table-scan-for-act-hi-procinst/m-p/192177#M145307 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><BR /><SPAN>Following query is performing Full table scan on ACT_HI_PROCINST which impacting the performance of Application&nbsp; Can you&nbsp; suggest any index we can Create&nbsp; on SUPER_PROCESS_INSTANCE_ID_ column and also what kind of index creation will increases the performance of sql query execution ?</SPAN><BR /><BR /><SPAN>SQL Query :</SPAN><BR /><SPAN> </SPAN><BR /><SPAN>select * from ( select a.*, ROWNUM rnum from (</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select RES.*</SPAN><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; from ACT_HI_PROCINST RES</SPAN><BR /><BR /><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE&nbsp; RES.SUPER_PROCESS_INSTANCE_ID_ = :1</SPAN><BR /><BR /><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; order by RES.ID_ asc</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ) a where ROWNUM &lt; :2 ) where rnum&nbsp; &gt;= :3</SPAN><BR /><BR /><SPAN>Bind Variables :</SPAN><BR /><SPAN> 1 -&nbsp; (VARCHAR2(2000)):fe441ed4-0195-11e5-aaf8-001b21db7229</SPAN><BR /><SPAN> 2 -&nbsp; (NUMBER):2147483647</SPAN><BR /><SPAN> 3 -&nbsp; (NUMBER):1</SPAN><BR /><BR /><SPAN>Execution plan.</SPAN><BR /><BR /><SPAN>———–</SPAN><BR /><SPAN>Plan hash value: 545228885</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; | 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; 5 | 16730 | 84722&nbsp;&nbsp; (1)| 00:16:57 |</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; 5 | 16730 | 84722&nbsp;&nbsp; (1)| 00:16:57 |</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; |</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; 5 | 16665 | 84722&nbsp;&nbsp; (1)| 00:16:57 |</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; 5 |&nbsp; 1610 | 84722&nbsp;&nbsp; (1)| 00:16:57 |</SPAN><BR /><SPAN>|*&nbsp; 5 |&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL&nbsp;&nbsp; | ACT_HI_PROCINST |&nbsp;&nbsp;&nbsp;&nbsp; 5 |&nbsp; 1610 | 84721&nbsp;&nbsp; (1)| 00:16:57 |</SPAN><BR /><SPAN>——————————————————————————————–</SPAN><BR /><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 /></BODY></HTML> Mon, 25 May 2015 11:45:11 GMT sakumar1 2015-05-25T11:45:11Z Full Table Scan for ACT_HI_PROCINST https://connect.hyland.com/t5/alfresco-archive/full-table-scan-for-act-hi-procinst/m-p/192177#M145307 Hi,Following query is performing Full table scan on ACT_HI_PROCINST which impacting the performance of Application&nbsp; Can you&nbsp; suggest any index we can Create&nbsp; on SUPER_PROCESS_INSTANCE_ID_ column and also what kind of index creation will increases the performance of sql query execution ?SQL Query : s Mon, 25 May 2015 11:45:11 GMT https://connect.hyland.com/t5/alfresco-archive/full-table-scan-for-act-hi-procinst/m-p/192177#M145307 sakumar1 2015-05-25T11:45:11Z Re: Full Table Scan for ACT_HI_PROCINST https://connect.hyland.com/t5/alfresco-archive/full-table-scan-for-act-hi-procinst/m-p/192178#M145308 <HTML><HEAD></HEAD><BODY><SPAN>Indices are always a trade off between insert speed and query speed. If you need more performance for querying, feel free to add the appropriate indices that speed up your use cases.</SPAN></BODY></HTML> Tue, 02 Jun 2015 18:45:43 GMT https://connect.hyland.com/t5/alfresco-archive/full-table-scan-for-act-hi-procinst/m-p/192178#M145308 jbarrez 2015-06-02T18:45:43Z