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

Rene Nyffenegger wrote:

> > 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

Nice. I hadn't considered that.

Thanks.

Daniel Morgan Received on Sat Mar 01 2003 - 17:56:31 CST