I have been testing using global temporary tables and have been getting some
odd results. It seems up to a certain size they are treated as memory
structures and that size seems to be fixed, I have tried increasing sort
area, setting pga aggregate, nothing seems to effect the behavior. Once they
go to temp they perform worse than regular tables. And, for a final item,
look at this:

SQL> create table test_temp as select * from dba_objects;

Table created.

Elapsed: 00:00:02.03

SQL> select count(*) from test_temp;
 COUNT(*)
----------
0 <---- Look at this, the commit on the DDL caused it to drop all
the rows it just added

1 row selected.

Elapsed: 00:00:19.00

-- Lets add a few rows...

SQL> declare
2 i integer;
3 begin
4 for i in 1..10 loop
5 insert into test_temp select * from dba_objects;
6 end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:59.01

SQL> select count(*) from test_temp;
 COUNT(*)
----------
346236

1 row selected.

Elapsed: 00:00:01.04

-- Let's delete them

SQL> delete test_temp;

346236 rows deleted.

Elapsed: 00:03:17.08

SQL> select count(*) from test_temp;
 COUNT(*)
----------
0

1 row selected.

Elapsed: 00:00:19.00

-- Now let's get them back

SQL> rollback;

Rollback complete.

Elapsed: 00:06:31.07
SQL> select count(*) from test_temp
SQL> /
 COUNT(*)
----------
31476

1 row selected.

Elapsed: 00:00:01.02

-- oops..where did my other 310,000 rows go?

Michael R. Ault
Senior Technical Management Consultant
TUSC - The Ultimate Software Consultants
5 Concourse Parkway
Suite 3100
Atlanta, Ga 30328, USA
770-481-1935

Oracle LazyDBA home page