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

>>>>> "DA" == DA Morgan <damorgan_at_exesolutions.com> writes:

 DA> "Howard J. Rogers" wrote:
>> On Sat, 01 Mar 2003 15:57:37 +0000, DA Morgan wrote:
>>
>> > 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.
>> >>
>>
>> >
>> > 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
>>
>> I haven't followed this thread, so if this is inappropriate, my
>> apologies. But GTT are VASTLY different from heap tables with a
>> single line of code. For a start, they don't generate redo
>> (though we've already been round the fact that the undo they
>> generate itself generates redo). For another, since the data is
>> private to the session, then multiple users can make simultaneous
>> use of the same GTT without the need to create unique/individual
>> heap tables. And lastly, since the data is private to the sesson,
>> we also don't need to go through the messy business of taking out
>> row-level locks to prevent conflicting updates.
>>
>> Transactions in GTTs are therefore much lighter in overhead
>> resources than the same transactions being done in a regular heap
>> table.
>>
>> Regards HJR

 DA> Glad you jumped in and thanks for the clarification. I agree with
 DA> your points except for one. The data is no more private than data
 DA> put into any other table before you commit the
 DA> transaction. Uncommited data in a table is also private to the
 DA> session.

Yes, from one perspective this is true, but possibly a little simplistic. If you are processing data based on data in the temporary table, it is likely you will want to perform some commits, but still want the data in the GTT to be isolated from other sessions. While you can probably solve this using well placed savepoints and possibly autonomous transactions, it makes things more complex than with the GTT solution where you can commit to save the data your interested in and know the data in the GTT will still be 'invisible' to other sessions which may also be using that table.

Of course, this data isolation is only part of why GTT can be useful in some situations - I don't think anybody is advocating they be used in the same way temporary tables are used in SQL Server etc. On the whole, they are rarely needed, but in some situations they can make the solution simpler and more maintainable.

I noticed somebody else (FC?) posted in this thread suggesting the plan table could be defined as a GTT. I thought about this and actually think it would possibly be a good solution in some situations. You generally only interested in plan data during an analysis session and don't need it forever, you could have one plan table used by all schemas rather than tables in every schema and I gather from the documentation that one of the goals of GTTs is to make insert and update operations more light weight - all of which seems to make GTTs well suited for a plan table.

I find the statement "Never use temporary tables in Oracle" just a little too strong. It reminds me of the "Never use goto in your code" statement. In both cases, while it is true you should avoid making extensive use of either construct, never say never. I remember a situation back when I was working on building a C compiler. I'd been taught to never use goto and so never did. During a code review session, the head of the project presented two different versions of a basic C compiler. One used goto and the other didn't. It was immediately evident the solution which used goto was actually a lot cleaner, easier to follow and more efficient than the version which worked hard to avoid using a goto when it was the 'natural' choice for that particular situation.

The goto statement got its bad reputation because too many people used it poorly and created spaghetti code which was difficult to follow, debug or maintain. I think the use of temporary tables has got a bad rep for similar reasons - too many people using them poorly - creating code which was inefficient and often less intuitive and more difficult to follow than spending a little more time working out a better solution or getting to know/understand SQL better.

I would never advocate GTTs (or any kind of temp table) as a genral rule. However, I think they do have their use, even if this is very rare and in some situations they can provide an elegant, clear and efficient solution. I guess the real skill is in identifying those situations where the GTT is the right solution from those where it is not.

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!