Need to re-write delete statement

Database
Enthusiast

Need to re-write delete statement

Below delete is consuming 11K CPU.Need assistance to re-write the below delete sql to consume less CPU.

DELETE  FROM IMPORT_REFER_F AA WHERE ( AA.JOB_ID,  AA.JOB_HEADER_ID,AA.JOB_LINE_NUM,AA.JOB_IDN) 

IN (SELECT JOB_ID,JOB_HEADER_ID,JOB_LINE_NUM,JOB_IDN FROM IMPORT_REFER_S BB GROUP BY 1,2,3,4)

AND  AA.JOB_HEADER_ID = IMPORT_REFS_F_GT.JOB_HEADER_ID;

IMPORT_REFER_F :      2677397287

PI  is multi column  and has stats on multi and individual:  JOB_ID ,JOB_HEADER_ID ,JOB_LINE_NUM ,REF_3 ,G_S_L_ID ,JOB_IDN

IMPORT_REFER_S  : 13035863

PI  is multi column  and has stats on multi and individual:  JOB_ID ,JOB_HEADER_ID ,JOB_LINE_NUM ,REF_3 ,G_S_L_ID ,JOB_IDN

IMPORT_REFS_F_GT  :43465

PI is JOB_HEADER_ID   and has stats

There are no PPI columns  defined on any of 3 tables and stats are fresh.

1 REPLY
Enthusiast

Re: Need to re-write delete statement

DELETE AA
FROM IMPORT_REFER_F AA,
IMPORT_REFER_S BB,
IMPORT_REFS_F_GT CC
WHERE
 AA.JOB_ID = BB.JOB_ID
AND AA.JOB_HEADER_ID = BB.JOB_HEADER_ID
AND AA.JOB_LINE_NUM = BB.JOB_LINE_NUM
AND AA.JOB_IDN = BB.JOB_IDN
AND BB.JOB_HEADER_ID = CC.JOB_HEADER_ID

--- or try the below SQL

DELETE AA
FROM IMPORT_REFER_F AA,
IMPORT_REFS_F_GT CC,
IMPORT_REFER_S BB,
WHERE
 AA.JOB_HEADER_ID = CC.JOB_HEADER_ID
AND AA.JOB_ID = BB.JOB_ID
AND AA.JOB_HEADER_ID = BB.JOB_HEADER_ID
AND AA.JOB_LINE_NUM = BB.JOB_LINE_NUM
AND AA.JOB_IDN = BB.JOB_IDN

Please try these re-writes.