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

You see daniel, this is the main difference between competing databases.
Its a design issue.

If you talk to anyone about temp tables, one group will say that temp
implies that they will only be instantiated for the duration of the
connection or context.

Another group will say that its more efficient to persist the object (the
table itself) but not persist the data within the object.

Its design issues like this that will effect scalability and security.

I do support the major dbs and work with what tools I'm told that I can use.

__________________________________________________ _______________
More photos; more messages; more storage—get 5GB with Windows Live Hotmail.
http://imagine-windowslive.com/hotma...M_mini_2G_0507

david (AT) smooth1 (DOT) co.uk wrote:
They are global, in that they are defined by someone with DBA
privileges, and the definitions are shared across all user sessions.
Different user sessions can then use them, and their specific data
extents are then local to that particular session, and can be temporary
(or can be persisted if required). So average joe blow user session
cannot create them.

There has always been some discussion about which approach is more
"correct". Obviously we at Oracle think this approach is better, for a
number of really good reasons. We could also do temp tables the same way
as informix and SQL Server, but have chosen to not implement them yet,
also for a number of good reasons. It does make migration from these
databases to Oracle a little problematic however

Apologies - I now see that I glommed into the thread a little late.
Something to do with a red-eye back from Argentina and leaping before I
look. Serge's description of the differences is a good one.

DA Morgan wrote:
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

Ian Michael Gumby wrote:
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

Mark Townsend wrote:
mismatch is a challenge.

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

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

My point was that you were responding in an Informix group by discussing
DB2. Had your response been about Informix I'd not have pointed out
where the discussion was taking place.

I don't open up discussions here about Oracle ... those who are regulars
here do. In the same way I think the same should apply to DB2. Don't
bring it up unless they do. This is, after all, their group, not ours.
My entry into this thread was that we have had Apple twice cozy up to
Oracle just to walk away and orphan those who used their hardware and
OS, however great, and I didn't want those here to not have a bit of
that history to consider. What DB2 has to do with IDS and Mac? I still
can't make the connection.

BTW: Obnoxio ... likely be in B'ham first week of December. I think I
still owe you a drink if you are in the neighborhood.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

DA Morgan said:
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.

On Sun, 2007-10-28 at 19:52 +0000, Obnoxio The Clown wrote:
Oy, What's wrong with Brum...
--
S. Anthony Sequeira
++
Oh, well, I guess this is just going to be one of those lifetimes.
++