利用Oracle在线重定义Online Redefinition清理历史数据

我在<了解Oracle在线重定义Online Redefinition>一文中介绍了Oracle在线重定义的特点及其使用步骤，Online Redefinition的适用场景很多，包括:

 

• Modify the storage parameters of a table or cluster
• Move a table or cluster to a different tablespace
• Add, modify, or drop one or more columns in a table or cluster
• Add or drop partitioning support (non-clustered tables only)
• Change partition structure
• Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
• Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
• Add support for parallel queries
• Re-create a table or cluster to reduce fragmentation
• Change the organization of a normal table (heap organized) to an index-organized table, or do the reverse.
• Convert a relational table into a table with object columns, or do the reverse.
• Convert an object table into a relational table or a table with object columns, or do the reverse.

 

但是Online Redefinition恰恰就是不支持对于表上历史数据的清理( 甚至于数据更新update都是支持的)， 对于存有海量数据且没有分区的大表而言历史数据的清理是非常头痛的工作，特别是在7*24 应用不能下线的环境当中， 我们往往无法利用CTAS或INSERT APPEND+NOLOGGING的方式重建表以加速清理工作，而仅能使用最为原始的DELETE DML， 而我们知道DELETE操作是很缓慢的(与之前所提及的方法相比，真实世界中DELETE的效率还会受到INDEX clustering_factor聚集因子等因素的影响而显得更慢，见<SQL调优:Clustering Factor影响数据删除速度一例>)， 且为了避免ORA-01555快照过久的错误出现，我们不能简单地使用一条DELETE SQL来清理数据，而需要使用PL/SQL匿名块控制并定期commit提交以避免ORA-01555。

 

实际上我们还是可以通过将非分区表Online Redefinition转换为以删除条件为Range范围分区的Partition-ed Table，再直接Truncate Partiton的方法来加速历史数据的清理， 同时又不影响业务的在线。

 

例如有如下非分区表一张：

 

create table order_history
(
order_id number primary key,
issue_date date ,
location varchar2(200),
amount number,
maclean varchar2(200),
QUANTITY_SOLD number,
PROMO_ID number,
CUST_ID number,
CHANNEL_ID number) tablespace users pctfree 0;

SQL> select count(*) from ORDER_HISTORY;

  COUNT(*)
----------
   4550092

SQL> select count(*) from ORDER_HISTORY where issue_date< sysdate-365;

  COUNT(*)
----------
   3412569

SQL> exec dbms_stats.gather_table_stats('SH','ORDER_HISTORY');

PL/SQL procedure successfully completed.

 

该表上存有450万条Order记录 ， 其中340万条是一年之前的历史记录需要予以删除。

我们先着手将该非分区表在线冲定义为以issue_date日期为范围的Range Partition。

 

以下我们会创建临时分区表，并完成Online Redefinition：

 

create table order_history_int
(
order_id number primary key,
issue_date date ,
location varchar2(200),
amount number,
maclean varchar2(200),
QUANTITY_SOLD number,
PROMO_ID number,
CUST_ID number,
CHANNEL_ID number)
partition by range(issue_date)
( partition p1 values less than (to_date('2009-01-01','YYYY-MM-DD')) tablespace users,
  partition p2 values less than (to_date('2010-01-01','YYYY-MM-DD')) tablespace users,
  partition p3 values less than (to_date('2011-01-01','YYYY-MM-DD')) tablespace users,
  partition p4 values less than (to_date('2012-01-01','YYYY-MM-DD')) tablespace users,
  partition p5 values less than (maxvalue) tablespace users);

set timing on;

SQL> begin
  2    dbms_redefinition.can_redef_table(uname        => 'SH',
  3                                      tname        => 'ORDER_HISTORY',
  4                                      options_flag => DBMS_REDEFINITION.cons_use_pk);
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

这里采用了DBMS_REDEFINITION.cons_use_pk， 即Primary Key的重定义方式
若没有主键和伪主键，那么也可以选择rowid的方式，当然我们更推荐使用主键

SQL> begin
  2    DBMS_REDEFINITION.START_REDEF_TABLE(uname        => 'SH',
  3                                        orig_table   => 'ORDER_HISTORY',
  4                                        int_table    => 'ORDER_HISTORY_INT',
  5                                        options_flag => DBMS_REDEFINITION.cons_use_pk);
  6  end;
  7  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:25.10 

SQL> select count(*) from ORDER_HISTORY_INT;

  COUNT(*)
----------
   4550092

Elapsed: 00:00:00.24

SQL> DECLARE
  2    num_errors PLS_INTEGER;
  3  BEGIN
  4    DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname            => 'SH',
  5                                            orig_table       => 'ORDER_HISTORY',
  6                                            int_table        => 'ORDER_HISTORY_INT',
  7                                            copy_indexes     => 0,
  8                                            copy_triggers    => TRUE,
  9                                            copy_constraints => FALSE,
 10                                            copy_privileges  => TRUE,
 11                                            ignore_errors    => FALSE,
 12                                            num_errors       => num_errors,
 13                                            copy_statistics  => TRUE);
 14  END;
 15  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.82

SQL> begin
  2    dbms_redefinition.finish_redef_table(uname      => 'SH',
  3                                         orig_table => 'ORDER_HISTORY',
  4                                         int_table  => 'ORDER_HISTORY_INT');
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.87

 

以上仅用了不到2分钟时间就完成了对ORDER_HISTORY历史表的在线重定义，紧接着我们只需要truncate前几年的历史分区即可，因为truncate操作是DDL语句且与DELETE的机制大相径庭，所以速度一般是很快的：

但是请注意若表上有global Index，那么这样可能会导致索引失效，所以建议在Online Redefinition时将全局索引转换为本地索引，或者使用update global indexes;子句

 

 

SQL>  alter table ORDER_HISTORY truncate partition p1 [update global indexes];

Table truncated.

Elapsed: 00:00:00.21

SQL> alter table ORDER_HISTORY truncate partition p2 [update global indexes];

Table truncated.

Elapsed: 00:00:00.07

SQL> alter table ORDER_HISTORY truncate partition p3 [update global indexes];

Table truncated.

Elapsed: 00:00:00.07

SQL> select count(*) from ORDER_HISTORY;

  COUNT(*)
----------
   1137523

Elapsed: 00:00:00.14

SQL> select count(*) from ORDER_HISTORY where issue_date< sysdate-365;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.02

 

 

以上我们通过Oracle在线重定义Online Redefinition特性将非分区的历史数据表在线转化为以日期为范围的分区表，利用分区表truncate partition清理数据的便利性来解决传统以PL/SQL过程游标获取rowid循环方式删除历史数据十分缓慢的问题，同时也保证了业务应用的始终在线。