Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Assistance needed with creating tables in oracle.

Re: Assistance needed with creating tables in oracle.

From: Jim Kennedy <kennedy-family_at_attbi.com>
Date: Sun, 12 May 2002 05:15:26 GMT
Message-ID: <OTmD8.19791$Po6.8045@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 - 00:15:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US