I'm having a hard time wrapping my head around this query. it is taking almost 200+ seconds to execute. I've pasted the execution plan as well.
SELECT
user_id ,
ROLE_ID ,
effective_from_date ,
effective_to_date ,
participant_code ,
ACTIVE
FROM
CMP_USER_ROLE E
WHERE
ACTIVE = 0
AND (SYSDATE BETWEEN effective_from_date AND effective_to_date
OR TO_CHAR(effective_to_date,'YYYY-Q') = '2010-2')
AND participant_code = 'NY005'
AND NOT EXISTS ( SELECT 1 FROM CMP_USER_ROLE r
WHERE r.USER_ID= E.USER_ID AND r.role_id = E.role_id
AND r.ACTIVE = 4
AND E.effective_to_date <= (SELECT MAX(last_update_date)
FROM CMP_USER_ROLE S
WHERE S.role_id = r.role_id
AND S.role_id = r.role_id
AND S.ACTIVE = 4 ))
Explain plan
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 154 (2)| 00:00:02 |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID | USER_ROLE | 1 | 37 | 30 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | N_USER_ROLE_IDX6 | 27 | | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | HASH GROUP BY | | 1 | 47 | 124 (2)| 00:00:02 |
|* 6 | TABLE ACCESS BY INDEX ROWID | USER_ROLE | 159 | 3339 | 119 (1)| 00:00:02 |
| 7 | NESTED LOOPS | | 11 | 517 | 123 (1)| 00:00:02 |
|* 8 | TABLE ACCESS BY INDEX ROWID| USER_ROLE | 1 | 26 | 4 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | N_USER_ROLE_IDX5 | 1 | | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | N_USER_ROLE_IDX2 | 957 | | 74 (2)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
