分类: Oracle
2012-01-04 13:52:51
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应该做到的基本功。