Oracle 中 DELETE 与 TRUNCATE 语句的区别及优缺点

通常，我们可以使用 TRUNCATE 或 DELETE 语句删除表中的记录。 TRUNCATE 语句的通常更高效，但是它的使用需要外注意。 TRUNCATE 属于 DDL 语句，这意味着 Oracle 会在该语句运行后自动提交该语句及当前 session 中的事务，因此我们无法回滚 TRUNCATE 语句。

通常，我们可以使用 TRUNCATE或 DELETE语句删除表中的记录。TRUNCATE语句的通常更高效，但是它的使用需要格外注意。TRUNCATE属于 DDL语句，这意味着 Oracle会在该语句运行后自动提交该语句及当前session中的事务，因此我们无法回滚 TRUNCATE语句。与此同时，又因为 TRUNCATE属于 DDL语句，我们无法在一个事务中 truncate两张不同的表。如下所示：

TRUNCATE语句删除COMPUTER_SYSTEMS表中的所有数据：

SQL> truncatetable computer_systems;

在 truncate一张表时，默认情况下，除了该表的MINEXTENTS表级存储参数所定义的空间外，之前分配给该表的所有空间都会被回收。如果想要 TRUNCATE语句不回收当前分配的 extent，可以在 truncate语句中使用 REUSESTORAGE子句：

SQL> truncatetable computer_systems reuse storage;

我们可以查询 DBA/ALL/USER_EXTENTS视图查看该表的 extent是否已回收，例如：

 

set pagesize 50000

set long 9999

selectdbms_metadata.get_ddl('TABLE','TEST','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','TEST','SCOTT')

--------------------------------------------------------------------------------

  CREATE TABLE"SCOTT"."TEST"

   (   "EMPNO" NUMBER(4,0),

        "ENAME" VARCHAR2(10),

        "JOB" VARCHAR2(9),

        "MGR" NUMBER(4,0),

        "HIREDATE" DATE,

        "SAL" NUMBER(7,2),

        "COMM" NUMBER(7,2),

        "DEPTNO" NUMBER(2,0)

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576MINEXTENTS 1

 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULTCELL_FLAS

H_CACHE DEFAULT)

  TABLESPACE "USERS"

 

SQL> insert intotest select * from emp;

14 rows created.

SQL> /

14 rows created.

SQL> /

14 rows created.

SQL> /

14 rows created.

SQL> /

14 rows created.

 

SQL> selectcount(*) from user_extents where segment_name = 'TEST';

  COUNT(*)

----------

         2

SQL> truncatetable test;

Table truncated.

 

SQL> selectcount(*) from user_extents where segment_name = 'TEST';

  COUNT(*)

----------

         1

 

如果在删除数据时需要选择回滚而不提交操作，应选择使用 DELETE语句。但是 DELETE语句的缺点是它会生成大量的 undo和 redo信息。因此，对于大表，TRUNCATE语句通常是删除表数据最快的方法。TRUNCATE语句的另一优势是可以将表的高水位线重置为 0。我们可以利用 Truncate语句的这一特点，调整全表查询的性能，通过TRUNCATE语句释放表的高水位线，让表中的行记录存储在高水位线以下的块中，大幅提高全表扫描的效率。Truncate语句除了无法回滚外，还有一个缺点：如果一张父表上定义的主键被子表作为外键约束引用，即使子表包含 0行记录，父表也无法删除。在这种情景下尝试 truncate父表时，Oracle会抛出如下异常：

 

SQL> truncatetable dept;

truncate table dept

               *

ERROR at line 1:

ORA-02266:unique/primary keys in table referenced by enabled foreign keys

 

Oracle的内部机制会阻止 truncate父表，因为在多用户系统中，在您先 truncate子表再 truncate父表的时间间隔内，存在另一个 session向子表插入记录的可能性。在这种场景下，您必须临时禁用表的外键约束后才能执行 TRUNCATE语句，然后再重新启用外键约束。

对比 TRUNCATE和 DELETE语句的行为。Oracle允许在子表引用了外键约束的情况下使用 DELETE语句删除父表的记录。这是因为 DELETE会生成 undo属于read-consistent操作，可以进行回滚。使用 DELETE语句时，必须使用COMMIT或 ROLLBACK来完成事务。

 

SQL> delete fromtest;

28 rows deleted.

 

SQL> commit;

Commit complete.

 

注意：commit语句可以使 delete语句的操作永久生效。还可以使用其他的方式隐含提交事务，例如在delete语句执行后续的 DDL语句或者以正常方式退出客户端连接工具（sqlplus）。

 

如果执行 ROLLBACK语句而不执行COMMIT语句，则表中的数据会和执行 DELETE语句之前一样。在执行 DML语句时，可以查询V$TRANSACTION视图来了解事务的详细信息，例如，如果往表中插入了数据，在执行 COMMIT或 ROLLBACK之前，可以通过如下方式查看当前连接 session的活动事务的信息：

SQL> insert intotest select * from emp;

14 rows created.

 

 

SQL> selectTADDR,USERNAME from v$session where username ='SCOTT';

 

TADDR    USERNAME

--------------------------------------

36C71818 SCOTT

 

SQL> selectXIDUSN,XIDSLOT,XIDSQN from v$transaction where addr = '36C71818';

 

    XIDUSN   XIDSLOT     XIDSQN

-------------------- ----------

         6          3        746

 

SQL> commit;

SQL> selectTADDR,USERNAME from v$session where username ='SCOTT';

 

TADDR    USERNAME

--------------------------------------

         SCOTT

 

 

DELETE语句和 TRUNCATE语句的区别：

 

DELETE语句既可以提交也可以回滚，TRUNCATE语句在执行后自动提交；

DELETE语句生成 UNDO信息，TRUNCATE语句不生成或生成量很少；

DELETE语句无法释放表的高水位，TRUNCATE可以；

DELETE语句不受外键约束影响，TRUNCATE受影响；

在删除大表数据时，DELETE效率远低于TRUNCATE。

 

删除表数据时还有一种非常快的方法：先 drop然后重建表，不过在重建表后您需要重建属于该表的所有索引、约束、授权和触发器。此外，在 drop表时重建表过程中，该表会短暂不可用，通常这种方式只允许在开发或测试环境使用。

作者：xiangsir

9063597

QQ：444367417

MSN：xiangsir@hotmail.com