Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Tue, 27 Jan 2004 11:39:52 -0800
Message-ID: <1075232332.172716@yasure>

Alex Filonov wrote:

> Cris Carampa <cris119_at_operamail.com> wrote in message news:<bv5al1$c6r$1_at_panco.nettuno.it>...
> 

>>Daniel Morgan wrote:
>>
>>
>>>Everyone using 8i+ features should be able to improve the performance of
>>>this by at least 5X.
>>
>>I always thought the best way to do that was:
>>
>>INSERT INTO t1
>> (owner, object_name, subobject_name, object_id,
>> data_object_id, object_type, created, last_ddl_time,
>> timestamp, status, temporary, generated, secondary)
>>select
>> x.owner, x.object_name, x.subobject_name, x.object_id,
>> x.data_object_id, x.object_type, x.created,
>> x.last_ddl_time, x.timestamp, x.status, x.temporary,
>> x.generated, x.secondary
>>from
>> all_objects x ;
>>
>>But since this construct was already available in Oracle7 I'm surely wrong.
>>

> 
> 
> You're quite right. Daniel probably means some kind of FOR ALL or BULK
> but insert...select is surely fastest, because it is executed inside
> of SQL engine. And, of course, it was available long ago, surely in
> v6, but I think much earlier.

All correct. I told the students that they couldn't do that obvious step but forgot to include it in what I posted here. Mea culpa.

Since I blew I'll post a solution now ... I was thinking in terms of some variation on the following:

CREATE OR REPLACE PROCEDURE fast_proc (
p_array_size IN PLS_INTEGER DEFAULT 100) IS

TYPE ARRAY IS TABLE OF all_objects%ROWTYPE; l_data ARRAY;

CURSOR c IS
SELECT *
FROM all_objects;

BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO t2 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
  END LOOP;
  CLOSE c;
END fast_proc;
/

of which many possibilities exist. One of which Billy V posted. The point I would hope more junior developers take away from this is that while cursors definitely have their uses ... they should not be the first thing one thinks of any more. Ok ... shouldn't be the first thing after the 'obvious' DML statement.

-- 
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 - 13:39:52 CST