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