oracle delete操作会不会改变HWM-实验

SQL> create user test_hwm identified by hwm;

User created.

SQL> grant dba to test_hwm;

Grant succeeded.

SQL> conn test_hwm/hwm

Connected.

SQL> create table t(id int,name varchar2(20));

Table created.

SQL> set autotrace on stat
SQL> select * from t;

no rows selected


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

SQL> begin
  2  for i in 1 .. 10000000
  3  loop
  4     insert into t values(i,'kobe24shou');
  5  end loop
  6  ;
  7  commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)
----------
  10000000


Statistics
----------------------------------------------------------
         29  recursive calls
          1  db block gets
      60666  consistent gets
      19824  physical reads
    2169600  redo size
        411  bytes sent via SQL*Net to client
        385  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  t;

10000000 rows deleted.


Statistics
----------------------------------------------------------
      20470  recursive calls
   10523533  db block gets
      56262  consistent gets
      23070  physical reads
 2853957692  redo size
        683  bytes sent via SQL*Net to client
        550  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
   10000000  rows processed

SQL> commit;

Commit complete.

SQL> select count(*) from t;        //删除之后water mark还是在高水位

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      60414  consistent gets
      26094  physical reads
    2172060  redo size
        410  bytes sent via SQL*Net to client
        385  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 t;     //truncate 会把高水位置位

Table truncated.

SQL> select count(*) from t;        

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


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

总结：假设一个表刚开始是空的，然后不断的插入数据，HWM不断的往上涨，然后删除一些数据，HWM还是处在原来的位置。

这时，继续插入记录。这时oracle可能有三个选择：

第一：在包含数据的块中寻找空间记录插入，这些块由freelist链表维系着。

第二：如果找不到合适的块，oracle会在HWM以下的完全删空的块寻找。

第三：如何还找不到，oracle会在HWM以上新增一个或多个空白的块，并且上移HWM。