All Forums Database
ap90792 11 posts Joined 08/14
28 Aug 2015
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.

kirthi 65 posts Joined 02/12
28 Aug 2015
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.
 

You must sign in to leave a comment.