The most efficient way to perform "mass deletes" is to truncate. If
you have to delete all the records, it's very efficient and
straightforward. If you need to keep only a portion of the existing
records in a table, the way I would try to handle it is by
disabling/dropping the indexes, copying the records to be kept in a
temporary table (and I DON'T mean temporary in the Oracle sense, but
in the conceptual sense), truncate or drop the initial table, and
finally recopy the records from the "temporary" to the initial table.
Use "create table ... as select ... where ..." to move records around.
Truncate the initial table if there is something worth keeping
(grants, triggers, procedures referencing it, extents allocated to it,
constraints, ...), otherwise just drop it. Don't forget to rebuild the
indexes and constraints (if applicable) afterwards.
Daniel
Is their anyway to perform mass deletes (several million records) without
"maxing out" rollback segments?
I'm working on archiving data from an Oracle 8.1.7 database The system is
about 4 years old and no data has EVER been archived /removed. So the
two largest tables contain well over 25 million records!
As part of the archive process I'm deleting out-dated records, but because
of the sheer volume keep receiving "Unable to extend Rollback Segment
errors." I've tried to commit every 50 deletes, I've even tried committing
after
every single delete, but eventually always receive this error. Our DBA
does not want to extend the rollback segments (probably with good reason).
This process will run off-hours, so there's no conflict with end-users.
TIA for any insights you can provide.
Tomas