程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle大表清理truncate..reusestorage

Oracle大表清理truncate..reusestorage

編輯:Oracle教程

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved