Rewrite into SQL
From: amonte <ax.mount_at_gmail.com>
Date: Mon, 9 Mar 2009 22:50:17 +0100
Message-ID: <85c1fb130903091450v3436854au407bb2b3f732bdbe_at_mail.gmail.com>
Hi all
END;
/
Date: Mon, 9 Mar 2009 22:50:17 +0100
Message-ID: <85c1fb130903091450v3436854au407bb2b3f732bdbe_at_mail.gmail.com>
Hi all
I have a simple piece of pl/sql code which can be written into SQL, I have rewritten but was wondering if anyone knows a more efficient way to do it?
*Original code:*
BEGIN
FOR i IN ( SELECT cntr_no, max(update_time) arrive_date, max(trace_seq) mysequence FROM trace_inv WHERE data_field= 'DELETE' AND update_time <= sysdate - 60 GROUP BY cntr_no; LOOP INSERT INTO BACKUP_TRACE SELECT * FROM trace_inv WHERE cntr_no = i.cntr_no AND trace_seq <= mysequence; END LOOP;
END;
/
*Rewritten:*
INSERT INTO backup_trace
SELECT *
FROM trace_inv y
WHERE trace_seq <= (SELECT max(trace_seq)
FROM trace_inv x where x.cntr_no = y.cntr_no AND x.update_time <= SYSDATE - 60 AND x.data_field= 'DELETE');
Thanks all
Alex
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 09 2009 - 16:50:17 CDT