Re: How to Create Local Temporary Table

Brian Peasland wrote:
>

First: thank you for the technical content! :-)
>>> 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.
If I use temp tables to free locks, yes. That is one usage I can think of.

> 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 presume you are referring to CTE (common table expression, aka WITH). Yet, complex SQL does not replace the need for procedural logic. E.g. when the result of a query needs to be processed by multiple procedures in a serial fashion (as opposed to being able to share a cursor). Further more, as I presume we will all readily agree the hordes of application developers don't know squat about SQL and run scared of a three way join, never mind what Oracle calls an inline view (aka nested subquery).
This is a pill that's for me harder to swallow than many others. Took me years to get past denial on that one...

>I've never used DB2, so I cannot speak to its SQL language,
Never mind DB2. Not the topic. This is about concepts. If it places the group's minds at ease DB2 supports neither VARRAY nor local temps. I have nothing to gain from pushing one over the other. All I have to gain is insight.

> but I have used SQL Server quite extensively. And SQL Server's SQL
> functionality is lagging behind Oracle in many areas.
..like not having VARRAY or other collection types, yes.

> 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.
Materialized views (aka indexed views in SQL Server) are, as you note, permanent objects. They are valuable when similar queries are used often within the lifespan of the database and they trade update performance and storage for query performance.
I'm sure one would not want to use them to buffer a resultset for usage within a specific procedure or package in either Oracle or SQL Server. The maintenance cost is too high compared to the benefit (perhaps you run that proc only once or week...)
Even if one maintains them only upon request the fact that they are logged, transactional, occupy namespace, ... makes them unwieldy for ad-hoc throw away usage.

As you note T-SQL lags compared to PL/SQL. Amongst other things PL/SQL provides BULK COLLECT INTO and FOR ALL to speed up processing of (as it seems most commonly) VARRAY. VARRAY often (it seems to me) is associated with records. That is procedures/packages declare a record type. Then a VARRAY of this record type is declared which results in a two dimensional matrix of columns and rows (aka a table albeit of the ordered kind, but PL/SQL provides other flavours..)

To fill such a VARRAY posters in this newsgroup encourage the usage of bulk operations. So you essentially run a SELECT query and fill the VARRAY in pretty much one go. In the cases that I have seen that is conceptually pretty much the same as filling a local temp using an INSERT statement.

On the other side FORALL is used to do bulk deletes, updates, inserts using the VARRAY.
With a local temporary table in SQL server this would be UPDATE T .. FROM T, temp, (or MERGE INTO T USING temp in Oracle) DELETE FROM T FROM T, TEMP and (DELETE FROM WHERE EXISTS... in Oracle) INSERT INTO T SELECT .. FROM temp

Yes, there is a difference in that FORALL and BULK COLLECT are batch processes (non atomic) while the SQL statements are atomic. However, at least I have rarely seen exception handling for these constructs in real life.

Now if all that is ever done with VARRAY is to hook up to the client app. I might be inclined to agree that VARRAY and local temps are not comparable - or rather let's say "serving overlapping function". I do however see plenty of procedures use local VARRAY in PL/SQL which appear to do just the same as what local temps do in T-SQL I come across.

Now, perhaps one is faster than the other. I don't know. But since I'm asking a concept question that is secondary. As it stands I think collection objects in Oracle are used as work around for local temp tables and local temp tables are used as work around for the lack of other collection objects such as VARRAY.

Essentially VARRAY provide me with an in memory ordered table. local temps provide me with an in memory (think RAM disk), non logged table.

Obviously I must be missing something crucial given the emotion displayed by some but what?
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab