| |
Demand: Oracle Database delete delete heap table ten million common history.
Remove direct impact:
1. may be due to lack of space leading to the final table undo delete to fail;
2. Possible cause undo tablespace overuse, affecting other users normal operation.
Improvement program: Each row will delete 1k submitted once. (This put a big thing split into a number of small things)
NOTE: The following methods to delete all of the records prior to 2014, for example, depending on your situation changes, to prevent misuse.
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 ( '2014-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;
Method 1 variable Explanation:
[Del_cursor] cursor name
[Table_name] you want to delete the data table
Time Field Name [time_stamp] Your filter on table
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 ( '2014-01-01', 'yyyy-mm-dd');
for i in 1..TRUNC (delete_ct) +1
loop
delete [table_name] where [time_stamp] < to_date ( '2014-01-01', 'yyyy-mm-dd') and rownum < = maxrows;
commit;
end loop;
end;
Method 2 variable Explanation:
[Table_name] you want to delete the data table
Time Field Name [time_stamp] Your filter on table
Note
The core idea of the two methods is a big thing to split into several small things Either way, it is recommended to test in the corresponding test environment and then consider whether you can use in the actual production.
Incidentally, such a large table should be considered whether under modified ingredients zone tables. |
|
|
|