Re: How to Create Local Temporary Table

>> Temp tables in the products that originated with Ingres (Informix,
>> Sybase, SQL Server) are used as a workaround for problems related
>> to locking (lock escalation) and the transaction model.
> Is that so? I thought temp tables are often used to avoid redoing > expensive queries perhaps. But that's just the crazy person in me....

True enough, in other RDBMS platforms. Not so true in Oracle. As Daniel pointed out, many times, it is locking that forces one to look at temp tables and Oracle does not share this problem.

In other cases, one would typically employ a temp table to temporarily hold a result set from one query to be used in another query. With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. I've never used DB2, so I cannot speak to its SQL language, but I have used SQL Server quite extensively. And SQL Server's SQL functionality is lagging behind Oracle in many areas.

If you do have expensive queries that need a result set stored, then Oracle has another option called Materialized Views (MV). Data stored in MV is permanent therefore a MV is not considered a temp table. The MV can help with expensive query processing, and with Query Rewrite, its implementation can be transparent to the end user. The user does not have to query the MV directly and application code does not have to change. Quite a different animal than temp tables.

Cheers,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown