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方式
|