In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-18 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article mainly introduces how to efficiently delete large table historical data in the database, the article is very detailed, has a certain reference value, interested friends must read it!
Clean up large table historical data
The clean up of historical data is accelerated by converting the non-partitioned table Online Redefinition to Partition-ed Table partitioned with delete condition Range range, and then directly Truncate Partiton.
At the same time does not affect the business online.
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 (*)
-
seven hundred and forty eight
The above is all the contents of the article "how to efficiently delete large table historical data in the database". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!
Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.
Views: 264
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.