DA Morgan wrote:
> Serge Rielau wrote:
>> 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).
>
> Excuse me Serge but this isn't the DB2 usenet group. It's over there on
> your right. This is Informix and Oracle's temp table implementation is
> Oracle's.
This has nothing to do with implementation. Semantics dictate
implementation. Nothing you described has anything to do with Orcale's
vs. IDSs (and DB2, and SQL Server's) design.
It is about DECLAREd TEMPS vs. CREATEd TEMPS.
A DECLARE'd temp doesn't have any of that heavy code path overhead you
describe. Whether you call it an index by table or a DGTT or a local temp...

If you could take of those blinders and think of SQL as a language
instead of as a binary shipped by Oracle vs. IBM you could follow me.

Cheers
Serge

PS: There is more to once life choices than Fahrenheit. I'm in the right
spot at the right time.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab