Re: Rewrite into SQL

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Mon, 09 Mar 2009 23:58:51 +0100
Message-ID: <49B59F2B.1030504_at_roughsea.com>

Alex,

  You should list columns explictly rather than use *

INSERT INTO backup_trace
SELECT y.*
FROM trace_inv y,

     (SELECT ctrn_no, max(trace_seq) trace_seq
      FROM trace_inv
      where update_time <= SYSDATE - 60
       AND data_field= 'DELETE') x

WHERE x.ctrn_no = y.ctrn_no
  and y.trace_seq <= x.trace_seq;

I can think also of using max() as an analytical function by using over (partition by ctrn_no) but it depends on whether the rows you want to insert all contain 'DELETE' in data_field and were all updated more than 2 months ago - which isn't obvious from the query.

HTH SF

amonte wrote:
> 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

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 09 2009 - 17:58:51 CDT