利用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.
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循环方式删除历史数据十分缓慢的问题,同时也保证了业务应用的始终在线。
posted on 2013-03-19 00:48 Oracle和MySQL 阅读(242) 评论(0) 编辑 收藏 举报