Andy Hassall 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 am not sure as I am not a DBA but my guess is that is done in the
"old" way. We have it configured according to the 3rd party application
specs and know the 3rd party like I do...it wouldn't surprise me at all.
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
Thanks for the advice.
Robert
