Re: IDS on a Mac?

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?

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. The OP's statement most likely stems from not understanding the differences between the two products. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

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

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. >> 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) True. But consider the improved efficiency of not running the DDL in the first place. Consider that in Oracle the DDL is run one time during schema creation and never run again. A substantially lower overhead than 300,000 people simultaneously connected and all running essentially identical DDL to create 300,000 essentially identical tables. If everyone, as in Oracle, can use the same table without any chance of corrupting or altering another session's data no need for more than one. > CREATED temporary tables on the other hand provide the same certainty > about the table's signature as persistent tables. With a lot of overhead running the DDL to create and then drop them. > So how does Oracle get around this downside? > > Cheers > Serge You'll have to ask Mark. <g> While you're at it ask him for a job. Tomorrow's forecast: Redwood Shores 76 and sunny Toronto 58 and raining And it is only going to get a lot worse. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org

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

>From: DA Morgan <damorgan@psoug.org> 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/hotmail/?locale=en-us&ocid=TXT_TAGHM_migration_HM_mini_2G_0507

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? > 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

>> How do Oracle temp tables work? What is the problem with them? >> > > 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: > 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

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. 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: >>> How do Oracle temp tables work? What is the problem with them? >>> >> >> 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. Tx, so is yours. Also agree on the migration issue. The impedance mismatch is a challenge. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab

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