Hi all, I'm not an Oracle person by ANY stretch of any imagination, but with SQL Server, you can insert the "new" records into a new table, drop the old table, and re-name the new table- thereby eliminating stuff in the rollback transaction log.... Dunno if this is at all possible w/ Oracle, just wondering if it's a valid course of action.
KC On 1/18/06, Andy Hassall <[EMAIL PROTECTED]> wrote: > > > I have been handed a couple of requests to change some data. One is an > > update on 3.6 million records and the other is two different > > deletions of > > over 16 million rows of data. I need to be able to do these > > in batches as I > > am told that Oracle will "blow up" if I do them in one shot. > > "Blow up" in what way? Multi-million row deletes and updates are entirely > possible if Oracle is properly configured. More important than the number > of > rows is the actual byte size of updates you're making - how much are you > processing? Unless you're pushing into the several gigabyte range, or > there's significant concurrent activity on the same data by other users > whilst you're processing it, or it's a very resource-constrained database, > it's usually possible to do it in one shot. The main thing to consider is > undo space (or rollback space if on an older version of Oracle, or 9i > configured the "old" way). > > > I would like to do them in increments of 50,000 then a > > commit...then the > > next 50,000 and then another commit. > > Incremental commits carry their own risk - the infamous "snapshot too > old", > where undo information required to reconstruct the read-consistent view > from > the start of the query is overwritten; assuming you're using a select > statement to work out which rows to update or delete. > > > What it is the best way to do this? > > As a single statement if possible, only resorting to more complicated > measures if you actually know you need them. > > If you're sure you actually do then it depends on how you're determining > which rows to update or delete - whether you have a select query > identifying > the rows, or whether you can limit it with "rownum". If you're driving > from > a select statement, you'll want to use bulk features - such as BULK > COLLECT > in PL/SQL. > > -- > Andy Hassall :: [EMAIL PROTECTED] :: http://www.andyh.co.uk > http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool > >
