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