Truncate数据表背后的几个参数

Truncate语句是Oracle SQL体系中非常有特色的一个。Truncate直接的效果是进行数据表数据的清理，深层次是一种典型的DDL语句。

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

vc商城(eshop)

采用目前业界最流行的模版编译系统，所有的页面都可以实现在线/离线修改，只需简单掌握HTML的知识，就可以轻松创建属于自己的个性化的专业用户界面，内建多语言包替换模块，独创的商品参数模版系统，强大的后台管理支持和数据备份功能

0

查看详情

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

更多详情见请继续阅读下一页的精彩内容：