Re: How to Create Local Temporary Table

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Fri, 21 Apr 2006 02:07:33 GMT
Message-ID: <Iy1v8q.JH7@igsrsparc2.er.usgs.gov>

>> 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).

Partially, I was referring to functions readily available in SQL like CUME_DIST, RANK, and DENSE_RANK. Without these functions, I can see a developer storing data into a temp table on the first pass and then going through the result set for a second pass.

> 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).

Agreed, but that's what the DBA is for! :)

> 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.

I don't mind if you defend DB2. In fact, I expect you to. It is obvious you are not only an IBM employee, but that you work specifically on DB2. So I know that you have oodles more expertise with that product than I do. I was just letting you know that I am open to the idea that DB2 has things in its SQL language that I may not be aware of.

>> 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.

Exactly.

> 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.

I would hope not, but then you never know what someone might try to do! :)

> 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.

I guess I would note the difference is that the VARRAY is wholely contained in memory whereas the temp table can incur I/O. That I/O may (or may not) be costly for performance. So while the end result may be the same, how they got there may not.

> 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.

I think that's part of the point that others in this thread have tried to state, but maybe not exactly to the point that you have done. With these other things, you would not use a temp table, primarly because it can incur I/O. As Daniel said, "We use arrays in Oracle for performance". And as Sybrand said, "In Oracle you don't NEED temporary tables." The point that they're trying to make is that if you used to rely on temp tables in another RDBMS, in Oracle, look to something else and you may be better served.

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

In Oracle, temp tables live in the buffer cache, but they also live in the TEMP tablespace. When you first create a temp table in Oracle, no space is allocated. The temp table is declared and defined in the Data Dictionary. On the first INSERT operation of the temp table, space is allocated in the user's TEMP tablespace. Temp tables, because the data is temporary, do not incur transaction logging like other tables. So some I/O is saved here, but Oracle does generate undo (I/O in the undo tablespace) and redo for the undo (I/O in the online redo logs) for DML on temp tables.

If I understood you correctly, temp tables in SQL Server live only in memory. If that is correct, then this is a big difference between the two RDBMS's. How Oracle implements temp tables is not the same, as such the developer utilizing temp tables in Oracle needs to know this and should consider other alternatives.

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

Received on Thu Apr 20 2006 - 21:07:33 CDT