Oracle 中 DELETE 与 TRUNCATE 语句的区别及优缺点
- WBOY原创
- 2016-06-07 15:02:082117浏览
通常，我们可以使用 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