I am well aware of what they are and aren't, just want others to realize
their limits and to completely understand them myself rather than buying the
Oracle line on them. If you use an on commit preserve rows type (preserve
rows after a commit, default is not to preserve rows) then the problem with
the rollback situation goes away.

Mike

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


Another way to look at it is a scratch pad until you 'commit' your
transaction.

I've seen your postings. You're definitely smart enough to put them to good
use.
It just reguires a change in thinking.



-----Original Message-----
From: John Lewis [mailto:[Email Address Removed] Tuesday, April 30, 2002 11:32 AM
To: '[Email Address Removed] 'LazyDBA.com Discussion'
Subject: RE: Using Global Temporary tables


The 'commit' is intentional. It's actually a feature. I use it for
web sites I've done.

What I do is make the temp table once - Not on fly - then just let users use
it.
Think of it like a global bucket everyone can use but still see only thier
stuff.

I've had good results with them. Don't think of them like Sybase or Sql
Server temp tables.

Regards -

-----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 LazyDBA home page