理解高水位线 及模拟HWM

HWM:
场景：
big table :select count(*) from table ; cost
delete from table ;  select count(*) from table ; cost
truncate table ; select count(*) from table  cost


SQL> create table ttt as select * from all_objects;

Table created.

SQL> select count(*) from ttt;

  COUNT(*)
----------
     54715


Execution Plan
----------------------------------------------------------
Plan hash value: 2073541258

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   101   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TTT  | 44229 |   101   (0)| 00:00:03 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
        822  consistent gets
        758  physical reads
          0  redo size
        413  bytes sent via SQL*Net to client
        400  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 ttt;

54715 rows deleted.


Execution Plan
----------------------------------------------------------
Plan hash value: 1731860563

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | DELETE STATEMENT   |      |     1 |   101   (0)| 00:00:03 |
|   1 |  DELETE            | TTT  |       |            |          |
|   2 |   TABLE ACCESS FULL| TTT  |     1 |   101   (0)| 00:00:03 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         72  recursive calls
      61043  db block gets
        843  consistent gets
       1584  physical reads
   20308908  redo size
        672  bytes sent via SQL*Net to client
        548  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      54715  rows processed

SQL> select count(*) from ttt;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2073541258

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   101   (0)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TTT  |     1 |   101   (0)| 00:00:03 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


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

SQL> truncate table ttt;

Table truncated.

SQL> select count(*) from ttt;

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


Execution Plan
----------------------------------------------------------
Plan hash value: 2073541258

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TTT  |     1 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


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



delete  --->当删除了表的行记录后，大量数据库都是空块，可以在使用
但是hwm高水位线不调整，全表扫描比许扫描hwm以下的所有块，所以导致全表扫描消耗成本很高
当我们truncate table 后，高位位线归零，再次全表扫描，成本将下来了



delete  ---->dml   not hwm 
truncate --->ddl    hwm