Full Table Scan of ACT_RU_JOB RES

[sakumar1]

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

[jbarrez]

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 …