Oracle High Water Mask

最新推荐文章于 2025-07-03 21:28:28 发布

原创最新推荐文章于 2025-07-03 21:28:28 发布 · 673 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #delete #access #table #disk

dbms--Oracle 专栏收录该内容

22 篇文章

订阅专栏

本文探讨了HighWaterMark (HWM) 在数据库操作中的作用，特别是delete与truncate命令的区别。通过对比执行计划与统计信息，展示了不同操作对HWM的影响，以及这对查询性能的潜在影响。

High Water Mask（最高水位线，呵呵）以下简称HWM

delete命令不影响表自身的HWM，即表中的区块会随着数据量的增大而增大，但删除数据时，

区块并不会减少，当查询时，会按照HWM的标准去扫描表，即使使用delete将表数据清空，查询时，

依旧会扫描HWM的区块，查询速度也就和没使用delete命令之前是一样的，而truncate可以将表的

HWM重置。

999999条数据时，统计个数的执行计划

SQL>
1* select count(1) from td_count

Execution Plan
---------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TD_COUNT'

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

/************************************************************/

执行delete删除所有数据后的执行计划

SQL>
1* select count(1) from td_count

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TD_COUNT'

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

/************************************************************/

执行truncate后的执行计划

SQL> select count(1) from td_count;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TD_COUNT'

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