Oracle large table cleanup truncate... reusestorage

Oracle large table cleanup truncate... reusestorage

Recently, a large table needs to be cleaned up, which requires no impact on performance. In MySQL, we can minimize I/O by using coreutils and hard links. Oracle can also minimize I/O by recycling space in batches. What is the problem.

1. truncate table syntax

TRUNCATE TABLE [schema_name.]table_name  [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]  [ DROP STORAGE | REUSE STORAGE ] ;

-- Only the description of reuse storage is listed below. reuse storage Specify reuse storage to retain the space from the deleted rows allocated to the table. storage values are not reset to the values when the table was created. this space can subsequently be used only by new data in the table resulting from insert or update operations. this clause leaves storage parameters at their current settings. this setting is useful as Alternative to deleting all rows of a very large table-when the number of rows is very large, the table entails contains thousands of extents, and when data is to be reinserted in the future. truncate table with reuse storage performs several orders of magn1_faster than deleting all rows, but has the following drawbacks :? You cannot roll back a truncate table statement .? All cursors are invalidated .? You cannot flash back to the state of the table before the truncate operation. this clause is not valid for temporary tables. A session becomes unbound from the temporary table when the table is truncated, so the storage is automatically dropped. if you have specified more than one free list for the object you are truncating, then the reuse storage clause also removes any mapping of free lists to instances and resets the high-water mark to the beginning of the first extent.

Ii. Demonstration of truncate table .. reuse storage (11g)

SQL> select * from v $ version where rownum = 1; BANNER orders Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit ProductionSQL> create table tb_reuse as select * from dba_objects; Table created. SQL>/-- run the 37200896 rows created command multiple times. SQL> create table tb_noreuse as select * from tb_reuse; Table created. SQL> select count (*) from tb_reuse; COUNT (*) ---------- 37200896SQL> select count (*) from tb_noreuse; COUNT (*) ---------- 37200896SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name in ('tb _ reuse', 'tb _ noreus '); SEGMENT_NAME BYTES/1024/1024 BYTES --------------- TB_REUSE 4165 -- occupies space close to 4GBTB_NOREUSE limit 2sql> truncate table tb_noreuse; -- directly truncate, fast Table truncated. elapsed: 00:00:00. 25SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name in ('tb _ reuse', 'tb _ noreuse'); SEGMENT_NAME BYTES/1024/1024 bytes --------------- TB_REUSE 4165TB_NOREUSE. 0625 -- space recycled Elapsed: 00:00:00. 03SQL> truncate table tb_reuse reuse storage; -- using the reuse storage method does not significantly improve Table truncated performance. elapsed: 00:00:00. 07SQL> alter table tb_reuse deallocate unused keep 2048; -- the specified m is missing. The default value is byteTable altered. elapsed: 00:00:00. 36SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name in ('tb _ reuse', 'tb _ noreuse'); SEGMENT_NAME BYTES/1024/1024 bytes --------------- TB_REUSE. 0625TB_NOREUSE. 0625 Elapsed: 00:00:00. 03

Iii. Demonstration of truncate table .. reuse storage (12g)

SQL> select * from v $ version where rownum = 1; BANNER CON_ID detail ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production 0SQL> create table tb_12_use as select * from dba_objects; table created. SQL> insert into tb_12_use select * from tb_12_use; 90903 rows created. SQL>/11635584 rows created. SQL> create table tb_12_nouse as select * from tb_12_use; Table created. SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name in ('tb _ 12_USE ', 'tb _ 12_NOUSE '); SEGMENT_NAME BYTES/1024/1024 ---------------------------- --------------- TB_12_NOUSE 3074 -- use space 3GBTB_12_USE 3072SQL> select 'lesham' As author ,' http://blog.csdn.net/leshami 'As Blog from dual; author blog ------- ------------------------------ Leshami http://blog.csdn.net/leshami SQL> set timing on; SQL> truncate table TB_12_NOUSE; -- use the conventional method truncateTable truncated. elapsed: 00:00:01. 73SQL> truncate table TB_12_USE reuse storage; -- using the reuse storage method does not significantly improve Table truncated performance. elapsed: 00:00:01. 10SQL> alter table TB_12_USE deallocate unused keep 2048 m; Table altered. elapsed: 00:00:00. 25SQL> alter table TB_12_USE deallocate unused keep 1 m; Table altered. elapsed: 00:00:00. 14SQL> select segment_name, bytes/1024/1024 from dba_segments where segment_name in ('tb _ 12_USE ', 'tb _ 12_NOUSE'); SEGMENT_NAME BYTES/1024/1024 bytes --------------- TB_12_NOUSE. 0625TB_12_USE 1.0625 Elapsed: 00:00:00. 03 -- since the previous test is in non-archive mode, restart to switch to archive mode and then test SQL> archive log list again; database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 396 Next log sequence to archive 398 Current log sequence 398SQL> select count (*) from tb_12_use; COUNT (*) ---------- 23273472SQL> select count (*) from tb_12_nouse; COUNT (*) ---------- 23273472SQL> truncate table TB_12_NOUSE; Table truncated. elapsed: 00:00:02. 07SQL> truncate table TB_12_USE reuse storage; -- the reuse storage method is used after archiving, and there is not much performance improvement-Because truncat is a DDL, it does not generate too many archTable truncated. elapsed: 00:00:00. 76

Iv. Summary

A. Through the above tests, there is no significant difference between reuse storage and normal storage.
B. truncate table is a ddl operation and cannot be rolled back.
C. Although there are no significant performance differences and large tables in the production environment, we recommend using reuse storage and deallocate.