SEO

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

To get around this, your app has to create the temp table, and the index.
This is a royal pain because these DDL auto commit. Meaning that they can
fsck up your ability to roll back a transaction.

__________________________________________________ _______________
Spiderman 3 Spin to Win! Your chance to win $50,000 & many other great
prizes! Play now! http://spiderman3.msn.com

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

Serge Rielau wrote:
> 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.
>

Speaking about limitations / differences...

Are inserts / deletes / updates on Oracle temp tables still logged? I
remember "older versions" and I believe this was so, but I'm not sure as
to whether it was or still is . . . .

JWC

John Carlson wrote:
> Serge Rielau wrote:
>> 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.
>>
>
> Speaking about limitations / differences...
>
> Are inserts / deletes / updates on Oracle temp tables still logged? I
> remember "older versions" and I believe this was so, but I'm not sure as
> to whether it was or still is . . . .
>
> JWC

Global temporary tables have several major benefits:

1. Non-interference between private sets of data.

2. Ease of getting rid of 'scratch' data. In a heap table you either
rollback, or delete it. But in a GTT, you can truncate explicitly,
without affecting anyone else (or allow the implicit "truncate on commit
/ exit" effect to do the same thing).

3. Decreased redo generation as, by definition, they are non-logging.

No decreased ... not zero.

create table reg_tab (
testcol VARCHAR2(100));

CREATE GLOBAL TEMPORARY TABLE gtt_ocd (
testcol VARCHAR2(100))
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE gtt_ocp (
testcol VARCHAR2(100))
ON COMMIT PRESERVE ROWS;

col value format 999999999999

-- get baseline redo value
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a heap table
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO reg_tab
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a GTT with ON COMMIT DELETE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO gtt_ocd
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

-- load 1000 rows into a GTT with ON COMMIT PRESERVE ROWS
BEGIN
FOR i IN 1 .. 1000 LOOP
INSERT INTO gtt_ocp
(testcol)
VALUES
(RPAD('X', 99));
END LOOP;
COMMIT;
END;
/

-- record the redo generated
SELECT value
FROM sys.v_$sysstat
WHERE name = 'redo size';

Description Value Redo Generated
Baseline 254269080 -
Regular Table Run 254605916 336836
On Commit Delete 254742528 136612
On Commit Preserve 254879140 136612

Be sure this is part of the compatibility feature set. <g>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org