Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1010857
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类: Oracle

2012-01-04 13:52:51

 

Truncate Table操作是我们对数据表进行数据清除的一种便捷方式。相对于DML操作的deletetruncate操作速度更快。但是,使用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_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应该做到的基本功。

 

阅读(1816) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~