它们都是删除表中的数据,而不能删除表结构,delete 可以删除整个表的数据也可以删除表中某一条或N条满足条件的数据,而truncate只能删除整个表的数据,一般我们把delete 操作收作删除表,而truncate操作叫作截断表.
truncate操作与delete操作对比
操作 | 回滚 | 高水位标记 | 空间 | 效率 |
truncate | 不能 | 下降 | 回收 | 块 |
delete | 可以 | 不变 | 不回收 | 慢 |
首先要明白两点
1.在oracle 中数据删除后还能回滚是因为它把原始数据放到了undo表空间,
2.DML语句使用undo表空间,DDL语句不使用undo,而delete是DML语句,truncate是DDL语句,别外DDL语句是隐式提交.
所以truncate操用不能回滚,而delete操作可以.
两种操作对比(首先新建一个表,并插入数据)
SQL> create table t
2 (
3 i number
4 );
Table created.
SQL> insert into t values(10);
SQL> commit;
Commit complete.
SQL> select * from t;
I
----------
10
Delete删除,然后回滚
SQL> delete from t;
1 row deleted.
SQL> select * from t;
no rows selected
#删除后回滚
SQL> rollback;
Rollback complete.
SQL> select * from t;
I
----------
10
Truncate截断表,然后回滚.
SQL> truncate table t;
Table truncated.
SQL> rollback;
Rollback complete.
SQL> select * from t;
no rows selected
可见delete删除表还可以回滚,而truncate截断表就不能回滚了.(前提是delete操作没有提交)
2.高水线
所有的Oracle表都有一个容纳数据的上限(很象一个水库历史最高的水位),我们把这个上限称为“high water mark”或HWM。这个HWM是一个标记(专门有一个数据块用来记录高水标记等),用来说明已经有多少数据块分配给这个表. HWM通常增长的幅度为一次5个数据块.
delete语句不影响表所占用的数据块, 高水线(high watermark)保持原位置不动
truncate 语句缺省情况下空间释放,除非使用reuse storage; truncate会将高水线复位
下面对两种操作对比
SQL> analyze table t estimate statistics;
Table analyzed.
SQL> select segment_name,blocks from dba_segments where segment_name=upper('t');
SEGMENT_NAME BLOCKS
------------------------------ ----------
T 24
SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ------------
T 20 3
USER_TABLES.BLOCKS 列代表该表中曾经使用过得数据库块的数目,即水线。
注意:USER_TABLES.BLOCKS EMPTY_BLOCKS (20+3=23)比DBA_SEGMENTS.BLOCKS少一个数据库块,这是因为有一个数据库块被保留用作表头。DBA_SEGMENTS.BLOCKS 表示分配给这个表的所有的数据库块的数目。USER_TABLES.BLOCKS表示已经使用过的数据库块的数目(水线)。
Delete删除表,
SQL> delete from t;
10000 rows deleted
SQL> commit;
Commit complete.
SQL> analyze table t estimate statistics;
Table analyzed.
SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- ----------------------------------------------------------------
T 20 3
Truncate截断表
SQL> truncate table t;
Table truncated.
SQL> analyze table t estimate statistics;
Table analyzed.
SQL> select table_name,blocks,empty_blocks from user_tables where table_name=upper('t');
TABLE_NAME BLOCKS EMPTY_BLOCKS
------------------------------ ---------- --------------------------------------------------------
T 0 7
可见,delete表,BLOCK(高水线)不变,而truncate表BLOCKS(高水线)变为0
现在我们也看到blocks+empty_blocks=7,也就是oracle分配区时默认一次7+1(表头)=8个blocks;
高水线的作用: HWM对数据库的操作有如下影响:
a) 全表扫描通常要读出直到HWM标记的所有的属于该表数据库块,即使该表中没有任何数据。
b) 即使HWM以下有空闲的数据库块,键入在插入数据时使用了append关键字,则在插入时使用HWM以上的数据块,此时HWM会自动增大。
因此高水线是oracle优化时一个重要的参数
3.空间
既然高水线用来说明已经有多少数据块分配给这个表,那么高水线也可理解为表的空间占用。
即使delete将表中的数据全部删除,HWM还是为原值,所以还有那么多的空间分配给这个表,即它的空间还没有回收,
而truncate表后高水线变为0,那现在它就表示没有分配空间,即它的空间被回收了。
4.效率
要想查看delete,truncate那个效率更高,先构建一个大表,然后查看它们分别对些表删除所需的时间。
有个相当形象的比喻:领导给你两本书让你扔掉,delete就是你守在复印机前,把书一页页撕下来复印一份,再一页页扔到垃圾桶里,truncate就是直接把两本书扔到垃圾桶里,那个快那个慢不言而喻。
先在表中插入100000条记录,并打开时间
SQL> set timing on;
SQL> begin
2 for i in 1..100000 loop
3 insert into t values('10');
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:12.50
Delete删除表
SQL> delete from t;
100000 rows deleted.
Elapsed: 00:00:20.09
Truncate 截断表
#先把表回滚
SQL> rollback;
Rollback complete.
Elapsed: 00:00:17.36
SQL> select count(*) from t;
COUNT(*)
-------------------
100000
Elapsed: 00:00:00.01
SQL> truncate table t;
Table truncated.
Elapsed: 00:00:00.20
可见删除同一个大小的表,delete用了20.09秒,而truncate只用了0.2秒.
高水位线实验:
-- 创建test3表
SQL> create table test3 as
2 select * from dba_objects where 1 = 2;
Table created
-- 查看表中分配块,区大小
SQL> SELECT segment_name, segment_type, blocks -- 分配数据块数, extents -- 分配区块数
2 FROM dba_segments
3 WHERE segment_name = 'TEST3'
4 ;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
-------------------------------------------------------------------------------- ------------------ ---------- ----------
TEST3 TABLE 8 1
TEST3 TABLE 8 1
-- 分析表TEST3表
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
-- 查询TEST3表高水位线
SQL> SELECT blocks -- 高水位线(占用TEST3表数据块数), empty_blocks -- TEST3表空闲块数, num_rows
2 FROM user_tables
3 WHERE table_name = 'TEST3';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
0 7 0
-- 因为未向TEST3表中插入任何数据,因此此表的高水位线为0,现向TEST3表中插入数据再观察
SQL> insert into test3
2 select * from dba_objects;
50361 rows inserted
SQL> commit;
Commit complete
-- 重新分析表
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
-- 再次查看表中分配块,区大小
SQL> SELECT segment_name, segment_type, blocks, extents
2 FROM dba_segments
3 WHERE segment_name = 'TEST3'
4 ;
SEGMENT_NAME SEGMENT_TYPE BLOCKS EXTENTS
-------------------------------------------------------------------------------- ------------------ ---------- ----------
TEST3 TABLE 8 1
TEST3 TABLE 768 21
此时看到BLOCKS数已增长到768, 也就是Oracle分配给TEST3表768个数据块,21个区
-- 再次查看TEST3表高水位线
SQL> SELECT blocks, empty_blocks, num_rows
2 FROM user_tables
3 WHERE table_name = 'TEST3';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
689 78 50361
已增长到689个块, 还有78个空闲块,689 + 78 = 767, 比分配的少1个数据块,是因为这一个数据块是用作segment header
-- 现将TEST3表delete,在查看高水位线
SQL> delete from test3;
50361 rows deleted
SQL> commit;
Commit complete
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
SQL>
SQL> SELECT blocks, empty_blocks, num_rows
2 FROM user_tables
3 WHERE table_name = 'TEST3';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
689 78 0
发现此表高水位线并未减少,证明delete只是删除表中数据块的记录,但并不会使表中的高水位线下降, 在进行全表扫描时会Oracle会扫描表中高水位线下的所有数据块,
因此数据虽然被删除了,但查询时有可能还是很慢。所以在进行大表删除时应使用truncate语句,看下面实验:
SQL> truncate table test3;
Table truncated
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
SQL>
SQL> SELECT blocks, empty_blocks, num_rows
2 FROM user_tables
3 WHERE table_name = 'TEST3';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
0 7 0
现在表中高水位下降到0了, 一点心得, 记录下来。