Re: How to Create Local Temporary Table

From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Fri, 21 Apr 2006 00:01:24 -0400
Message-ID: <4ar3otFuhld5U1@individual.net>

Brian Peasland wrote:

>> Further more, as I presume we will all readily agree the hordes of 
>> application developers don't know squat about SQL and run scared of a 
>> three way join, never mind what Oracle calls an inline view (aka 
>> nested subquery). 

> Agreed, but that's what the DBA is for! :)
... who is hopelessly outnumbered and can't fix more than the worst offenders in my experience ;-)
...

> If I understood you correctly, temp tables in SQL Server live only in
> memory. If that is correct, then this is a big difference between the
> two RDBMS's. How Oracle implements temp tables is not the same, as such
> the developer utilizing temp tables in Oracle needs to know this and
> should consider other alternatives.

See that's the trick. I got that spoon fed from ex Sybase DBA's who are begging for local temp tables in DB2 (and I'm far from convinced I really got it ;-):
This special temp tablespace is _typically_ placed on a RAM disk (I hadn't heard this words since my Commodore Amiga days ...) because it doesn't survive a restart of the server anyway. This way everything is pinned in memory. It's not a Sybase/SQL Server feature as such. It's best practices that apparently works very well for them.

Anyway, thanks for the explanations. This was helpful.

Cheers
Serge

-- 
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Received on Thu Apr 20 2006 - 23:01:24 CDT