攻略1-8 高效移除表中数据

问题描述

在从表中删除数据时遇到了性能问题。你想尽可能高效地从表中移除数据。

解决方案

可以使用TRUNCATE(截断)语句或DELETE(删除)语句来从表中移除记录。TRUNCATE通常效率更高,但也要认识到它的一些副作用。例如,TRUNCATE是一种DDL语句。这就意味着在该语句运行之后,Oracle会自动提交它,因此无法对TRUNCATE语句进行回滚。因为TRUNCATE语句是DDL,你不能在一个事务中截断两张独立的表。

下面这个例子使用TRUNCATE语句移除COMPUTER_SYSTEMS表中的所有数据:

enter image description here

工作原理

如果需要在移除数据以后还能选择进行数据回滚(而不是立即提交),那么就应该使用DELETE语句。但是,DELETE语句的缺点是它会产生大量的撤销(undo)和重做信息。因此,对于大表来说,TRUNCATE语句通常是移除数据最有效的方法。

TRUNCATE语句的另一个特性就是它会将表的高水位线重新归零。Oracle对高水位线的定义是段中已使用和未使用空间的边界。当创建一张表的时候,Oracle会为表分配通过MINEXTENTS表存储参数来定义的、一定数量的盘区。每个盘区包含若干数据块。在数据插入到表中之前,没有数据块被占用,高水位线为0。随着数据被插入到表中,就分配了相应的盘区,高水位线边界也就提高了。

当你使用DELETE语句移除表中数据时,高水位线将不会发生变化。使用TRUNCATE语句并重置高水位线的一个优点就是,全表扫描查询仅搜索位于高水位线之下的存储块中的数据行。这对于进行全表扫描的查询性能具有很大的影响。

TRUNCATE语句的另一个副作用就是,如果一张表定义了主键,并且这个主键是其子表的外键,那么不能截断该表,即使这个子表包含零个数据行也是如此。在这种场景下,试图截断父表时,Oracle将会抛出下面这个错误:

enter image description here

Oracle之所以会阻止你截断父表,是因为在一个多用户系统中,有可能在截断子表与接下来截断父表之间的这段时间里,另一个会话向子表中填充数据行。在这种情况下,必须暂时禁用子表所引用的外键约束,执行TRUNCATE语句,然后再重新启用约束。

比较TRUNCATE语句和DELETE语句的功能。Oracle的确允许使用DELETE语句从父表中移除数据行,而不管是否有指向子表的约束存在(假设子表中的数据为零行)。这是因为DELETE会生成重做,具有读一致性,并且能够回滚。表1-5总结了DELETE和TRUNCATE之间的区别。

如果需要使用DELETE语句,就必须使用COMMIT或ROLLBACK来结束事务。提交一条DELETE语句就使得数据永久消失:

enter image description here

注意 其他(有时候不是那么明显)提交事务的方式还包括继续执行下一条DDL语句(隐式提交会话中的活动事务),或者是正常退出客户端工具(例如SQL*Plus)。

如果提交一条ROLLBACK语句而不是COMMIT,那么表中的数据将会与执行DELETE语句之前一样。

使用DML语句时,可以查询V$TRANSACTION视图来确认事务的细节。例如,假设你往表中插入了数据,在进行COMMIT或ROLLBACK之前,能够看到当前所连接会话的活动事务信息如下所示:

enter image description here

enter image description here

注意 从表中移除数据的另一种方法是删除(drop)再重建表。然而,这意味着你还必须重建属于这张表的所有索引、约束、授权和触发器。除此以外,删除一张表时,这张表就会暂时不可用,直到重建完成,并且进行了必需的授权之后才可用。通常来说,删除并重建表这种形式只能在开发或测试环境中进行。

目录