DELETE、TRUNCATE与高水位线（HWM）

我们知道，当通过DELETE方式删除表数据时，表段的HWM不会降低，Truncate缺省的会回收空间，降低HWM。

以下是一则案例，用户Delete了表数据，通过show_space来查看空间，发现空间大部分已经是空闲的，都位于75%~100%的范围：

SQL> exec show_space('RSBERRORLOG','SAPSR3');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................       1,667,598
Full Blocks        .....................               1
Total Blocks............................       1,675,904
Total Bytes.............................  13,729,005,568
Total MBytes............................          13,093
Unused Blocks...........................           6,144
Unused Bytes............................      50,331,648
Last Used Ext FileId....................              54
Last Used Ext BlockId...................         924,425
Last Used Block.........................           2,048

但是查询时可以发现，需要全索引扫描，消耗大量的逻辑读，返回结果为0：

SQL> set autotrace on
SQL> set timing on
SQL> set linesize 120
SQL> select count(*) from sapsr3.RSBERRORLOG;

  COUNT(*)
----------
         0

Elapsed: 00:00:17.41

Execution Plan
----------------------------------------------------------
Plan hash value: 3944197743

---------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 | 97583   (1)| 00:19:32 |
|   1 |  SORT AGGREGATE       |                 |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| RSBERRORLOG~001 |    68M| 97583   (1)| 00:19:32 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     418847  consistent gets
      33439  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        463  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

将表TRUNCATE之后，可以发现查询的逻辑读降低到3，表和索引的HWM降低，空间释放出来：

SQL> truncate table sapsr3.RSBERRORLOG;

Table truncated.

Elapsed: 00:00:06.14
SQL> set autotrace on
SQL> select count(*) from sapsr3.RSBERRORLOG;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3944197743

---------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |     1 | 97583   (1)| 00:19:32 |
|   1 |  SORT AGGREGATE       |                 |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| RSBERRORLOG~001 |    68M| 97583   (1)| 00:19:32 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        463  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

但是注意以上执行计划评估索引ROWS仍然有68M行，这是由于统计信息没有更新的缘故。

对表收集统计信息，再次查询，记录ROWS显示正常：

SQL> exec dbms_stats.gather_table_stats('SAPSR3','RSBERRORLOG');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
SQL> select count(*) from sapsr3.RSBERRORLOG;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3213820690

--------------------------------------------------------------------------
| Id  | Operation        | Name          | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |               |     1 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |               |     1 |            |          |
|   2 |   INDEX FULL SCAN| RSBERRORLOG~0 |     1 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        514  bytes sent via SQL*Net to client
        463  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SHOW_SPACE显示空间使用情况：

SQL> exec show_space('RSBERRORLOG','SAPSR3');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               5
Unused Bytes............................          40,960
Last Used Ext FileId....................               5
Last Used Ext BlockId...................          97,761
Last Used Block.........................               3

供参考。