Re: Assistance needed with creating tables in oracle.

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

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-family_at_attbi.com> wrote in message news:OTmD8.19791$Po6.8045_at_rwcrnsc52.ops.asp.att.net...
> 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
sure
> you are correct that using them in those environments gives you the
fastest
> 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
those
> 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:
> http://asktom.oracle.com/pls/ask/f?p=4950:1:
>
> Some useful links:
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:48812348054,%7Bsybase%7D%20and%20%7Btemp%7D
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:1423204060137,%7Bsybase%7D%20and%20%7Btemp%7D
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:1164655862293,%7Bsybase%7D%20and%20%7Btemp%7D
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:734225532321,%7Bsybase%7D%20and%20%7Btemp%7D
>
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:1886476148373,%7Bsybase%7D%20and%20%7Boracle%7D
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:1502605216980,%7Bsybase%7D%20and%20%7Boracle%7D
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:1136552219291,%7Bsybase%7D%20and%20%7Boracle%7D
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:61012348055,%7Bsybase%7D%20and%20%7Boracle%7D
>
>

http://asktom.oracle.com/pls/ask/f?p=4950:8:802762::NO::F4950_P8_DISPLAYID,F
> 4950_P8_CRITERIA:1390203651798,%7Bsybase%7D%20and%20%7Boracle%7D
>
> That should help.
>
> Jim
>
>
>
> "KPK" <kpkeller_at_alltel.net> wrote in message
> news:abkjht$e0b$1_at_iac5.navix.net...
> > Thanks for the reply.
> >
> > I'll have you know that I've been writing SQL now since the WatCOM SQL
> days
> > (late 80s-early 90s). I've been writing software since 1981 and started
> out
> > with DBase and moved into Sybase on Unix and then SQL Server and Sybase
on
> > 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
> in
> > the United States. I have also designed and written SQL Server and
Sybase
> > applications used in plant Safety Systems that are now responsible for
> > saving the lives of many oilfield and hydrocarbon plant workers in the
> Gulf
> > of Mexico region. These apps are not only scalable, but are very fast
(at
> > 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
and
> > (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
> the
> > *temp table* technique I illustrated. My intentions are not to try and
> > stabb at Oracle, but to acquire valuable technical information from
folks
> (I
> > 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
utilized
> a
> > competing database asks a simple (How To using Oracle) question, they
are
> > branded as SQL idiots! As if PL/SQL is the ONLY SQL there is. As I
> recall
> > 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
> ADA
> > programming back in the day. We are merely trying to better understand
> the
> > syntax and the best techniques to use within the Oracle database.
That's
> > why at the end of my posting, I asked "Any Suggestions?" If indeed the
> temp
> > 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
our
> > company by not optimizing performance on all of the major DB platforms
in
> > the industry which we serve. Pardon me and all of us non-Oracle
> developers
> > trying to better understand and embrace Oracle for using newsgroups in
the
> > 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.
> I
> > just thought I could reach out to my fellow developers without being
> > chastized for not first reading some book. By providing support for
> Oracle
> > in our application only helps those smaller independents who have not
yet
> > standardized or even utilize any SQL database consider using Oracle
within
> > their organization. Larry Ellison would be proud. Up until about 3
years
> > ago, there was virtually NO presence of Oracle in the offshore
> environment.
> > Now that companies like ours and oilfield consultant firms like the last
> one
> > 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
above
> > > 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
stored
> > procedures (for those platforms). Don't have a cow!
> > I have written more than a thousand stored procedures and triggers and
> made
> > use of temp tables in perhaps 10% to 12% of them. I don't know where
you
> > get these *assumptions*!
> >
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:udqdq1p88dk5dd_at_corp.supernews.com...
> > >
> > > "KPK" <kpkeller_at_alltel.net> wrote in message
> > > news:abj9vb$lqd$1_at_iac5.navix.net...
> > > > Hello all:
> > > >
> > > > We are building support into our Powerbuilder 7.0 application to
allow
> > our
> > > > customers to utilize their Oracle databases (we are testing against
> 8i).
> > > >
> > > > Right now our application has support for Microsoft SQL Server 7.0
and
> > > 2000
> > > > as well as Sybase SQL AnyWhere 5.5 as well as Adaptive Server 7.
> Oracle
> > > > (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 CompletionDaily.ObjectID = TankDaily.ObjectID and RecordDate =
> > > > '02-02-2002'
> > > > and UserID = 400
> > > >
> > > > Update TankCompletionDaily
> > > > Set BeginningOil = StartOil,
> > > > EndingOil = EndOil
> > > > From #TempVols
> > > > Where ObjID = TankCompID and RecordDate = '02-02-2002' //or whatever
> > date
> > > we
> > > > want
> > > >
> > > > Just ignore the "set sqlca.autocommit = true" line because that is
> > unique
> > > to
> > > > Powerbuilder.
> > > >
> > > > I don't want to use a nested SELECT for each column being updated
> > because
> > > > it's too slow. The #Temp Table method is much faster as I have
> already
> > > > tested it.
> > > >
> > > > What's nice about this, is that I can create it in a stored
procedure
> or
> > > > within Powerbuilder using Dynamic SQL and Powerbuilder's Transaction
> > > Object.
> > > > The stored procedure is a bit faster than doing it in PB, however.
> > > >
> > > > How can I accomplish this same task within 8i using a temp table?
The
> > > > reason for the temp table is that both Sybase and SQL Server handle
> the
> > > temp
> > > > table a bit differently from a normal table.
> > > >
> > > > In what ways you ask?
> > > >
> > > > Well first, when the temp table is created, both databases generate
a
> > > unique
> > > > value and append it to the temp table so that if any other users run
> > this
> > > > code simultaneously, they too will have their own temp table with
> > > different
> > > > values assigned to their current transaction/connection session
> > therefore
> > > > eliminating the possibility of table name conflicts. Once the code
> > above
> > > is
> > > > complete, both databases (after the commit) will delete the temp
> table.
> > > > Another reason for using a temp table is that we have some stored
> > > procedures
> > > > that are quite long. We create the temp table at the beginning of
the
> > > > stored procedure and populate it with ONLY those records that meet
our
> > > > selection criteria and the rest of the stored procedure's queries
and
> > sql
> > > > transactions can make use and even join against that temp table.
> > > >
> > > > Any suggestions?
> > > >
> > > >
> > > >
> > >
> > > 1 Try to learn Oracle, or better still try to learn SQL.
> > > Sucessful one on one ports from Sqlserver to Oracle don't exist,
because
> > > Oracle is not sqlserver sold by a different vendor. If I have to
> > investigate
> > > performance problems on a database which is used for an application
> which
> > > has been ported from Sqlserver to Oracle, that is ALWAYS the root
cause:
> > the
> > > developers involved simply didn't know anything about Oracle. Read the
> > > initial chapters of Tom Kyte's book and you will see this demonstrated
> > into
> > > more detail
> > >
> > > 2 If you *really* think you can't do without temp tables (and the
above
> > > example shows you resort to temp tables for simply *everything*)
> > > , check out the Oracle manuals on GLOBAL TEMP tables.
> > >
> > > Regards
> > >
> > > --
> > > Sybrand Bakker
> > > Senior Oracle DBA
> > >
> > > to reply remove '-verwijderdit' from my e-mail address
> > >
> > >
> >
> >
>
>
Received on Sun May 12 2002 - 08:56:05 CDT