? ? ?看了小布老師關于block里面數據存儲的high water mark的實驗，自己也做了一遍。

?

SQL> create table x(i int,name varchar(20));

Table created.

?

SQL> select count(*) from x;

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

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

?

SQL> begin
2 for i in 1 ..100000
3 loop
4 insert into x values(i,'my');
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

?

SQL> select count(*) from x;

COUNT(*)
----------
100000

Statistics

----------------------------------------------------------
0 recursive calls
0 db block gets
186 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 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 x;

100000 rows deleted.

?

Statistics

----------------------------------------------------------
1062 recursive calls
104609 db block gets
790 consistent gets
5 physical reads
25386252 redo size
838 bytes sent via SQL*Net to client
709 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
100000 rows processed

SQL> commit;

Commit complete.

?

SQL> select count(*) from x;

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

Statistics

----------------------------------------------------------
0 recursive calls
0 db block gets
186 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
469 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 x;

Table truncated.

?

SQL> select count(*) from x;

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

Statistics

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

?

?

----------------

通過上面的實驗，我們可以看出water mark的作用。

如果delete 數據，oracle去查詢時，還是要從0開始查，直至water mark為止。

如果truncate數據，也就是說，oracle發現water mark=0，直接就返回了。其實truncate是把表的segment給清除了，自然不占用空間。

?

（有個疑問，小布老師實驗里面，physical reads是比較大的；我的實驗里面physical reads=0；

也就是說數據一直保存在內存里面，沒有保存到硬盤；我的環境是10.2.0.1，他是9i有關系？

還是10g有個系統表對數據自動統計，現在還沒統計的原因？）

?

?

oracle:block 的 water mark問題