看了小布老师关于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有个系统表对数据自动统计，现在还没统计的原因？）
