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

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 Received on Sat Mar 01 2003 - 09:31:45 CST