Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Global Temporary Table performance with inserts.

Re: Global Temporary Table performance with inserts.

From: Frank <fbortel_at_nescape.net>
Date: Sat, 06 Dec 2003 16:11:39 +0100
Message-ID: <bqsr30$75p$1@news2.tilbu1.nb.home.nl>


Jonathan Lewis wrote:

> There are a couple of oddities with GTTs,
> array inserts, and volume of undo/redo
> generated; but I had thought they appeared
> only in version 9.
>
> Are you doing array inserts, and if so, are you
> doing with the /*+ append */ hint ?
>
>

There is definitely is speed difference: This is 9.2.0.4:
SQL> create global temporary table t1 on commit preserve rows

   as select * from all_objects where 1=2 Table created.
SQL> create table t2 as select * from all_objects where 1=2; Table created.

SQL> set timing on
SQL> insert into t1 select * from all_objects;

28904 rows created.

Elapsed: 00:00:02.05
SQL> insert into t2 select * from all_objects;

28905 rows created.

Elapsed: 00:00:01.08

T1 (GTT) is about twice as slow as t2.
Now for the append hint:
SQL> insert /*+append */ into t1 select * from all_objects;

28905 rows created.

Elapsed: 00:00:01.06

Same as t2 (regular table)

-- 
Regards, Frank van Bortel
Received on Sat Dec 06 2003 - 09:11:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US