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

Van Messner wrote:

> "Which is exactly what happens when you insert rows into any table and don't
> issue a commit."
>
> Global temporary tables can persist your data across a session or across a
> transaction - it's your choice.
>
> "DA Morgan" <damorgan_at_exesolutions.com> wrote in message
> news:3E60D261.AFBA63B6_at_exesolutions.com...
> > 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?
> >
> > Daniel Morgan
> >

Which is, as I said, is no different from a normal heap table except for the single line of code required to empty the table whenever you wish.

Daniel Morgan Received on Sat Mar 01 2003 - 17:57:37 CST