This query came to me as its taking more time return result and cost is also high.
Ex:
SELECT id_
FROM act_ru_task
WHERE id_ IN (SELECT tasks.id_
FROM (SELECT a.*, rownum rnum
FROM (SELECT DISTINCT res.*
FROM act_ru_task res
INNER JOIN act_ru_identitylink i
ON i.task_id_ = res.id_
WHERE res.priority_ = :b3
AND res.assignee_ IS NULL
AND ((res.due_date_ <= :b2 AND
res.due_date_ IS NOT NULL) OR
res.due_date_ IS NULL)
AND res.assignee_ IS NULL
AND i.type_ = 'candidate'
AND i.group_id_ = :b1
ORDER BY res.id_ ASC) a
WHERE rownum <= :b4) tasks
WHERE rnum >= :b5)
FOR UPDATE
After taking plan
I found there is no duplicate values in the table and distinct is doing full table scan which is not required here.
Before putiing distinct in the table please make sure its needed for your requirement.
