The first posting is for a default GTT that releases rows on commit. In some
follow on testing on a GTT that has the on commit preserve rows clause the
rollback issue is resolved. However I feel that the default behavior is a
problem, especially if it isn't documented. What is it deciding to rollback
to?

Mike

-----Original Message-----
From: Marimuthu, Kumaresan [mailto:[Email Address Removed] Tuesday, April 30, 2002 2:14 PM
To: LazyDBA.com Discussion
Subject: RE: Using Global Temporary tables


Hi Mike!,

1) Are you done this stuff in the same session ??
2) Is your Global Temporary tables is Session specific or transaction
specific ??

: Kumar

-----Original Message-----
From: Mike Ault [mailto:[Email Address Removed] Tuesday, April 30, 2002 10:52 AM
To: LazyDBA.com Discussion
Subject: Using Global Temporary tables


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 documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

--------
Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page