Oracle大表クリーンアップtruncate..reuse storage
， 。 MySQL coreutils I/O，Oracle I/O， ， 。
一、TRUNCATE TABLE文法
TRUNCATE TABLE [schema_name.]table_name [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ] [ DROP STORAGE | REUSE STORAGE ] ;-- reuse storage
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 an alternative to deleting all rows of a very large table—when the number of rows is very large, the table entails many thousands of extents, and when data is to be reinserted in the future. TRUNCATE TABLE with REUSE STORAGE performs several orders of magnitude 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.
二、プレゼンテーションtruncate table..reuse storage(11g)
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
SQL> create table tb_reuse as select * from dba_objects;
Table created.
SQL> / --
37200896 rows created.
SQL> create table tb_noreuse as select * from tb_reuse;
Table created.
SQL> select count(*) from tb_reuse;
COUNT(*)
----------
37200896
SQL> select count(*) from tb_noreuse;
COUNT(*)
----------
37200896
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');
SEGMENT_NAME BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE 4165 -- 4GB
TB_NOREUSE 4172
SQL> truncate table tb_noreuse; -- truncate，
Table truncated.
Elapsed: 00:00:00.25
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');
SEGMENT_NAME BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE 4165
TB_NOREUSE .0625 --
Elapsed: 00:00:00.03
SQL> truncate table tb_reuse reuse storage; -- reuse storage ，
Table truncated.
Elapsed: 00:00:00.07
SQL> alter table tb_reuse deallocate unused keep 2048; -- m， byte
Table altered.
Elapsed: 00:00:00.36
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name in('TB_REUSE','TB_NOREUSE');
SEGMENT_NAME BYTES/1024/1024
----------------------------------- ---------------
TB_REUSE .0625
TB_NOREUSE .0625
Elapsed: 00:00:00.03三、プレゼンテーションtruncate table..reuse storage(12g)
SQL> select * from v$version where rownum=1;
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
SQL> 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 -- 3GB
TB_12_USE 3072
SQL> select 'Leshami' 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; -- truncate
Table truncated.
Elapsed: 00:00:01.73
SQL> truncate table TB_12_USE reuse storage; -- reuse storage ，
Table truncated.
Elapsed: 00:00:01.10
SQL> alter table TB_12_USE deallocate unused keep 2048m;
Table altered.
Elapsed: 00:00:00.25
SQL> alter table TB_12_USE deallocate unused keep 1m;
Table altered.
Elapsed: 00:00:00.14
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 .0625
TB_12_USE 1.0625
Elapsed: 00:00:00.03
-- ，
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 396
Next log sequence to archive 398
Current log sequence 398
SQL> select count(*) from tb_12_use;
COUNT(*)
----------
23273472
SQL> select count(*) from tb_12_nouse;
COUNT(*)
----------
23273472
SQL> truncate table TB_12_NOUSE;
Table truncated.
Elapsed: 00:00:02.07
SQL> truncate table TB_12_USE reuse storage; -- reuse storage ，
-- truncat DDL， arch
Table truncated.
Elapsed: 00:00:00.76四、まとめ
a、上記のテストにより、reuse storageを使用すると通常方式と明らかな差がないb、truncate tableがddl操作であり、cをロールバックできない、明らかな性能差がないにもかかわらず、生産環境が大きい場合、reuse storageを使用してdeallocate方式を結合することを提案する