Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

Re: Global Temp Tables - lots of redo in 9i, why? how to fix?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 1 Mar 2003 17:17:07 GMT
Message-ID: <b3qpuj$1p2b8q$1@ID-82536.news.dfncis.de>

> Comments interspersed.
> 
> Tim X wrote:
> 

>> >>>>> "DA" == DA Morgan <damorgan_at_exesolutions.com> writes:
>>
>> <snipped>
>>
>> I've used GTTs once and can think of another area where they can be
>> useful. However, in reality, I don't treat them as temporary tables so
>> much as tables which have temporary data - the table stays, but the
>> data does not.
> 
> Which is exactly what happens when you insert rows into any table and don't
> issue a commit.
> 
> 

>> The place where I have used them is in an application which needs to
>> collect and process data from a number of remote databases and put the
>> results into a permanent table. While I was able to find a solution
>> which did this without using any temporary tables, I found the use of
>> a GTT was a bit faster and more importantly, resulted in simpler and
>> clearer SQL. The GTT solution was selected in the end because it was
>> simpler to follow and therefore would (hopefully) be simpler to
>> maintain and I always try to put an emphasis on maintenance.
> 
> I agree that this could be desirable. But my statement was that I had yet to
> find anytime that they were "needed" meaning "required".
> 

>> The second possible use of GTTs which I can think of is in
>> applications where you have to maintain some data for a temporary
>> period and you possibly don't want other sessions to be aware of data
>> which they have not placed in the table. A possible example could be a
>> web application where you want to maintain some sort of state
>> information, but only until the session ends and possibly you have
>> multiple sessions, but you don't want to maintain session identifiers.
>> Again, this is not really a temporary table as the table is permanent,
>> but the data is temporary. I find the special features of GTTs over
>> regular tables meet these sort of situations a lot better.
> 
> This is exactly the behavior of an uncommitted insert into a heap table. Not
> that a GTT won't work ... but so will any table.
> 

>>
>> So, while I agree 100% that using temporary tables within Oracle
>> should be avoided (e.g. tables which are created and (hopefully)
>> removed) simply to hold intermediate data during some complex
>> processing etc), I do think temporary global
>> tables can be very useful in some situations because of their special
>> features (such as data only being visible to the session which
>> inserted it, automatic removal of data when the session exits, no
>> rollback overhead etc).
>>
>> Possibly Global Temporary Tables should have been called Global
>> Temporary Data Tables?
>>
>> Tim
>>
>> --
>> Tim Cross
>> The e-mail address on this message is FALSE (obviously!). My real e-mail is
>> to a company in Australia called rapttech and my login is tcross - if you
>> really need to send mail, you should be able to work it out!
> 
> The only value I see is the automatic removal which avoids my needing to write
> one line of code. The other functionality is nothing more than INSERT without a
> COMMIT.
> 
> Or is there something I'm missing?


GTT allows you to have RI on a 'session level basis'. Consider:

create global temporary table gtt_ (
  a_ number primary key,
  b_ varchar2(10)
) on commit preserve rows;

#### Session A

  sQL> insert into gtt_ values(1,'one');   

  1 row created.   

  SQL> insert into gtt_ values(2,'two');   

  1 row created.   

  SQL> insert into gtt_ values(3,'three');   

  1 row created.

#### Session B

  SQL> insert into gtt_ values(1,'eins');   

  1 row created.   

  SQL> insert into gtt_ values(2,'zwei');   

  1 row created.   

  SQL> insert into gtt_ values(3,'drei');   

  1 row created.

Althoug Session A has already used 1, 2 and 3 as values for the primary key, session B can happily use these values as well, without getting blocked until A either commits or rolls back.

Rene Nyffenegger   

-- 
  no sig today
Received on Sat Mar 01 2003 - 11:17:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US