--- | 0 | DELETE STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | DELETE | TEST1 | | | | | 2 | TABLE ACCESS FULL| TEST1 | 1 | 2 (0)| 00:00:01 | --------------------------------------------------------------------
Statistics ---------------------------------------------------------- 814 recursive calls 1038983 db block gets 1953 consistent gets 8 physical reads 245334988 redo size 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(*) ---------- 0
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 130 13631488 1664 28
--用delete删除数据后查看执行计划 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 1599 consistent gets 0 physical reads 0 redo size 525 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
此时虽然表中已经没有一条记录了，但是由于TEST1表并没有索引，走的是全表扫描，全表扫描就是要从hearder_block的值一致扫描到blocks得值，即从block 130一致到block 1664，可以看到，即使去访问一个空表，也要消耗1599次逻辑读，这就是高水位没有下降的缘故，之前说过，delete操作并不会降低表的高水位，带来的副作用就是访问该表时带来的性能下降（产生大量逻辑读）
--truncate表后再次查看统计信息 SQL> truncate table test1;
Table truncated.
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 ---------------------------------------------------------- 2 recursive calls 1 db block gets 7 consistent gets 0 physical reads 96 redo size 525 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
此时再去全表扫描TEST1表，只有7次一致性读了，大大降低了需要扫描的数据库块，只有1个，
--查看此时TEST1表的表信息 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 ------------------------------ ---------- ---------- ------------ ----------- ------------- |