How to Delete more than 10 million normal heap table data from Oracle Database Delete

How to Delete more than 10 million normal heap table data from Oracle Database Delete

Requirement: delete an Oracle database to delete 10 million historical records from a common heap table.

Impact of direct deletion:

1. The deletion may fail due to insufficient undo tablespace;

2. The undo tablespace may be excessively used, affecting the normal operation of other users.

Improvement Solution: submit each delete 1 K rows. (This splits a big thing into several small things)

Note: The following method takes deleting all records before January 1, 2014 as an example. Modify the settings based on your actual situation to prevent misoperation.

Method 1

Declare
Cursor [del_cursor] is select a. *, a. rowid row_id from [table_name] a order by a. rowid;
Begin
For v_cusor in [del_cursor] loop
If v_cusor. [time_stamp] <to_date ('2017-01-01 ', 'yyyy-mm-dd') then
Delete from [table_name] where rowid = v_cusor.row_id;
End if;
If mod ([del_cursor] % rowcount, 1000) = 0 then
Commit;
End if;
End loop;
Commit;
End;

Variable description in Method 1:

[Del_cursor] cursor name

[Table_name] Name of the table to be deleted

[Time_stamp] Name of the time field of the table you used as the filter Condition

Method 2

Declare
Maxrows number default 1000;
Delete_ct number default 0;
Begin
Select count (1)/maxrows into delete_ct from [table_name] where [time_stamp] <to_date ('2017-01-01 ', 'yyyy-mm-dd ');
For I in 1 .. TRUNC (delete_ct) + 1
Loop
Delete [table_name] where [time_stamp] <to_date ('1970-01-01 ', 'yyyy-mm-dd') and rownum <= maxrows;
Commit;
End loop;
End;

Variable description in Method 2:

[Table_name] Name of the table to be deleted

[Time_stamp] Name of the time field of the table you used as the filter Condition

Note

The core idea of the two methods is to split a big thing into several small things, no matter which method is used, we recommend that you test in the corresponding test environment before considering whether it can be used in actual production.

By the way, whether such a large table can be transformed into a partition table should be considered comprehensively.