Truncate Table与外键约束

 

Truncate Table操作是我们对数据表进行数据清除的一种便捷方式。相对于DML操作的delete，truncate操作速度更快。但是，使用truncate操作本质是一种DDL操作，在使用truncate进行清表操作时要受到很多限制。本篇说明外键约束与truncate操作的关系。

 

外键约束是建立在数据库表之间的约束关系，保证子表列的取值在父表列（通常为主键列）上存在对应关系。

 

1、  环境准备

 

我们建立一个父子关系数据表，使用外键进行约束。

 

 

//使用11g环境

SQL> select * from v$version where rownum<2;

 

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

 

//建立父表t

SQL> create table t (id number, name varchar2(100));

Table created

 

SQL> alter table t add constraint pk_t_id primary key (id);

Table altered

 

//建立字表m

SQL> create table m (cid number, tid number, cname varchar2(100));

Table created

 

SQL> alter table m add constraint pk_m_cid primary key (cid);

Table altered

 

SQL> alter table m add constraint fk_m_t_tid foreign key (tid) references t(id);

Table altered

 

//外键列建立索引

SQL> create index idx_m_tid on m(tid);

Index created

 

 

注意：我们建立了两张数据表，m通过外键fk_m_t_tid与数据表t建立关联关系。此时，我们没有向数据表中插入任何记录。

 

 

2、  truncate实验

 

Truncate Table语句的作用就是清除数据表中的所有记录，现象上看起来同delete tt;相同。但是本质上，truncate语句是有很多特殊之处。首先truncate不属于DML（数据操作语言）范畴，是属于DDL（数据定义语言）。其次，truncate数据表的本质就是将原有的数据表段segment拆除，之后重新建立对象。

 

Truncate操作之后的数据表，本质已经不是原有的数据段segment了。一般delete操作，最多是影响到分配给段的空间被回收，不会影响到段头本身。只在object对象标识上认为是过去的对象而已。下面我们通过三个视图来查看truncate操作本质。

 

 

SQL> create table ll as select * from dba_objects;

Table created

 

SQL> select object_name, object_id, data_object_id from dba_objects where object_name='LL';

 

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID

---------- ---------- --------------

LL              75185          75185

 

 

对象M的两个编号，object_id和data_object_id分别为75185。

 

 

SQL> select extent_id, file_id, block_id, blocks from dba_extents where segment_name='LL';

 

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ---------- ----------

         0          1      89232          8

 （篇幅原因，省略…）

        23          1      90368        128

 

24 rows selected

 

SQL> select segment_name,HEADER_FILE,HEADER_BLOCK,BYTES, BLOCKS from dba_segments where segment_name='LL';

 

SEGMENT_NA HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS

---------- ----------- ------------ ---------- ----------

LL                   1        89232    9437184       1152

 

 

系统为数据段m分配了一个extent分区空间，头块位置为（fileno.=1，blockno.=88824）。头块也就是数据段segment段头位置。

 

此时，我们进行truncate操作。

 

 

SQL> truncate table ll;

Table truncated

 

 

三个数据字典情况变化为：

 

 

SQL> select segment_name,HEADER_FILE,HEADER_BLOCK,BYTES, BLOCKS from dba_segments where segment_name='LL';

 

SEGMENT_NA HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS

---------- ----------- ------------ ---------- ----------

LL                   1        89232      65536          8

 

SQL> select object_name, object_id, data_object_id from dba_objects where object_name='LL';

 

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID

---------- ---------- --------------

LL              75185          75186

 

SQL> select extent_id, file_id, block_id, blocks from dba_extents where segment_name='LL';

 

 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS

---------- ---------- ---------- ----------

         0          1      89232          8

 

 

我们发现，当进行truncate操作时，data_object_id会发生变化。说明底层段对象是新建立的，数据段和分区对应没有变化，说明truncate的时候会重用段头空间。但是，数据段是要进行回收，高水位先HWM进行下降。

 

 

3、truncate与外键约束

 

当数据表存在外键约束的时候，进行truncate操作存在一些问题。数据表m和t，为主子表关系。数据表M上存在引入t主键的外键约束信息，在对主表进行truncate操作时，会有一些问题。

 

 

SQL> select * from t;

        ID NAME

---------- --------------------------------------------------------------------------------

         1 d

 

SQL> select * from m;

       CID        TID CNAME

---------- ---------- --------------------------------------------------------------------------------

 

 

注意，此时m中没有记录，不存在约束问题。

 

 

SQL> truncate table t;

truncate table t

 

ORA-02266: 表中的唯一/主键被启用的外键引用

 

 

报错02266，检查官方文档对应信息，如下：

 

 

[oracle@oracle11g ~]$ oerr ora 02266

02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"

// *Cause: An attempt was made to truncate a table with unique or

//         primary keys referenced by foreign keys enabled in another table.

//         Other operations not allowed are dropping/truncating a partition of a

//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.

// *Action: Before performing the above operations the table, disable the

//          foreign key constraints in other tables. You can see what

//          constraints are referencing a table by issuing the following

//          command:

//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

 

 

含义是当存在主外键约束关系的时候，外键关系启用的时候，不能对主表进行drop和truncate操作。如果非要进行使用，就需要暂时禁用外键约束。

 

 

SQL> alter table m disable constraint fk_m_t_tid;

Table altered

 

 

此时，再次进行truncate操作，就可以有效果了。

 

 

SQL> truncate table t;

Table truncated

 

//之后再次启用约束

SQL> alter table m enable constraint fk_m_t_tid;

Table altered

 

 

 

分析，从操作本身看，truncate是先将对象删除，之后重建的过程。Oracle的约束要求是实时保证的，那么在删除对象的时候，必然有违反约束的空间，必然不会允许执行。

 

4、结论

 

Truncate操作同delete操作虽然效果相同，但是本质千差万别。Truncate本身使用会有一些副作用，比如不能使用回收站恢复、闪回机制失效等等。但truncate操作本身具有快速和一些存储方面优势。

 

掌握各种命令的适用场景，在正确的时间地点使用正确的语句，是我们成熟DBA应该做到的基本功。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-701091/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-701091/