AnsweredAssumed Answered

Performance issue in query ot get row of ACT_HI_VARINST

Question asked by hsimon on Sep 18, 2014
Latest reply on Sep 18, 2014 by hsimon

I just registered to this forum since I have to support the activity engine by a customer. We found that the cost for following query is very high:

select * from ( select a.*, ROWNUM rnum from ( select RES.* from ACT_HI_VARINST RES WHERE RES.TASK_ID_ = ? order by RES.ID_ asc ) a where ROWNUM < ?) where rnum >= ?

The reason is the full table scan used by the oracle rdbms.

For tests, we created a temporary index like CREATE INDEX IDX_TASK_ID ON ACT_HI_VARINST (TASK_ID_) and the query path is now a range scan with much lesser cost.

Does anyone have the same trouble? I guess, the index should be created autoamtically while installing.