AnsweredAssumed Answered

Full Table Scan of ACT_RU_JOB RES

Question asked by sakumar1 on Feb 25, 2015
Latest reply on Feb 28, 2015 by jbarrez
In our performance setup below query is making full table scan we see this could be the issue with index on.Can you please  let us know we can create the composite  index on table ACT_RU_JOB  columns (EXCLUSIVE_,PROCESS_INSTANCE_ID_,RETRIES_)?

select * from ( select a.*, ROWNUM rnum from (     select RES.*
from ACT_RU_JOB RES       where (RETRIES_ > 0)       and (DUEDATE_ is
null or DUEDATE_ < :1 )       and (LOCK_OWNER_ is null or
LOCK_EXP_TIME_ < :2 )       and (EXCLUSIVE_ = 1)       and
(PROCESS_INSTANCE_ID_ = :3 )         ) a where ROWNUM < :4 ) where rnum
>= :5

Plan hash value: 1684977460

———————————————————————————-
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
———————————————————————————-
|   0 | SELECT STATEMENT    |            |       |       |    44 (100)|          |
|*  1 |  VIEW               |            |     1 |  5440 |    44   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |            |       |       |            |          |
|*  3 |    TABLE ACCESS FULL| ACT_RU_JOB |     1 |  5427 |    44   (0)| 00:00:01 |
———————————————————————————-

Predicate Information (identified by operation id):
—————————————————

   1 - filter("RNUM">=:5)
   2 - filter(ROWNUM<:4)
   3 - filter(("EXCLUSIVE_"=1 AND "RETRIES_">0 AND ("DUEDATE_" IS NULL OR
              "DUEDATE_"<:1) AND ("LOCK_OWNER_" IS NULL OR "LOCK_EXP_TIME_"<:2) AND
              "PROCESS_INSTANCE_ID_"=SYS_OP_C2C(:3)))

Regards,
Sakumar

Outcomes