Re: Global Temporary Table performance with inserts.

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