Master's Oracle mass data deletion method

Batch deletion of massive data is usually complicated and slow, and there are many methods, but the general concept is: Batch deletion and successive submission. The following is my deletion process. My data tables can be deleted through the primary key. We have tested the Delete and For all methods. for all does not improve the performance here, therefore, batch deletion is still selected.

First, create a process and use a self-made transaction for processing:
Create or replace PRocedure delBigTab
(
P_TableName in varchar2,
P_Condition in varchar2,
P_Count in varchar2
)
As
Pragma autonomous_transaction;
N_delete number: = 0;
Begin
While 1 = 1 loop
EXECUTE IMMEDIATE
Delete from p_TableName where p_Condition and rownum <=: rn
USING p_Count;
If SQL % NOTFOUND then
Exit;
Else
N_delete: = n_delete + SQL % ROWCOUNT;
End if;
Commit;
End loop;
Commit;
DBMS_OUTPUT.PUT_LINE (Finished !);
DBMS_OUTPUT.PUT_LINE (Totally to_char (n_delete) records deleted !);
End;

The deletion process and time are as follows:
SQL> create or replace procedure delBigTab
2 (
3 p_TableName in varchar2,
4 p_Condition in varchar2,
5 p_Count in varchar2
6)
7
8 pragma autonomous_transaction;
9 n_delete number: = 0;
10 begin
11 while 1 = 1 loop
12 EXECUTE IMMEDIATE
13 delete from p_TableName where p_Condition and rownum <=: rn
14 USING p_Count;
15 if SQL % NOTFOUND then
16 exit;
17 else
18 n_delete: = n_delete + SQL % ROWCOUNT;
19 end if;
20 commit;
21 end loop;
22 commit;
23 DBMS_OUTPUT.PUT_LINE (Finished !);
24 DBMS_OUTPUT.PUT_LINE (Totally to_char (n_delete) records deleted !);
25 end;
26/

Procedure created.

SQL> set timing on
SQL> select min (NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN (NUMDLFLOGGUID)
------------------
11000000

Elapsed: 00:00:00. 23
SQL> exec delBigTab (HS_DLF_DOWNLOG_HISTORY, NUMDLFLOGGUID <11100000,10000 );

PL/SQL procedure sUCcessfully completed.

Elapsed: 00:00:18. 54
SQL> select min (NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN (NUMDLFLOGGUID)
------------------
11100000

Elapsed: 00:00:00. 18
SQL> set serveroutput on
SQL> exec delBigTab (HS_DLF_DOWNLOG_HISTORY, NUMDLFLOGGUID <11200000,10000 );
Finished!
Totally 96936 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18. 61
0.1 million records about 19 s

SQL> exec delBigTab (HS_DLF_DOWNLOG_HISTORY, NUMDLFLOGGUID <11300000,10000 );
Finished!
Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18. 62
SQL> exec delBigTab (HS_DLF_DOWNLOG_HISTORY, NUMDLFLOGGUID <11400000,10000 );
Finished!
Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18. 85
SQL>
SQL> exec delBigTab (HS_DLF_DOWNLOG_HISTORY, NUMDLFLOGGUID <13000000,10000 );
Finished!
Totally 1000000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:03:13. 87

1 million records about 3 minutes
SQL> exec delBigTab (HS_DLF_DOWNLOG_HISTORY, NUMDLFLOGGUID <200020.,10000 );

Finished!
Totally 6999977 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:27:24. 69
7 million, about 27 minutes
The above process is for reference only.