Truncate資料表背後的幾個參數

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

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 下一頁