Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 27 Jan 2004 08:52:49 -0800
Message-ID: <1075222307.118484@yasure>

Billy Verreynne wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote i
>
>

>>At a class I taught this last weekend I brought up the fact that most
>>PL/SQL programmers are still writing v7 code. I gave everyone there a 
>>challenge and thought I'd share it with the group for any of you looking 
>>for a challenge on which to sharpen your skills.

>
> <snipped>
>
> My attempt gives me a 3x improvement in performance....
>
> SQL> create or replace procedure fast_proc is
> 2 type TObjectTable is table of ALL_OBJECTS%ROWTYPE;
> 3 ObjectTable$ TObjectTable;
> 4 begin
> 5 select
> 6 * BULK COLLECT INTO ObjectTable$
> 7 from ALL_OBJECTS;
> 8
> 9 forall x in ObjectTable$.First..ObjectTable$.Last
> 10 insert into t1 values ObjectTable$(x) ;
> 11 end;
> 12 /
>
> Procedure created.
>
> SQL> insert into t1 select * from all_objects;
>
> 29259 rows created.
>
> Elapsed: 00:00:05.43
> SQL> exec test_proc
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:17.38
> SQL> exec fast_proc
>
> PL/SQL procedure successfully completed.
>
> Elapsed: 00:00:05.83
>
>
> --
> Billy

This is what I was looking for. But on every machine we had in the lab it routinely kicked out 500%+ improvement. Must confess I felt a bit foolish when Cris pointed out the obvious that I had verbally excluded from what I did in the class but forgot to write up.

Now what percentage of the code in the real world is written this way? Based on studies ... less than 10%.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Jan 27 2004 - 10:52:49 CST