Re: Rewrite into SQL

I did a small test but I got same results with both approaches

SQL> create table t1
  2 (
  3 trcseq number,
  4 cntr_no varchar2(10),
  5 update_time date,
  6 flag varchar2(8));

Table created.

SQL> insert into t1
  2 values
  3 (1, 'Country1', sysdate - 20, 'DELETE');

1 row created.

SQL> insert into t1
  2 values
  3 (2, 'Country2', sysdate - 70, 'DELETE');

1 row created.

SQL> SELECT cntr_no,

  2                          max(update_time) arrive_date,
  3                          max(trcseq) mysequence
  4                   FROM t1
  5                  WHERE flag = 'DELETE'
  6                    AND update_time <= sysdate - 60
  7                 GROUP BY cntr_no;

CNTR_NO ARRIVE_DA MYSEQUENCE
---------- --------- ----------
Country2 30-DEC-08 2

SQL> SELECT *
  2 FROM t1 y
  3 WHERE trcseq <= (SELECT max(trcseq)

  4                        FROM t1 x
  5                       where x.cntr_no = y.cntr_no
  6                         AND x.update_time <= SYSDATE - 60
  7                         AND x.flag = 'DELETE');

    TRCSEQ CNTR_NO UPDATE_TI FLAG
---------- ---------- --------- --------

         2 Country2 30-DEC-08 DELETE

Thanks

Alex

On Tue, Mar 10, 2009 at 12:12 AM, Toon Koppelaars < toon.koppelaars_at_rulegen.com> wrote:

> I think your rewrite is flawed...
>
> Suppose the table currently has two rows:
> trcseq1 Country1 Tim1 DELETE
> trcseq2 Country2 Tim2 DELETE
>
> Suppose Tim1 is less than 60 days ago.
> Suppose Tim2 is more than 60 days ago.
> Suppose trcseq1 is smaller than trcseq2.
>
> The PL/SQL block will not insert the first row in the other table.
> Your rewrite wil.
>
> Other rewrites all depend upon the constraints that are governing the
> allowed data in that table.
> For instance, if the following condition holds for table Trace_inv:
>
> "for any two different rows in trace_inv, if update_time of the one row is
> smaller than update_time of the other row, then trace_seq of the one row is
> also smaller than trace_seq of the other row"
> (might very well be true for your table)
>
> Then you can rewrite this into.
>
> INSERT INTO backup_trace
> SELECT *
> FROM trace_inv y
> WHERE y.update_time <= SYSDATE - 60
> AND y.data_field= 'DELETE';
>
> But then again. It depends.
>
>
>
>
> On Mon, Mar 9, 2009 at 10:50 PM, amonte <ax.mount_at_gmail.com> 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
>>
>
>
>
> --
> Toon Koppelaars
> RuleGen BV
> +31-615907269
> Toon.Koppelaars_at_RuleGen.com
> www.RuleGen.com
>
> (co)Author: "Applied Mathematics for Database Professionals"
>

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