DA Morgan wrote:
> david@smooth1.co.uk wrote:
>> On 18 Oct, 16:29, "Ian Michael Gumby" <im_gu...@hotmail.com> wrote:
>>
>>> BTW, when will Oracle get their act together and do temp tables right?
>>> Anyone who's had to suffer through their bastardized "global" temp
>>> tables
>>> can appreciate that a *real* database allows users to create temp
>>> tables on
>>> the fly as part of their adhoc queries.
>>>
>>> __________________________________________________ _______________
>>
>> How do Oracle temp tables work? What is the problem with them?
>
> In Oracle the tables are not temporary ... no need for them to be due to
> the difference in locking and transaction architecture. Rather it is the
> data within them that is transitory.
>
> There are two types of temp tables in Oracle ... the first for example:
>
> CREATE GLOBAL TEMPORARY TABLE gtt_zip2 (
> zip_code VARCHAR2(5),
> by_user VARCHAR2(30),
> entry_date DATE)
> ON COMMIT DELETE ROWS;
>
> does precisely what the syntax indicates. The second has a different
> behavior:
>
> CREATE GLOBAL TEMPORARY TABLE gtt_zip3 (
> zip_code VARCHAR2(5),
> by_user VARCHAR2(30),
> entry_date DATE)
> ON COMMIT PRESERVE ROWS;
>
> and empties itself at the end of a session.
>
> The advantages of Oracle's version of temp tables relates specifically
> to Oracle's use of undo segments and multiversion read consistency and
> would make no sense in Informix thus I can understand the attitude. In
> Oracle building Informix-type temp tables would be similarly bad design.
Huh? DB2 for zOS has the same kind of temp tables (they are in the SQL
Standard actually).

> 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.
To underline the challenge SQL Server 7 had some issues there where a
procedure would happily read columns as e.g. integer that were really
varchar because the temp was dropped and recreated differently between
two invocations...

CREATED temporary tables on the other hand provide the same certainty
about the table's signature as persistent tables.
Further, because they are persistently defined there is no need to
ensure teh table is actually declared in a given session. One can just
INSERT/UPDATE/SELECT from the table. It typically gets instantiated on
first reference.
The downside is (there is always a downside...) that it's a really bad
idea to create and destroy these tables ad-hoc.

So how does Oracle get around this downside? PL/SQL collections (INDEX
BY TABLES, ...) as storage fro temporary objects, BULK COLLECT and
FORALL for INSERT and SELECT into them.

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

DA Morgan said:
> BTW: Obnoxio ... likely be in B'ham first week of December.

You are a much, MUCH braver man than I.

--
Bye now,
Obnoxio

"I'm astonished anyone pays real money for this crap."
-- Cosmo

"Cluster in my trousers"
-- Guy Bowerman

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

DA Morgan wrote:
> You and Mark seem to have a bit of a disagreement with respect to the
> proper implementation. No doubt that will be resolved with new
> "compatibility" features.
There it is again "proper implementation".
What is a proper vehicle? A plane or a submarine?

FWIW, Mark and I agree.

Count to 10, ok?
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Obnoxio The Clown wrote:
> DA Morgan said:
>> BTW: Obnoxio ... likely be in B'ham first week of December.
>
> You are a much, MUCH braver man than I.

Scotch helps.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Ian Michael Gumby said:
>
>>From: DA Morgan <damorgan@psoug.org>
>
>>Obnoxio The Clown wrote:
>> > DA Morgan said:
>> >> BTW: Obnoxio ... likely be in B'ham first week of December.
>> >
>> > You are a much, MUCH braver man than I.
>>
>>Scotch helps.
>>--
>>Daniel A. Morgan
>
> I can't believe that OTC is afraid of a place like B'ham.
> After all, it can't be worse than Jo Berg....

I've never been the same since I went to Alderley Edge ... (

--
Bye now,
Obnoxio

"I'm astonished anyone pays real money for this crap."
-- Cosmo

"Cluster in my trousers"
-- Guy Bowerman

--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), and is
believed to be clean.

On Sun, 2007-10-28 at 19:52 +0000, Obnoxio The Clown wrote:
>
> DA Morgan said:
> > BTW: Obnoxio ... likely be in B'ham first week of December.
>
> You are a much, MUCH braver man than I.

Oy, What's wrong with Brum...
--
S. Anthony Sequeira
++
Oh, well, I guess this is just going to be one of those lifetimes.
++