Hi,
Requesting you to please check. Oracle code taking less than 4 seconds to execute whereas PostgreSQL taking more than an hr (still running).
Merge into is not available in old version of postgresql/pgadmin. Amazon RDS won’t support in PRD as of now.So,i;ve tried to re-write but that’s taking too much time.
------ ORACLE CODE -------
Merge INTO abc.cust_tot CT
Using (SELECT /*+ PARALLEL(ct,4) PARALLEL(cd,4) USE_HASH(ct) */
ct.cust_num, cust_yp, cust_yp_dt
from cust_tot ct
inner join cust_dt cd
on (ct.cust_num = cd.cust_num)
where ct.cust_num not in
(select cust_no from Cust_h_bal)) S
ON (CT.cust_num = S.cust_num)
WHEN MAtched then
Update
set cust_bal = case
when Trunc(NVL(S.cust_yp_dt,
To_date(‘01/01/1975’, ‘MM/DD/YYYY’))) >
ADD_MONTHS(Trunc(sysdate), -12) then
case
when S.cust_yp >= 0 then
case
when NVL(S.cust_yp, 1) <> NVL(cust_bal, 0) then
NVL(S.cust_yp, 0)
else
cust_bal
end
else
0
End
else
0
End,
timestamp = sysdate;
------------------- PostgreSQL:
UPDATE abc.cust_tot
SET cust_bal =
CASE
WHEN DATE_TRUNC(‘DAY’,(COALESCE(s.cust_yp_dt, TO_DATE(‘01/01/1975’, ‘MM/DD/YYYY’)))) >
DATE_TRUNC(‘DAY’,CURRENT_DATE) - INTERVAL ‘12 MONTHS’ THEN
CASE
WHEN s.cust_yp >= 0 THEN
CASE
WHEN COALESCE(s.cust_yp, 1) <> COALESCE(CT.cust_bal, 0)
THEN COALESCE(s.cust_yp, 0)
ELSE CT.cust_bal
END
ELSE 0
END
ELSE 0
END ,timestamp = CURRENT_DATE
FROM abc.cust_tot AS ct
inner JOIN (SELECT /*+ Parallel(ct 4 hard) Parallel(cd 4 hard) HashJoin(ct) */
ct.cust_no_717, cust_yp, cust_yp_dt
FROM
abc.cust_tot AS ct
inner JOIN abc.cust_dt AS cd
ON ct.cust_no_717 = cd.cust_no_717
WHERE NOT EXISTS
(SELECT 1
FROM abc.Cust_h_bal as chb where chb.cust_num = ct.cust_num)
) AS s
ON ct.cust_num = s.cust_num;
The target table should not repeated in the FROM clause of an UPDATE statement. That is most probably the reason why the UPDATE is so slow.
Your code is a bit hard to follow, but when I translate the original MERGE statement, I would come up with something like this:
UPDATE abc.cust_tot ct
SET cust_bal = CASE
WHEN DATE_TRUNC('DAY',(COALESCE(s.cust_yp_dt, date '1975-01-01))) >
DATE_TRUNC('DAY',CURRENT_DATE) - INTERVAL '12 MONTHS' THEN
CASE
WHEN s.cust_yp >= 0 THEN
CASE
WHEN COALESCE(s.cust_yp, 1) <> COALESCE(CT.cust_bal, 0) THEN COALESCE(s.cust_yp, 0)
ELSE CT.cust_bal
END
ELSE 0
END
ELSE 0
END,
timestamp = CURRENT_DATE
FROM (
SELECT ct.cust_num, cust_yp, cust_yp_dt
from cust_tot tot
join cust_dt cd on ct.cust_num = cd.cust_num
where not exists (select *
from Cust_h_bal bal
where bal.cust_num = tot.cust_num)
) AS s
WHERE ct.cust_num = s.cust_num;
Your UPDATE statement seems to do something different than the MERGE.
It joins the table cust_tot one additional time but using the column cust_no_717 - which is not used at all in your MERGE statement.
I am wondering if the JOIN in the derived table is needed at all.
So something like:
UPDATE abc.cust_tot ct
...
FROM cust_dt cd
WHERE ct.cust_num = cd.cust_num
AND NOT EXISTS (select *
from Cust_h_bal bal
where bal.cust_num = ct.cust_num)
psql:/dbf/appworx/pgsql/ats_monthly.sql:909: HINWEIS: Fehler – konnte nicht in Datei „base/pgsql_tmp/pgsql_tmp31083.1724“ schreiben: Kein Speicherplatz mehr auf dem Gerät
–--set fname :REPORT_PATH ats_monthly.xls