I have an Oracle table that has over 1.8 Billion rows, I need to delete up to 260 million of these rows.
I am a Microsoft SQL DBA, so not fully comfortable with the Oracle syntax and best practices for this kind of process.
Any recommendations and advice would be appreciated.
With 1.8 billion rows you really should have EE. By far the best method is to recreate the table without the rows you want to delete. Formatting new blocks, especially direct path, is much faster than updating existing ones. Deleting rows requires more redo, more undo, current block reads, index maintenance, bitmap space management, possible concurrency issues (locks, ITL availability, etc..), possibly trigger or foreign key lookups, etc... Oracle does a ton of work for updates and deletes. Whereas, in creating a new segment and loading it (CTAS), most of that is bypassed.
The difference is so great than you'd have to be deleting a very tiny percent of the table to make an actual DELETE operation worth it. You're talking 15%. That's far over the threshhold for preferring CTAS.
Basic concept is:
CREATE TABLE mytable$new PARALLEL (DEGREE 16) AS SELECT * FROM mytable WHERE [rows to keep];
/* script out and create any indexes, constraints, triggers, grants, etc.. on mytable$new */
ALTER TABLE mytable RENAME TO mytable$old;
ALTER TABLE mytable$new RENAME TO mytable;
If you absolutely don't want to go through all the scripting for indexes, constraints, grants, etc... you might opt to move the data twice so that it ends up in the original segment, though that adds more time:
CREATE TABLE mytable$old PARALLEL (DEGREE 16) NOLOGGING AS SELECT * FROM mytable WHERE [rows to keep];
TRUNCATE TABLE mytable;
ALTER SESSION ENABLE PARALLEL DML;
insert /*+ append parallel(16) */ INTO mytable SELECT * FROM mytable$old;
COMMIT;
DROP TABLE mytable$old;
At least this is how I'd do it in EE. I don't believe the parallelism will work without EE, but the approach should be the same even without parallelism.
CTAS with NOLOGGING is a very good option for deleting ~15% of a big table.
Export + import with QUERY parameter is another, a very similar concept.
Both methods require additional storage space and application downtime because of the unavailability of the table.
Other methods would take much longer or require Enterprise Edition features.
