Re: Global Temporary Table - Erratic results?

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 21 Aug 2006 14:02:04 -0700
Message-ID: <1156194123.982535@bubbleator.drizzle.com>

Deltones wrote:

> Frank van Bortel wrote:

>> Deltones schreef:
>>> I posted a question last week where people where some of the guys who
>>> replied recommanded that I use "create global temporary table" instead
>>> of "create table". I tried it with the script I've been asked to modify
>>> that contained the latter construction.
>>>
>>> I get my results every time with the "create table" version, but if I'm
>>> lucky, I get results maybe 50% of the time with the "create global
>>> temporary table". Take note that it's the same queries that are run in
>>> both cases and that I also tried the "on commit delete rows", "on
>>> commit preserve rows" clause, and that the tables are dropped at the
>>> end. Why does it do this?
>>>
>>> Are tables created with "global temporary" different that regular
>>> tables?
>>>
>>> Thanks
>>>
>>> Denis
>>>
>> Yes - they exist for the duration of the session, if not shorter.
>> And you do not "use the create global temporary table everytime",
>> you just use it once.
>>
>> Your style reminds me of dBaseIII and Foxbase I used to program.
>>
>> And if you get the wrong results, you might run into a bug,
>> in which case versions do matter enormously!

> 
> It's the style of the script I have to modify, not mine. The main query
> to create the first temp table is an 800 lines abomination :)
> 
> As for the duration of the session, I get that, but here what I mean. I
> run the script, I get my results. Then I run the same script, let's say
> 15 seconds later, I don't get anything.
> 
> But with the regular "create table" version, I get my results every
> time. That's what confuses me.

There are two different types of Global Temporary Tables. Go to http://tahiti.oracle.com and read up on them and Morgan's Library for demo code to understand how they work.

A GTT is a premanent object. You build it once and never again. It is not part of the script.

This is Oracle not some other product. To be successful you need to read and you need to do things Oracle's way.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Mon Aug 21 2006 - 16:02:04 CDT