Re: Oracle For Fun

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

Cris Carampa wrote:

> 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.
>
> Perhaps are there performance benefits in using BULK clauses and arrays
> instead?
>
> Kind regards,

You are, of course, absolutely correct. Just throw in the APPEND hint if you want to really give it a kick. I should have excluded the obvious ... "why use a loop at all" solution.

-- 
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:49:50 CST