I need help in optimizing the following query. It is taking a long time to finish. It takes almost 213 seconds . because of some constraints, I can not add an index and have to live with existing ones.

INSERT INTO temp_table_1
( USER_ID, role_id, participant_code, status_id )
WITH A AS
 (SELECT USER_ID user_id,ROLE_ID, STATUS_ID,participant_code
  FROM    USER_ROLE WHERE   participant_code IS NOT NULL), --1
B AS
 (SELECT ROLE_ID
  FROM    CMP_ROLE
  WHERE   GROUP_ID = 3),
C AS (SELECT USER_ID FROM USER) --2

SELECT USER_ID,ROLE_ID,PARTICIPANT_CODE,MAX(STATUS_ID)
FROM A INNER JOIN B USING (ROLE_ID)
       INNER JOIN C USING (USER_ID)
GROUP BY USER_ID,role_id,participant_code ;

--1 = query when ran alone takes 100+ seconds

--2 = query when ran alone takes 19 seconds

DELETE temp_table_1
WHERE ROWID NOT IN
( SELECT a.ROWID
  FROM temp_table_1 a,
  USER_ROLE b
  WHERE a.status_id = b.status_id
  AND   ( b.ACTIVE IN ( 1 ) OR ( b.ACTIVE IN ( 0,3 ) 
  AND SYSDATE BETWEEN b.effective_from_date AND b.effective_to_date ))
);

It seems like the person who wrote the query is trying to get everything into a temp table first and then deleting records from the temp table. whatever is left is the actual results.

Can't it be done such a way that there is no need for the delete? We just get the results needed since that will save time?