Re: newbie question - still: please help
thanks a lot 4 your replies. /*i followed the way dick and mark suggested.*/ daniel -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Wisser INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: newbie question - still: please help
Even with this little bit of information, it's obvious your DBA is on the right track. Any script that loops and executes 'hundred thousands of sequential update statements' is total nonsense. This will bring most any database to it's knees in a hurry. Oh yes, I've had developers pull this one on me. How about a 10gig text file of INSERT statements? Please supply more info, but from what you have here, I would say you should start listening to your DBA. Jared On Monday 13 January 2003 08:14, Daniel Wisser wrote: > 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: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: newbie question - still: please help
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 - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, January 13, 2003 9:14 AM > 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: newbie question - still: please help
>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 Would be curious to know your DBA's background. The most reasonable thing might be to size rollback segments as needed, and remove the loop (I guess your loop is here to enable you to commit regularly). The second best option would be (8.1.5 and over) to load PL/SQL arrays and do bulk updates (refer to the PL/SQL doc for bulk updates). Files have their use, but not this one. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroul INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: newbie question - still: please help
Daniel - Can you explain the two alternatives in a little more detail, especially the difference between the two. What types of systems are involved (Unix, Windows)? Thanks. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, January 13, 2003 10:14 AM To: Multiple recipients of list ORACLE-L 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
