Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to delete large table historical data efficiently in database

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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report