Re: Rewrite into SQL
- From: amonte <ax.mount@xxxxxxxxx>
- To: Toon Koppelaars <toon.koppelaars@xxxxxxxxxxx>
- Date: Tue, 10 Mar 2009 09:22:22 +0100
Hi Toon
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@xxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxx
> www.RuleGen.com
>
> (co)Author: "Applied Mathematics for Database Professionals"
>
Other related posts:
