Re: How to avoid a temporary table

I think the argument that temporary tables are unnecessary in Oracle is overdone.

The issue is one of physical de-normalisation, and as such temporary tables do a fine job. So do index clusters (pre-joining tables at the physical layer) and so do materialized views (which in some respects are 'permanent' temporary tables). If your design requires denormalisation somewhere along the line, then that's probably a good thing (since too many people normalise like mad, to the point of overdoing it), and temporary tables are a perfectly adequate solution to the problem.

I saw someone here recommend (probably in another thread) the use of the new 9i "WITH" syntax. Guess what that creates under the hood? Yup: a temporary table. If Oracle itself is doing it, feel free to join in, is my motto.

Many years ago, I needed a random selection from one table to produce an inspection report -only, you couldn't send inspectors out in a completely random search pattern: there was a route they needed to follow. So having selected my random things to inspect, I needed to order the results. And then I needed to hang on to them so that several inspectors could produce inspection lists. And the things they were inspecting were a join from three tables. Absolutely a classic case for a temporary table, I would have thought: grab the random selection, populate a temporary table, slap an index on ROUTE#, print out by ROUTE#, and hold onto the results so that multiple inspectors could print reports. But destroy the results when they're done, since next week's inspection list needed to start from (random) scratch.

So my message on Performance Tuning courses is: make use of Global Temporary Tables. They are a very nice way of denormalising data, and have several housekeeping characteristics which make them desirable.

Regards
HJR Received on Sat Oct 25 2003 - 05:16:18 CDT