当前位置:Gxlcms > mysql > Oracle中DELETE与TRUNCATE语句的区别及优缺点

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

时间:2021-07-01 10:21:17 帮助过:77人阅读

通常,我们可以使用 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


人气教程排行