truncate table与外键约束
转自：http://run-freely.blog.163.com/blog/static/13207267820127692111738/
Truncate Table操作是我们对数据表进行数据清除的一种便捷方式。相对于DML操作的delete，truncate操作速度更快。但是，使用truncate操作本质是一种DDL操作，在使用truncate进行清表操作时要受到很多限制。本篇说明外键约束与truncate操作的关系。
外键约束是建立在数据库表之间的约束关系，保证子表列的取值在父表列（通常为主键列）上存在对应关系。
1、 环境准备
我们建立一个父子关系数据表，使用外键进行约束。
//使用11g环境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database11gEnterpriseEdition Release11.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应该做到的基本功。
