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

142次閱讀

共計(jì) 2114 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。

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

清理大表歷史數(shù)據(jù)
通過將非分區(qū)表 Online Redefinition 轉(zhuǎn)換為以刪除條件為 Range 范圍分區(qū)的 Partition-ed Table，再直接 Truncate Partiton 的方法來加速歷史數(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)注丸趣 TV 行業(yè)資訊頻道！

正文完