Re: Rewrite into SQL

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Tue, 10 Mar 2009 09:46:12 +0100
Message-ID: <ecf3dae70903100146k25e5e24ai2785b02344d02d8d_at_mail.gmail.com>

I stand corrected.
Just shows I should not respond to interesting questions while jetlagged in a hotel at Hotsos...

On Tue, Mar 10, 2009 at 9:22 AM, amonte <ax.mount_at_gmail.com> wrote:

> 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_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"
>>
>
>

-- 
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

Received on Tue Mar 10 2009 - 03:46:12 CDT