oracle定时清理历史数据,如何高效率删除大表历史数据

清理大表历史数据

通过将非分区表Online Redefinition转换为以删除条件为Range范围分区的Partition-ed Table，再直接Truncate Partiton的方法来加速历史数据的清理，

同时又不影响业务的在线。

create table order_history as select * from dba_objects;

select count(*) from order_history;

select count(*) from order_history where created < sysdate-365;

COUNT(*)

———-

49983

收集统计信息

exec dbms_stats.gather_table_stats('SCOTT','ORDER_HISTORY');

create table order_history_int(

OWNER                                              VARCHAR2(30),

OBJECT_NAME                                        VARCHAR2(128),

SUBOBJECT_NAME                                     VARCHAR2(30),

OBJECT_ID                                          NUMBER,

DATA_OBJECT_ID                                     NUMBER,

OBJECT_TYPE                                        VARCHAR2(19),

CREATED                                            DATE,

LAST_DDL_TIME                                      DATE,

TIMESTAMP                                          VARCHAR2(19),

STATUS                                             VARCHAR2(7),

TEMPORARY                                          VARCHAR2(1),

GENERATED                                          VARCHAR2(1),

SECONDARY                                          VARCHAR2(1))

partition by range(created)

( partition p1 values less than (to_date('2010-01-01','YYYY-MM-DD')) tablespace users,

partition p2 values less than (to_date('2011-01-01','YYYY-MM-DD')) tablespace users,

partition p3 values less than (to_date('2012-01-01','YYYY-MM-DD')) tablespace users,

partition p4 values less than (to_date('2013-01-01','YYYY-MM-DD')) tablespace users,

partition p5 values less than (maxvalue) tablespace users);

begin

dbms_redefinition.can_redef_table(uname        => 'SCOTT',

tname        => 'ORDER_HISTORY',

options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);

end;

/

begin

DBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'SCOTT',

orig_table   => 'ORDER_HISTORY',

int_table    => 'ORDER_HISTORY_INT',

options_flag => DBMS_REDEFINITION.cons_use_rowid);

end;

/

SQL> select count(*) from ORDER_HISTORY_INT;

COUNT(*)

———-

50731

DECLARE

num_errors PLS_INTEGER;

BEGIN

DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => 'SCOTT',

orig_table       => 'ORDER_HISTORY',

int_table        => 'ORDER_HISTORY_INT',

copy_indexes     => 0,

copy_triggers    => TRUE,

copy_constraints => FALSE,

copy_privileges  => TRUE,

ignore_errors    => FALSE,

num_errors       => num_errors,

copy_statistics  => TRUE);

END;

/

begin

dbms_redefinition.finish_redef_table(uname      => 'SCOTT',

orig_table => 'ORDER_HISTORY',

int_table  => 'ORDER_HISTORY_INT');

end;

/

alter table ORDER_HISTORY truncate partition p1;

alter table ORDER_HISTORY truncate partition p2;

alter table ORDER_HISTORY truncate partition p3;

SQL> select count(*) from ORDER_HISTORY;

COUNT(*)

———-

748