Shulou(Shulou.com)06/01 Report--

Bulk batch delete data

1. Introduction to the case series

10 million rows of data need to be deleted in a large table with 100 million rows.

The requirements are completed as quickly as possible with the least impact on other applications of the database.

If the business cannot be stopped, you can refer to the following ideas:

According to ROWID slicing, reusing Rowid sorting, batch processing, table deletion

It is indeed the best way to choose this way when the business cannot stop.

Generally speaking, it can be submitted within every 10,000 lines, which will not cause too much pressure on the rollback segment.

When I make a big DML, I usually choose one or two thousand lines to submit.

Choosing to do it when the business is at a low peak will not have much impact on the application.

two。 Code implementation test environment

Drop table t_emp purge

Create table t_emp as select * from emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Insert into t_emp select * from t_emp

Commit

Specific code version 1. 0

Declare

Cursor c_rowid is

Select rowid from t_emp where deptno = 30 order by rowid;-- data need to be deleted

Type type_rowid is table of rowid index by pls_integer

V_tab_rowid type_rowid

V_num number: = 0

Begin

Open c_rowid

Loop

Fetch c_rowid bulk collect

Into v_tab_rowid limit 50

-- exit when c_rowid%notfound

Forall i in v_tab_rowid.first.. V_tab_rowid.last

Delete from t_emp where rowid = v_tab_rowid (I)

Commit

V_num: = v_num + v_tab_rowid.count

Exit when c_rowid%notfound

Endloop

Close c_rowid

Dbms_output.put_line (to_char (sysdate, 'yyyy-mm-dd:') | |' delete rows'| |

To_char (v_num))

End

/

Version 2.0

Declare

Cursor c_rowid is

Select rowid from t_emp where deptno = 30 order by rowid;-- data need tobe deleted

Type type_rowid is table of rowid index by pls_integer

V_tab_rowid type_rowid

V_num number: = 0

Begin

Open c_rowid

Loop

Fetch c_rowid bulk collect

Into v_tab_rowid limit 50

Exit when v_tab_rowid.count=0

Forall i in v_tab_rowid.first.. V_tab_rowid.last

Delete from t_emp where rowid = v_tab_rowid (I)

Commit

V_num: = v_num + v_tab_rowid.count

Endloop

Close c_rowid

Dbms_output.put_line (to_char (sysdate, 'yyyy-mm-dd:') | |' delete rows'| |

To_char (v_num))

End

/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.