AnsweredAssumed Answered

Full Table Scan for ACT_HI_PROCINST

Question asked by sakumar1 on May 25, 2015
Latest reply on Jun 2, 2015 by jbarrez
Hi,

Following query is performing Full table scan on ACT_HI_PROCINST which impacting the performance of Application  Can you  suggest any index we can Create  on SUPER_PROCESS_INSTANCE_ID_ column and also what kind of index creation will increases the performance of sql query execution ?

SQL Query :

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

Bind Variables :
1 -  (VARCHAR2(2000)):fe441ed4-0195-11e5-aaf8-001b21db7229
2 -  (NUMBER):2147483647
3 -  (NUMBER):1

Execution plan.

———–
Plan hash value: 545228885

——————————————————————————————–
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
——————————————————————————————–
|   0 | SELECT STATEMENT         |                 |     5 | 16730 | 84722   (1)| 00:16:57 |
|*  1 |  VIEW                    |                 |     5 | 16730 | 84722   (1)| 00:16:57 |
|*  2 |   COUNT STOPKEY          |                 |       |       |            |          |
|   3 |    VIEW                  |                 |     5 | 16665 | 84722   (1)| 00:16:57 |
|*  4 |     SORT ORDER BY STOPKEY|                 |     5 |  1610 | 84722   (1)| 00:16:57 |
|*  5 |      TABLE ACCESS FULL   | ACT_HI_PROCINST |     5 |  1610 | 84721   (1)| 00:16:57 |
——————————————————————————————–


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