Using a on commit preserve rows seems to eliminate the rollback issue and
seems to perform faster:

SQL> create global temporary table test_temp on commit preserve rows as
select * from dba_objects;

Table created.

Elapsed: 00:00:01.03
SQL> select count(*) from dba_objects;
 COUNT(*)
----------
31476

1 row selected.

Elapsed: 00:00:00.07
SQL> select count(*) from test_temp;
 COUNT(*)
----------
31476

1 row selected.

Elapsed: 00:00:00.02
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:01:00.04
SQL> select count(*) from test_temp;
 COUNT(*)
----------
346236

1 row selected.

Elapsed: 00:00:00.07
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select count(*) from test_temp;
 COUNT(*)
----------
346236

1 row selected.

Elapsed: 00:00:00.09
SQL> delete test_temp;

346236 rows deleted.

Elapsed: 00:02:36.08
SQL> rollback;

Rollback complete.

Elapsed: 00:01:55.05
SQL> select count(*) from test_temp;
 COUNT(*)
----------
346236

1 row selected.

Elapsed: 00:00:01.09



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