Using SQL*Plus with Oracle to DELETE data

Question

I have above 60M rows to delete from 2 separate tables (38M and 19M). I have never deleted this amount of rows before and I'm aware that it'll cause things like rollback errors etc. and probably won't complete.

What's the best way to delete this amount of rows?

@DavidAldridge First table is 44M rows and 38M to be deleted. Second table is 22M rows and 19M to be deleted. — user2656114
– user2656114, Commented Dec 2, 2013 at 9:07

ntalbs · Accepted Answer · 2013-12-02 09:26:54Z

1

You can delete some number of rows at a time and do it repeatedly.

delete from *your_table*
where *conditions*
  and rownum <= 1000000

The above sql statement will remove 1M rows at once, and you can execute it 38 times, either by hand or using PL/SQL block.

The other way I can think of is ... If the large portion of data should be removed, you can negate the condition and insert the data (that should be remained) to a new table, and after inserting, drop the original table and rename the new table.

create table *new_table* as
select * from *your_table*
where *conditions_of_remaining_data*

After the above, you can drop the old table, and rename the table.

drop table *your_table*;
alter table *new_table* rename to *your_table*;

answered Dec 2, 2013 at 9:26

ntalbs

29.6k8 gold badges72 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2656114 Over a year ago

Thanks. I think first I'll give the first suggestion a shot and see how that works out. Would doing such a large deletion noticeably slow down the system? I guess that depends on the system?

Armunin Over a year ago

Dont drop the old table, as you will have to create all triggers, indices etc from scratch. Just truncate the old table and insert the retained records... all described in the link given on the original question

ntalbs Over a year ago

@user2656114 Well, if you decided to use delete, be sure to avoid the full table scan.

user2656114 Over a year ago

@ntalbs Can you explain or provide an example of repeating it using using PL/SQL block please?

Collectives™ on Stack Overflow

Using SQL*Plus with Oracle to DELETE data

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related