Oracle High Water Mask

最新推荐文章于 2022-06-24 18:58:06 发布

battlehawk

最新推荐文章于 2022-06-24 18:58:06 发布

阅读量634

点赞数

分类专栏： dbms--Oracle 文章标签： oracle sql delete access table disk

本文链接：https://blog.csdn.net/battlehawk/article/details/5113710

版权

dbms--Oracle 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

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