Truncate資料表背後的幾個引數（上）
Truncate語句是Oracle SQL體系中非常有特色的一個。Truncate直接的效果是進行資料表資料的清理，深層次是一種典型的DDL語句。
Oracle中，delete語句是一種標註動作。在執行過程中，資料庫會訪問每個符合刪除條件的資料行進行標註動作，標記為“已刪除”。刪除的資料範圍越大、執行路徑越長，執行SQL語句時間也就越長。所以說，delete操作是一個和資料規模成正比的執行過程。
而Truncate操作最多接觸的知識點是DDL本質。Truncate操作下，Oracle並不關注每個資料行和資料範圍，而是集中修改段頭結構、更新核心資料字典上。對於特別巨大的資料表，Truncate操作速度要顯著快於delete操作。
在11.2.0.x系列版本中，我們還有一些引數可以用來控制Truncate資料表的行為。具體包括：drop storage、drop all storage和reuse storage，每個選項對應truncate資料表的不同行為。本文集中介紹引數的幾個選項。
1、環境介紹
筆者使用Oracle 11gR2進行測試，版本是11.2.0.4。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production
建立專門的非sys使用者，注意：這個細節很重要。
SQL> create user test identified by test;
User created
SQL> grant connect, resource to test;
Grant succeeded
SQL> grant select_catalog_role to test;
Grant succeeded
SQL> grant select any dictionary to test;
Grant succeeded
登入實驗環境，建立資料表。
SQL> conn test/test@sicsdb
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0
Connected as test
SQL> show user
User is "test"
SQL> create table t as select * from dba_objects;
Table created
SQL> create index idx_t_id on t(object_id);
Index created
SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);
PL/SQL procedure successfully completed
對應資料段和索引段結構如下：
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28808 65536 8
1 4 28816 65536 8
2 4 28824 65536 8
（篇幅原因，有省略……）
26 4 30336 1048576 128
27 rows selected
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28936 65536 8
1 4 28944 65536 8
（篇幅原因，有省略……）
15 4 30464 65536 8
16 4 30592 1048576 128
17 rows selected
2、Truncate drop storage行為
Truncate資料表預設行為包括了drop storage引數。使用drop storage之後，資料表中所有資料都被清空，資料表和索引段只保留一個分割槽結構。
SQL> truncate table t drop storage;
Table truncated
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28808 65536 8
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28936 65536 8
注意：雖然兩個段頭分割槽extent的大小和起始段都沒有發生變化，依然保持了28808和28936。但是資料字典結構中，認為是一個新的段結構。
SQL> select object_name, object_id, data_object_id from dba_objects where owner='TEST' and object_name in ('T','IDX_T_ID');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
--------------- ---------- --------------
IDX_T_ID 123667 123668
T 123666 123669
T和IDX_T_ID的object_id和data_object_id不一致了。Data_object_id是內部段結構的編號資訊。一旦進行truncate操作，就會認為是一個新段生成。
預設truncate操作下，Oracle會刪除所有資料，回收所有段結構後重新分配一個新的extent。內部的段結構上，Oracle認為是在原來段頭位置上重新分配的新段。
3、Truncate reuse storage行為
下面來測試一下reuse storage引數行為。首先需要重建表資料內容和充實段結構。
SQL> insert into t select * from dba_objects;
99693 rows inserted
SQL> commit;
Commit complete
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28808 65536 8
1 4 28816 65536 8
（篇幅原因，有省略……）
26 4 30720 1048576 128
27 rows selected
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28936 65536 8
1 4 28840 65536 8
（篇幅原因，有省略……）
17 4 30208 1048576 128
18 rows selected
操作reuse storage。
SQL> truncate table t reuse storage;
Table truncated
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='T' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28808 65536 8
1 4 28816 65536 8
（篇幅原因，有省略……）
26 4 30720 1048576 128
27 rows selected
SQL> select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS from dba_extents where segment_name='IDX_T_ID' and owner='TEST';
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 4 28936 65536 8
1 4 28840 65536 8
2 4 28904 65536 8
（篇幅原因，有省略……）
17 4 30208 1048576 128
18 rows selected
資料的確刪除。
SQL> select count(*) from t;
COUNT(*)
----------
0
Reuse storage情況下，段結構沒有回收，資料卻被刪除了！從段結構情況看，Oracle依然視之為新段，data_object_id發生變化。
SQL> select object_name, object_id, data_object_id from dba_objects where owner='TEST' and object_name in ('T','IDX_T_ID');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
--------------- ---------- --------------
IDX_T_ID 123667 123670
T 123666 123671
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17203031/viewspace-1480093/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Truncate資料表背後的幾個引數（下）
- truncate表後impdp匯入該表時加exclude=index引數並不能排除索引資料的匯入Index索引
- 梯度下降背後的數學原理幾何？梯度
- 影響資料庫效能與穩定性的幾個重要引數資料庫
- Jquery序列化表單引數上傳後臺jQuery
- 類似資料字典的幾個表
- 並行相關的幾個引數並行
- 使用ODU恢復被truncate表的資料
- 使用hellodba的工具恢復truncate表的資料
- 企業上雲的背後 看新數科技如何推動資料庫創新?資料庫
- 【TRUNCATE】在有外來鍵參照的表上無法使用TRUNCATE完成資料清理（ORA-02266）
- truncate table 誤刪除資料後的恢復
- innodb的幾個記憶體引數記憶體
- EBS密碼安全的幾個引數密碼
- 幾個引數配置的計算公式公式
- 恢復被執行truncate table的表資料
- 使用ODU恢復oracle被truncate的表資料Oracle
- 資料表建立引數介紹（一）
- 資料表建立引數介紹（二）
- 資料表建立引數介紹（三）
- 資料表建立引數介紹（四）
- 資料驅動背後的“陷阱”
- 資料庫索引背後的資料結構資料庫索引資料結構
- 檢視一個表中的幾列的資料都是一樣的數量
- truncate表後恢復方法總結
- 管理好ORACLE資料表的幾個建議Oracle
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 伺服器中的幾個重要引數伺服器
- 幾個重要的 ASM Disk Groups 引數ASM
- Oracle幾個初始化引數Oracle
- weblogic幾個優化引數Web優化
- 所有權背後的資料互動
- 大資料背後的星球脈動大資料
- Mashable：Google搜尋背後的資料Go
- 一次truncate table 後的資料恢復[轉帖]資料恢復
- React中如何使用setState第二個引數來處理set後的資料React
- 大資料表的truncate，列刪除，shrink回收高水位大資料
- SQLLDR直接載入幾個引數的測試SQL