Oracle大表清理truncate..reusestorage

来源:转载

最近需要清理一张大表,要求不能影响性能。在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 ProductionSQL> 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(*)---------- 37200896SQL> select count(*) from tb_noreuse; COUNT(*)---------- 37200896SQL> 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 --占用空间接近4GBTB_NOREUSE 4172SQL> truncate table tb_noreuse; --直接truncate,速度很快 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----------------------------------- ---------------TB_REUSE 4165TB_NOREUSE .0625 -- 空间已回收Elapsed: 00:00:00.03SQL> truncate table tb_reuse reuse storage; --使用reuse storage方式,并无太多性能提升Table truncated.Elapsed: 00:00:00.07SQL> alter table tb_reuse deallocate unused keep 2048; --这里漏掉了指定m,缺省为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----------------------------------- ---------------TB_REUSE .0625TB_NOREUSE .0625Elapsed: 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 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 --使用空间为3GBTB_12_USE 3072SQL> select 'Leshami' As author,'http://blog.csdn.net/leshami' as Blog from dual;AUTHOR BLOG------- ----------------------------Leshami http://blog.csdn.net/leshamiSQL> set timing on;SQL> truncate table TB_12_NOUSE; --使用常规方式truncateTable truncated.Elapsed: 00:00:01.73SQL> truncate table TB_12_USE reuse storage; --使用reuse storage方式,并无太多性能提升Table truncated.Elapsed: 00:00:01.10SQL> alter table TB_12_USE deallocate unused keep 2048m; Table altered.Elapsed: 00:00:00.25SQL> alter table TB_12_USE deallocate unused keep 1m;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------------------------------ ---------------TB_12_NOUSE .0625TB_12_USE 1.0625Elapsed: 00:00:00.03-- 由于前面的测试在非归档模式,因此重启切换到归档模式后再次测试SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 396Next log sequence to archive 398Current 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; --归档后使用reuse storage方式,同样无太多性能提升 --因为truncat属于DDL,本身并不会产生太大archTable truncated.Elapsed: 00:00:00.76

四、小结

a、通过上述测试,当使用reuse storage与普通方式并无明显差异
b、truncate table 是ddl操作,无法回滚
c、尽管无明显性能差异,生产环境大表情况,还是建议使用reuse storage结合deallocate方式



分享给朋友:
您可能感兴趣的文章:
随机阅读: