Re: Oracle For Fun

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: 27 Jan 2004 03:10:33 -0800
Message-ID: <1a75df45.0401270310.77aa4be4@posting.google.com>

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

Received on Tue Jan 27 2004 - 05:10:33 CST