Oracle deletes records in a table in batches

When Oracle deletes a large number of records in a table in batches, batch deletion is often used and is submitted one by one for three reasons:

When Oracle deletes a large number of records in a table in batches, batch deletion is often used and is submitted one by one for three reasons:

When Oracle deletes a large number of records in a table in batches, batch deletion is often used and is submitted one by one for three reasons:
1. Avoid the impact on select of other transactions
If other transactions need to query the records to be deleted, they need to go to the undo segment to query the pre-image. Batch update can reduce the row Update time to reduce this situation.
2. Avoid dml lock wait for each transaction
If other transactions are performing dml operations on these records to be deleted, mutual row lock waits may occur. batch locking can reduce the row locking time to reduce this situation.
3. Reduce the impact of using temporary tablespace on Performance
During Association deletion, sort or hash may be used to perform operations on a large number of records at a time. If sort_area_size or hash_area_size is not enough, temporary tablespace will be used and the performance will be reduced. batch operations can reduce the number of records for a single operation to reduce this situation.

The following are some code for batch deletion and batch submission. You can test and modify the code based on your actual situation.

-- Delete records in a non-associated single table by conditions
Declare
N_count number;
N_rownum number: = 10000;
Begin
Select count (*) into n_count from tb_detail where createdate For I in 1 .. ceil (icount/irownum) loop
Delete from tb_detail
Where createdate Commit;
End loop;
End;

-- Conditional deletion of associated tables
Declare
Type ridArray is table of rowid index by binary_integer;
Type dtArray is table of varchar2 (50) index by binary_integer;
V_rowid ridArray;
V_fid_to_delete dtArray;
N_delete number;
N_rownum number: = 10000;
Begin
Select count (*)
Into n_delete
From tb_main
Where createdate <to_date ('20140901', 'yyyymmdd ');
For I in 1 .. ceil (n_delete/n_rownum) loop
Select fid, rowid BULK COLLECT
INTO v_fid_to_delete, v_rowid
From tb_main
Where createdate <to_date ('20140901', 'yyyymmdd ')
And rownum <= n_rownum;
Forall j in 1 .. v_fid_to_delete.COUNT
Delete from tb_detail where fid = v_fid_to_delete (j );
Forall k in 1 .. v_rowid.COUNT
Delete from tb_main where rowid = v_rowid (k );
Commit;
End loop;
End;

-- Delete a subtable or primary table from an associated table based on conditions.
Declare
Type dtArray is table of varchar2 (50) index by binary_integer;
V_fid_to_delete dtArray;
N_delete number;
N_rownum number: = 10000;
Begin
Select fid BULK COLLECT
INTO v_fid_to_delete
From tb_main
Where createdate <to_date ('20140901', 'yyyymmdd ');
For I in 1 .. ceil (v_fid_to_delete.COUNT/n_rownum) loop
Forall j in (I-1) * n_rownum + 1 .. least (I * n_rownum, v_fid_to_delete.COUNT)
Delete from tb_detail where fid = v_fid_to_delete (j );
Commit;
End loop;
End;

This article permanently updates the link address:

,