上一篇文章中提到（），当我们用delete删除数据时，因为无法降低高水位，对表做全表扫描时会带来数据库性能问题，而truncate可以大大改善这一状况，下面来看测试
SQL> conn zlm/zlm
Connected.
--创建表之前先记录下剩余表空间大小
SQL> select * from dba_free_space where tablespace_name='ZLM';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
ZLM 6 6272 6
--创建一个100W行的大表
SQL> create table test1(int number);
Table created.
SQL> analyze table test1 compute statistics;
Table analyzed.
SQL> select table_name,num_rows,blocks,empty_blocks,avg_row_len,last_analyzed from dba_tables where table_name like 'TEST%';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN LAST_ANALYZED
------------------------------ ---------- ---------- ------------ ----------- ------------------
TEST1 0 27-SEP-14
SQL> declare
2 i number := 0;
3 begin
4 for i in 1 .. 1000000 loop
5 insert into test1 values (i);
6 end loop;
7 commit;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> select count(*) from test1;
COUNT(*)
----------
--查看高水位状况
SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ ---------- ---------- ----------
6 13631488 28
SQL> select block_id,extent_id,bytes,blocks from dba_extents where segment_name like 'TEST%';
BLOCK_ID EXTENT_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 65536 8
136 1 65536 8
144 2 65536 8
152 3 65536 8
160 4 65536 8
168 5 65536 8
176 6 65536 8
184 7 65536 8
192 8 65536 8
200 9 65536 8
208 10 65536 8
216 11 65536 8
224 12 65536 8
232 13 65536 8
240 14 65536 8
248 15 65536 8
256 16 1048576 128
384 17 1048576 128
512 18 1048576 128
640 19 1048576 128
768 20 1048576 128
896 21 1048576 128
1024 22 1048576 128
1152 23 1048576 128
1280 24 1048576 128
1408 25 1048576 128
1536 26 1048576 128
27 1048576 128
SQL> select * from dba_free_space where tablespace_name='ZLM';
TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------------------------------ ---------- ---------- ---------- ---------- ------------
ZLM 6 1792 4608 6
SQL> set autot trace
SQL> select count(*) from test1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3896847026
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEST1 | 1 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
1 db block gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> delete from test1;
Execution Plan
----------------------------------------------------------
Plan hash value: 2642947686
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | DELETE | TEST1 | | | |
| 2 | TABLE ACCESS FULL| TEST1 | 1 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------
Statistics
----------------------------------------------------------
8 physical reads
847 bytes sent via SQL*Net to client
769 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1000000 rows processed
用delete删除100W行数据的大表TEST1时，产生了大量的redo（2亿多），另外还有很多一致性读（1953），读取了100多W个数据块，814次递归调用，可以看到，用delete删除表记录，对数据库的性能消耗是很大的，尤其是当delete大量行的时候
SQL> set autot off
SQL> select count(*) from test1;
COUNT(*)
----------
SQL> select header_file,header_block,bytes,blocks,extents from dba_segments where segment_name like 'TEST%';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ ---------- ---------- ----------
6 13631488 28
更多详情见请继续阅读下一页的精彩内容：