| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re[2]: newbie question - still: please help
I think Dick is on the right track...
If you have a list of accounts which require an update, load that list into the database - if it isn't already in the database (please don't tell me you are creating the script from within Oracle - please don't tell me that). You could load this list either as a pile of inserts (barely better than the original solution, although at least the critical update command will run fast) or SQL*Load them.
As Dick said, you can then do a single update to the main table.
As far as performance goes... If you are updating a significant proportion of the ISIS_DOCAR table then an index won't be likely to benefit (a single FTS would be used). On the other hand, if only a smallish subset of ISIS_DOCAR is being updated than an index on the ID column could be useful.
Cheers,
Mark.
dgoulet_at_vicr.c
om To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.c Subject: Re[2]: newbie question - still: please help
om
14/01/2003
06:31
Please respond
to ORACLE-L
Daniel,
After some of the younger folks in the audience have had a say, Let's
let
the "old fart" have one.
While on the one hand I can see what your DBA is croaking on, I can
also see
where you are too. Your approach is easy to code, but can wreck hell on
the
database especially if each statement requires a full table scan.
With the DBA's view your update statement turns into a select, followed
by a
delete, and then an insert after you've processed the file. Damn, that's a
lot
of work for a simple update and who's to say that the process handling the
interim data file is bug free as well. As Scotty in Star Trek 3 said "The
more
you overtake the pluming, the easier it is to stop up the drain". What
happens
when part of your application needs the data record in the middle of your
update? Or the business logic changes? Very convoluted pluming, many
tight
drains.
But, having a loop as you do that sets a value to a constant for
several
document id's in series, come on!! Why not put those id's into a global
temp
table (or suitable substitute) and then use an "UPDATE ISIS_DOCAR SET
STATUS =
2000 WHERE ID in (select id from temp_table);" One statement, several
thousand
rows updated. Simple pluming, one very large drain.
An interim solution might be to select your data, which obviously you
did to
fill in 'n', with the "for update clause". Then your update changes into
"UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE current of cursor x;" Less io
on the
system, same result. The pluming gets a little more complicated, but the
drain
is still large & free flowing.
Dick Goulet
Senior Oracle DBA & Oracle Certified 8i DBA
Vicor Corporation
____________________Reply Separator____________________ Author: "Tim Gorman" <Tim_at_SageLogix.com> Date: 1/13/2003 9:40 AM
Daniel,
Your gut reaction is right on-target. It is always a struggle to keep certain folks from killing the entire village while trying to cure a single case of the sniffles. What's worse is that such folks are usually quite bright and talented. After, very few mediocre folks can either cure the sniffles or kill entire villages... ;-)
Longer response: This is a common argument that eventually distills down
to
something like "I don't need a stupid database engine to do this. I can do
it all in (choose one): C, C++, Perl, shell script, Java." What the person
has to realize is that those 'stupid database engines' started out as lone
programmers doing what he is describing but then running into problems such
as transaction recoverability (aka rollback), concurrency, and its close
cousin read-consistency. Oh yeah, and then there is also what my good
friend Gary once called "DFB" or "diddly file build-up" (i.e. an excess of
"diddly files" in a file-system), which very few people see up front but
invariably grows to dominate such approaches. After some decades of effort
by thousands of developers and designers (very few of whom are stupid),
what
results is the modern database engine. Such people who think they can
outperform database engines without losing such crucial features do not
have
any sense of humility about their place in the world. Ask him to skim
through Gray and Reuter's "Principles of Transaction Processing" to gain
some of that humility...
Shorter response: look into using PL/SQL bulk-bind operations (i.e.
FORALL,
BULK COLLECT, etc) instead of one-row-at-a-time processing.
I suspect the latter approach will be more effective... :-)
Hope this helps...and keep up the good work!
-Tim
> hi!
>
> a DBA inteds to speed up a script that is looping and
> sending hundred thousands of sequential update statements like:
>
> UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
> he suggests copying the table to a file, change it and then
> load it into the DB again. i am strongly convinced that this
> is nonsense.
>
> what is the best way to go for a script like this, doing tons of
> updates? (except convincing him to swith to sell burgers)
>
>
> thx
> daniel
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Wisser
> INET: daniel.wisser_at_isis-papyrus.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: Tim_at_SageLogix.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: dgoulet_at_vicr.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: mrichard_at_transurban.com.au Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Jan 13 2003 - 19:11:42 CST
 |
 |