Ian Michael Gumby wrote:
>> From: Serge Rielau <srielau@ca.ibm.com>
>> > The OP's statement most likely stems from not understanding the
>> > differences between the two products.
>> I don't think so....
>> Here is my take:
>> The advantage of session-local temporary tables, that is tables who's
>> definition is not persisted in the catalog has the advantage that ad-hoc
>> tables can be created quickly without impacting the catalog and without
>> a care whether some other session may have a table with the same name
>> (but a different signature)
>> The downside of this behavior is that it's somewhat challenging to use
>> these kinds of tables across multiple objects because there is no
>> guarantee that the procedure that is trying to use Temp1 actually gets
>> Temp1 in the shape it expects it to be.
>
> You're going to have to be a bit more specific in how you define
> "object". "Object" has different meanings to different people....
>
> The disadvantage that you state is kind of meaningless in practice. If
> you think about it, the temp table is only persistant during a
> connection. So that the calling app that instantiated the connection
> should know how or what is defined by the temp table. And the name temp1
> has to be unique per connection. (Meaning that connection A can create a
> temp1 table and connection B can create a temp table temp1 but they will
> be different objects...)
>
> The huge problem with Oracle's temp tables is that their definition isnt
> temp, its global. What is temporary is the data that you can maintain in
> the temp will last only as long as the session. So what happens if I
> want to load in 300,000 rows of temp data in to the temp table and
> there's no index on the table? (Hint: SEQUENTIAL SCAN OF THE TABLE). In
> Oracle, you can't create an index on a temp table if there are any rows
> in it, and you can't control who/what someone else does to the temp table.
Interesting. I didn't know Oracle had this funny limitation.
Either way that limitation is not core to the "concept" of a CREATEd
temporary table.

When you use a session local (DECLAREd) temporary table (whether it's
defined the IDS or DB2 way doesn't matter) the rest of the application
has to be compiled on the spot. (because each session and each
invocation for that matter) may see a different temp table. In reality
however it does not. In reality when you have n-users running the same
app each and everyone of of those will use the exact same temporary
table definition. So sharing that definition and formalizing it in the
catalogs is not a bad thing.

Having experience with both kinds of tables I see that both types have a
raison d'etre. Fighting about which one is better is like arguing
whether submarines are better than airplanes.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab