Can you please help me re-write below query as it is highly skewd and having very high Impact cpu.
DELETE FROM xyz.#VAU_DETAIL a
(select 1 from xyz.VAU_DETAIL b
where b.O_TRAN_ID = a.O_TRAN_ID
and b.ER_ID not = a.ER_ID
and a.AC_DT >= b.AC_DT);
Can you post the EXPLAIN output please?
Can you give as well the top result rows of:
SELECT O_TRAN_ID,COUNT(*) FROM xyz.#VAU_DETAIL GROUP BY 1 ORDER BY 2 DESC ?
"and b.ER_ID not = a.ER_ID" is what's killing your performance.. rewrite it as a loj on a.tran_id = b.tran_id where a.ac_dt > b.ac_dt and b.er_id is null (assuming "a" is the left table)