Re: Global Temporary Table performance with inserts.

Jonathan Lewis wrote:

> Repeat your no-append tests,
> but start a transaction by doing
> a small update some where else
> before doing the insert. Then:
>
> Check v$transaction.used_urec and v$mystat
> (your rows from v$sesstat) for statistics
> "redo size" and "redo entries" before and
> after doing the main insert. I think you will
> find that Oracle generates one undo record
> (urec) per row for the temp table, but one
> undo record per block for the normal table -
> hence the significant difference in time.

As I recall the original post was about 100 rows and when I run with 100 rows I find no detectable difference.

SQL> insert into t1

   2 select * from all_objects
   3 where rownum < 101;

100 rows created.

Elapsed: 00:00:00.04
SQL> insert into t2

   2 select * from all_objects
   3 where rownum < 101;

100 rows created.

Elapsed: 00:00:00.04
SQL> That is after the first run. The first time I run it I see a difference. All subsequent tests yield the same result.

-- 
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)