And indeed this is the type of response I was looking for. An educated one.
Yes. These products are different and Yes we want to use the method(s) that
provide the best performance on each. I am not a temp table advocate for if
there were a better way of doing this rather than using temp tables in
sybase and sql server, then we would have pursued them. The dev team and I
do plan on doing some experimentation, but we are under a time constraint on
the project for one of our customers and really need to get a compiled beta
version out to them for testing. Once they are busy testing, they will be
off our backs for a few weeks giving us enough time to further explore what
Oracle best practices are. We must to the same for DB/2 as well.
Regarding "If you still feel you just gotta have temp
> tables use global temporary tables otherwise performance will suffer
> greatly", I "feel" that we have to use what works best in each respective
environment. It is the functionality of our application that customers want
and it made sense to us to provide good and fast Oracle support in there as
I just don't feel that the Personal Attacks on my software development
skills were warranted. We are all developers and/or DBA folks. Our jobs
are already somewhat thankless. The last thing we need is to attack one
another (not saying you did). Bottom line (and apparently Mr. Bakker fails
to understand this) is that we "developers" typically don't get the time to
do things the correct way because upper management unfortunately and often
times make the decisions.
Although they (mostly) have no experience in Software Engineering or
development processes, they look at "time" and "cost". That's it. How much
time will it take to get "something" out to the customer and how much will
it cost them before they go out and look for another vendor that can do it
for less. When things bogg down and bugs begin to surface (usually do to
the lack of adequate testing time), then it's our (developers) fault. I
just don't need or want the extra headache from folks like Mr. Bakker.
Thank You Jim for your suggestion.
"Jim Kennedy" <kennedy-fam...@attbi.com> wrote in message
> Unlike Sqlserver and Sybase you don't need (or very very rarely) need temp
> tables in Oracle. Yes, in Sybase and Sqlserver you need them and I am
> you are correct that using them in those environments gives you the
> performance in those environments - no arguement. In Oracle however, you
> don't have to go through all those gyrations. (Good approuch possibly for
> SQLServer and Sybase - not a good approuch in Oracle. I am not saying you
> are stupid; I am saying things work differently.) You can use SQL (not
> pl/sql or transact, just sql). If you still feel you just gotta have temp
> tables use global temporary tables otherwise performance will suffer
> greatly. In Oracle readers don't block writers and writers don't block
> readers. It will save you a lot of pain and anguish to try some simple
> experiements in Oracle and see that the differences are great and use
> differences to your advantage. Again, this has nothing to do with your
> intelligence; it is just the products are different and assumptions in one
> are not valid in the other.
> Tom Kyte's book is excellent and well worth the money. His site is at:
> Some useful links:
> That should help.
> "KPK" <kpkel...@alltel.net> wrote in message
> > Thanks for the reply.
> > I'll have you know that I've been writing SQL now since the WatCOM SQL
> > (late 80s-early 90s). I've been writing software since 1981 and started
> > with DBase and moved into Sybase on Unix and then SQL Server and Sybase
> > Windows. I've written scalable sybase and sql server-based applications
> > that are being used by 3 of the top 10 independent Oil and Gas producers
> > the United States. I have also designed and written SQL Server and
> > applications used in plant Safety Systems that are now responsible for
> > saving the lives of many oilfield and hydrocarbon plant workers in the
> > of Mexico region. These apps are not only scalable, but are very fast
> > the database level) in those areas where timing is critical for setting
> > Alarm conditions. Our intentions for this application is not to merely
> > *TACK ON* support for Oracle, but investigate those areas where Oracle
> > (later DB/2) provide the best performance. It just so happens that our
> > tests have shown that both Sybase and SQL Server perform well utilizing
> > *temp table* technique I illustrated. My intentions are not to try and
> > stabb at Oracle, but to acquire valuable technical information from
> > figured) knew more about the product than I currently do. Over the past
> > several months, I've been periodically visiting a few of these Oracle
> > newsgroups and it seems that everytime a developer who previously
> > competing database asks a simple (How To using Oracle) question, they
> > branded as SQL idiots! As if PL/SQL is the ONLY SQL there is. As I
> > Transact SQL has been around for quite sometime as well and is also very
> > successful and well accepted. I happen to like PL/SQL as I've done some
> > programming back in the day. We are merely trying to better understand
> > syntax and the best techniques to use within the Oracle database.
> > why at the end of my posting, I asked "Any Suggestions?" If indeed the
> > table method is not the best approach, then suggest something that is or
> > don't. This application is our company's livelihood and we only hurt
> > company by not optimizing performance on all of the major DB platforms
> > the industry which we serve. Pardon me and all of us non-Oracle
> > trying to better understand and embrace Oracle for using newsgroups in
> > manner which they were meant to be used. I do indeed apologize and I do
> > intend to purchase one of the many great Oracle PL/SQL titles out there.
> > just thought I could reach out to my fellow developers without being
> > chastized for not first reading some book. By providing support for
> > in our application only helps those smaller independents who have not
> > standardized or even utilize any SQL database consider using Oracle
> > their organization. Larry Ellison would be proud. Up until about 3
> > ago, there was virtually NO presence of Oracle in the offshore
> > Now that companies like ours and oilfield consultant firms like the last
> > I worked for suggest Oracle, it is beginning to replace Sybase and SQL
> > Server out there.
> > With regard to your comment:
> > > 2 If you *really* think you can't do without temp tables (and the
> > > example shows you resort to temp tables for simply *everything*)
> > > , check out the Oracle manuals on GLOBAL TEMP tables.
> > I only cited one example of where the temp table (for those platforms)
> > offers the greatest benefit and talked about how we've used them in
> > procedures (for those platforms). Don't have a cow!
> > I have written more than a thousand stored procedures and triggers and
> > use of temp tables in perhaps 10% to 12% of them. I don't know where
> > get these *assumptions*!
> > "Sybrand Bakker" <post...@sybrandb.demon.nl> wrote in message
> > news:email@example.com...
> > > "KPK" <kpkel...@alltel.net> wrote in message
> > > news:firstname.lastname@example.org...
> > > > Hello all:
> > > > We are building support into our Powerbuilder 7.0 application to
> > our
> > > > customers to utilize their Oracle databases (we are testing against
> > > > Right now our application has support for Microsoft SQL Server 7.0
> > > 2000
> > > > as well as Sybase SQL AnyWhere 5.5 as well as Adaptive Server 7.
> > > > (later DB/2 as well) support is being added now.
> > > > In our application, we make use of temporary tables quite a bit.
> > Consider
> > > > the following example:
> > > > Set SQLCA.AutoCommit = TRUE
> > > > Create Table #TempVols(ObjID int, StartOil float, EndOil float)
> > > > Insert Into #TempVols(ObjID, StartOil, EndOil)
> > > > Select Distinct CompletionDaily.ObjectID, Sum(StartOilVol),
> > > > Sum(EndingOilVol)
> > > > From CompletionDaily, TankDaily
> > > > Where
read more »