ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Truncate Table与外键约束

Truncate Table与外键约束

原创 Linux操作系统 作者:realkid4 时间:2011-06-30 22:12:08 0 删除 编辑

 

Truncate Table操作是我们对数据表进行数据清除的一种便捷方式。相对于DML操作的deletetruncate操作速度更快。但是,使用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_iddata_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.=1blockno.=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进行下降。

 

 

3truncate与外键约束

 

当数据表存在外键约束的时候,进行truncate操作存在一些问题。数据表mt,为主子表关系。数据表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";

 

 

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

 

 

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7753935