2-3 minutes of data deletion

This statement is based on ROWID

Create or replace procedure delBigTab
(
P_TableName in varchar2,
P_Condition in VARCHAR2
)
AS
Type mycur is ref cursor;
V_cur mycur;
V_cur_ SQL VARCHAR2 (2000 );
L_ SQL VARCHAR2 (2000 );
Pragma autonomous_transaction;
N_delete number: = 0;
Type v_rowid is table of varchar2 (100) index by binary_integer;
Var_rowid v_rowid;
BEGIN
V_cur_ SQL: = 'select rowid from '| p_TableName | 'where' | p_Condition | 'order by rowid ';
OPEN v_cur FOR v_cur_ SQL;
LOOP
FETCH v_cur BULK COLLECT
INTO var_rowid LIMIT 20000;
FORALL I IN 1 .. var_rowid.count
/* Delete from datasync_prc.ax_log_mail where rowid = var_rowid (I );*/
Execute immediate 'delete from' | p_TableName | 'where rowid =: 1' USING var_rowid (I );
COMMIT;
Exit when v_cur % notfound or v_cur % notfound is null;
End loop;
CLOSE v_cur;
End;

 

The second one is to limit the amount of data to be deleted based on rownum.

Create or replace procedure delete_big_table (pi_table_name in varchar2, pi_condition in varchar2) is
Pragma autonomous_transaction;
Lv_delete_ SQL varchar2 (2000 );
Lv_select_ SQL varchar2 (2000 );
Ln_delete_num number;
Type mycur is ref cursor;
V_select_cur mycur;
V_delete_cur mycur;
Begin
Ln_delete_num: = 0;
Lv_select_ SQL: = 'select count (1) from' | trim (pi_table_name) | 'where' | pi_condition;
Open v_select_cur for lv_select_ SQL;
Fetch v_select_cur into ln_delete_num;
Close v_select_cur;

Lv_delete_ SQL: = 'delete' | trim (pi_table_name) | 'where rownum <=: num_count and '| pi_condition;

While 1 = 1 loop
Execute immediate lv_delete_ SQL using 2000;
Ln_delete_num: = ln_delete_num-2000;
Commit;
Exit when ln_delete_num <= 0;
End loop;

End delete_big_table;

 

Test Deletion

TABLE_NAME MAX (NUM_ROWS)

Temp_wund_action 2328150

Set

Temp_wund_action: copy the following two tables

Analyze table temp_wund_action_01 compute statistics;

Analyze table temp_wund_action_02 compute statistics;

And perform analysis

TABLE_NAME MAX (NUM_ROWS)

Temp_wund_action_02 2315417
Temp_wund_action_01 2315417

 

Run

Execute delete_big_table ('temp _ WUND_ACTION_01 ', '1 = 1 ');

166.625 seconds

Execute delBigTab ('temp _ WUND_ACTION_02 ', '1 = 1'); 116.141 seconds