首页 > 代码库 > 【转】浅谈truncate的使用
【转】浅谈truncate的使用
delete 操作不会改变表的高水标记,因此如果我们对一个表插入1000万条数据,然后再回滚(对insert操作做回滚相当于相应地做delete操作),会使表的高水标记增长得很高,这时虽然我们操作的表依然是一个空表,但是查询它却会读惊人数量的内存块,实验如下:
ETL@RACTEST> create table test_table (anumber);
Table created.
Elapsed: 00:00:00.01
ETL@RACTEST> set autotrace traceonlystatistics;
ETL@RACTEST> select * from test_table;
no rows selected
Elapsed: 00:00:00.00
Statistics
----------------------------------------------------------
24 recursivecalls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
318 bytessent via SQL*Net to client
453 bytesreceived via SQL*Net from client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以看到查询这个空表读了7个内存块。然后我们向表中插入1000万条数据后再回滚:
insert into test_table select level from dual connect bylevel<=10000000;
10000000 rows created.
Elapsed: 00:00:58.38
ETL@RACTEST> rollback;
Rollback complete.
Elapsed: 00:00:01.15
ETL@RACTEST> set autotrace traceonlystatistics;
ETL@RACTEST> select * from test_table;
no rows selected
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
283 recursivecalls
1 db block gets
15463 consistentgets
0 physical reads
176 redosize
318 bytessent via SQL*Net to client
453 bytesreceived via SQL*Net from client
1 SQL*Net roundtrips to/fromclient
4 sorts (memory)
0 sorts (disk)
0 rows processed
可以看到,同样是读一个空表,但是却做了15463(consistent gets)+1(db blockgets)个逻辑读,可见delete操作不会修改表的高水标记。并且delete操作也很耗费时间,因此我们通常在想清空一个表的数据时用truncate来替代delete。truncate会以一种快速的方式清空表,只产生很少的日志信息,并且会将高水标记清零。例如:
ETL@RACTEST> insert into test_table select levelfrom dual connect by level<=10000000;
10000000 rows created.
Elapsed: 00:00:32.45
ETL@RACTEST> commit;
Commit complete.
Elapsed: 00:00:00.02
ETL@RACTEST> truncate table test_table;
Table truncated.
Elapsed: 00:00:29.52
ETL@RACTEST> set autotrace traceonlystatistics;
ETL@RACTEST> select * from test_table;
no rows selected
Elapsed: 00:00:00.00
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
6 consistent gets
0 physical reads
96 redosize
318 bytessent via SQL*Net to client
453 bytesreceived via SQL*Net from client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
truncate后再查询只做了7个逻辑读,也就是读了7个内存块。
但是truncate操作有一个限制,例如B表有一个外键参照A表的一个字段,那么truncate A表Oracle就会报错,实验如下:
create table ttt1 (a number,primary key(a));
Table created.
Elapsed: 00:00:00.02
ETL@RACTEST> insert into ttt1 select level from dualconnect by level<=10;
10 rows created.
Elapsed: 00:00:00.02
ETL@RACTEST> commit;
Commit complete.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1;
A
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.00
ETL@RACTEST> create table ttt2 (a number,constraintfk_ttt2_a foreign key(a) references ttt1(a));
Table created.
这时ttt2表的a字段参照了ttt1表的a字段,我如果用delete操作清空ttt1表的值是可以的,因为ttt2表没有数据,删除ttt1的数据不会违背外键参照完整性。
ETL@RACTEST> delete from ttt1;
10 rows deleted.
但是如果我们使用truncate,oracle则要报错:
ETL@RACTEST> truncate table ttt1;
truncate table ttt1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabledforeign keys
因为truncate实现的是一种快速删除,因此它无法检验参照完整性,即使删除的数据不违反参照完整性,Oracle也不允许做truncate操作,也就是我们说的宁可错杀一千,不可放过一个,呵呵。
我在培训的时候有人说外键如果设置了级联删除就可以truncate,但实际上是不行的,实验如下:
ETL@RACTEST> drop table ttt2 purge;
Table dropped.
Elapsed: 00:00:00.03
ETL@RACTEST> create table ttt2 (a number,constraintfk_ttt2_a foreign key(a) references ttt1(a) on deletecascade);
Table created.
Elapsed: 00:00:00.01
ETL@RACTEST> truncate table ttt1;
truncate table ttt1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabledforeign keys
Elapsed: 00:00:00.00
这是一种方式的级联删除,trucate仍然报错,下面是另一种级联:
ETL@RACTEST> drop table ttt2 purge;
Table dropped.
Elapsed: 00:00:00.01
ETL@RACTEST> create table ttt2 (a number,constraintfk_ttt2_a foreign key(a) references ttt1(a) on delete setnull);
Table created.
Elapsed: 00:00:00.01
ETL@RACTEST> truncate table ttt1;
truncate table ttt1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabledforeign keys
同样不行,因此得出结论,只要有外键参照A表的字段,那么就不能对A表使用truncate操作!
0 db block gets
7 consistent gets
0 physical reads
0 redo size
318 bytessent via SQL*Net to client
453 bytesreceived via SQL*Net from client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以看到查询这个空表读了7个内存块。然后我们向表中插入1000万条数据后再回滚:
insert into test_table select level from dual connect bylevel<=10000000;
10000000 rows created.
Elapsed: 00:00:58.38
ETL@RACTEST> rollback;
Rollback complete.
Elapsed: 00:00:01.15
ETL@RACTEST> set autotrace traceonlystatistics;
ETL@RACTEST> select * from test_table;
no rows selected
Elapsed: 00:00:00.14
Statistics
----------------------------------------------------------
283 recursivecalls
1 db block gets
15463 consistentgets
0 physical reads
176 redosize
318 bytessent via SQL*Net to client
453 bytesreceived via SQL*Net from client
1 SQL*Net roundtrips to/fromclient
4 sorts (memory)
0 sorts (disk)
0 rows processed
可以看到,同样是读一个空表,但是却做了15463(consistent gets)+1(db blockgets)个逻辑读,可见delete操作不会修改表的高水标记。并且delete操作也很耗费时间,因此我们通常在想清空一个表的数据时用truncate来替代delete。truncate会以一种快速的方式清空表,只产生很少的日志信息,并且会将高水标记清零。例如:
ETL@RACTEST> insert into test_table select levelfrom dual connect by level<=10000000;
10000000 rows created.
Elapsed: 00:00:32.45
ETL@RACTEST> commit;
Commit complete.
Elapsed: 00:00:00.02
ETL@RACTEST> truncate table test_table;
Table truncated.
Elapsed: 00:00:29.52
ETL@RACTEST> set autotrace traceonlystatistics;
ETL@RACTEST> select * from test_table;
no rows selected
Elapsed: 00:00:00.00
Statistics
----------------------------------------------------------
1 recursive calls
1 db block gets
6 consistent gets
0 physical reads
96 redosize
318 bytessent via SQL*Net to client
453 bytesreceived via SQL*Net from client
1 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
0 rows processed
truncate后再查询只做了7个逻辑读,也就是读了7个内存块。
但是truncate操作有一个限制,例如B表有一个外键参照A表的一个字段,那么truncate A表Oracle就会报错,实验如下:
create table ttt1 (a number,primary key(a));
Table created.
Elapsed: 00:00:00.02
ETL@RACTEST> insert into ttt1 select level from dualconnect by level<=10;
10 rows created.
Elapsed: 00:00:00.02
ETL@RACTEST> commit;
Commit complete.
Elapsed: 00:00:00.00
ETL@RACTEST> select * from ttt1;
A
----------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.00
ETL@RACTEST> create table ttt2 (a number,constraintfk_ttt2_a foreign key(a) references ttt1(a));
Table created.
这时ttt2表的a字段参照了ttt1表的a字段,我如果用delete操作清空ttt1表的值是可以的,因为ttt2表没有数据,删除ttt1的数据不会违背外键参照完整性。
ETL@RACTEST> delete from ttt1;
10 rows deleted.
但是如果我们使用truncate,oracle则要报错:
ETL@RACTEST> truncate table ttt1;
truncate table ttt1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabledforeign keys
因为truncate实现的是一种快速删除,因此它无法检验参照完整性,即使删除的数据不违反参照完整性,Oracle也不允许做truncate操作,也就是我们说的宁可错杀一千,不可放过一个,呵呵。
我在培训的时候有人说外键如果设置了级联删除就可以truncate,但实际上是不行的,实验如下:
ETL@RACTEST> drop table ttt2 purge;
Table dropped.
Elapsed: 00:00:00.03
ETL@RACTEST> create table ttt2 (a number,constraintfk_ttt2_a foreign key(a) references ttt1(a) on deletecascade);
Table created.
Elapsed: 00:00:00.01
ETL@RACTEST> truncate table ttt1;
truncate table ttt1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabledforeign keys
Elapsed: 00:00:00.00
这是一种方式的级联删除,trucate仍然报错,下面是另一种级联:
ETL@RACTEST> drop table ttt2 purge;
Table dropped.
Elapsed: 00:00:00.01
ETL@RACTEST> create table ttt2 (a number,constraintfk_ttt2_a foreign key(a) references ttt1(a) on delete setnull);
Table created.
Elapsed: 00:00:00.01
ETL@RACTEST> truncate table ttt1;
truncate table ttt1
*
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabledforeign keys
同样不行,因此得出结论,只要有外键参照A表的字段,那么就不能对A表使用truncate操作!
转自:http://blog.sina.com.cn/s/blog_6ff05a2c0100mira.html
【转】浅谈truncate的使用
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。