? ? ?看了小布老師關(guān)于block里面數(shù)據(jù)存儲(chǔ)的high water mark的實(shí)驗(yàn)，自己也做了一遍。

?

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

?

?

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

通過上面的實(shí)驗(yàn)，我們可以看出water mark的作用。

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

如果truncate數(shù)據(jù)，也就是說，oracle發(fā)現(xiàn)water mark=0，直接就返回了。其實(shí)truncate是把表的segment給清除了，自然不占用空間。

?

（有個(gè)疑問，小布老師實(shí)驗(yàn)里面，physical reads是比較大的；我的實(shí)驗(yàn)里面physical reads=0；

也就是說數(shù)據(jù)一直保存在內(nèi)存里面，沒有保存到硬盤；我的環(huán)境是10.2.0.1，他是9i有關(guān)系？

還是10g有個(gè)系統(tǒng)表對數(shù)據(jù)自動(dòng)統(tǒng)計(jì)，現(xiàn)在還沒統(tǒng)計(jì)的原因？）

?

?

oracle:block 的 water mark問題