Re: Fast utility to transfer data from one database to another excluding IMP/EXP

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 08 Aug 2006 20:55:12 +0200
Message-ID: <ebammc$eka$02$1@news.t-online.com>

HansF schrieb:
> On Tue, 08 Aug 2006 20:15:47 +0200, Acme Acmeson wrote:
>

>> When inserting rows the append hint may have an impact, but also when 
>> selecting ?

>
> The thought plickens ... er ... plot thickens
>
> How about reducing overall system resources and speeding up things when
> using the append hint in an insert that is attached to a select ...
>
> On a Dell Latitude C840/2.2GHz/2GB running XE SuSE 10:
>
>
> pop2_at_fuzzy:~> sqlplus test/test
>
> SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 8 12:35:07 2006
>
> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>
>
> Connected to:
> Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
>
> SQL> create table test as select * from all_objects where 1=2;
>
> Table created.
>
> SQL> set timing on
> SQL> insert into test select * from all_objects;
>
> 5293 rows created.
>
> Elapsed: 00:00:07.02
> SQL> truncate table test;
>
> Table truncated.
>
> Elapsed: 00:00:00.10
> SQL> connect system/oracle
> Connected.
> SQL> alter system flush buffer_cache;
>
> System altered.
>
> Elapsed: 00:00:00.43
> SQL> connect test/test
> Connected.
> SQL> insert /** append */ into test select * from all_objects;
>
> 5293 rows created.
>
> Elapsed: 00:00:01.00
> SQL>
>
>
>

Actually, it seems to be a very funny thread now. I didn't know before, that /** append */ (i.e. a simply comment) can so improve performance... But , should be of course tested - and on my linux box - it seems, Oracle scales with the count of tests ;-)

cms_at_ORA102> create table test as select * from all_objects where 1=2;

Table created.

cms_at_ORA102>
cms_at_ORA102> set timing on
cms_at_ORA102> insert into test select * from all_objects;

51055 rows created.

Elapsed: 00:00:08.59
cms_at_ORA102>
cms_at_ORA102> truncate table test;

Table truncated.

Elapsed: 00:00:01.45
cms_at_ORA102>
cms_at_ORA102> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
cms_at_ORA102>
cms_at_ORA102> insert /** append */ into test select * from all_objects;

51055 rows created.

Elapsed: 00:00:04.11
cms_at_ORA102> truncate table test;

Table truncated.

Elapsed: 00:00:01.15
cms_at_ORA102> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.01
cms_at_ORA102> insert into test select * from all_objects;

51055 rows created.

Elapsed: 00:00:02.67

Best regards

Maxim Received on Tue Aug 08 2006 - 13:55:12 CDT