數(shù)據(jù)庫(kù)中如何高效率刪除大表歷史數(shù)據(jù)

這篇文章主要介紹數(shù)據(jù)庫(kù)中如何高效率刪除大表歷史數(shù)據(jù)，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

清理大表歷史數(shù)據(jù)
通過(guò)將非分區(qū)表Online Redefinition轉(zhuǎn)換為以刪除條件為Range范圍分區(qū)的Partition-ed Table，再直接Truncate Partiton的方法來(lái)加速歷史數(shù)據(jù)的清理， 
同時(shí)又不影響業(yè)務(wù)的在線。
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
收集統(tǒng)計(jì)信息
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

以上是“數(shù)據(jù)庫(kù)中如何高效率刪除大表歷史數(shù)據(jù)”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！