oracle delete hwm,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。