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?
