hwm概念的疑问

显示全部楼层 · 2010-3-1 11:08:30

有一个表在不停的insert，hwm会变化么？
如果采用insert /*+ append */ 或者sqlldr direct=true 那么hwm不做变化
新的数据块加入到hwm之上，oracle在做全表扫描时不检索到hwm完成么？那么基于hwm之上的数据块该怎么办呢？

千问 · 2010-3-1 11:08:30

??????
没人说么？

千问 · 2010-3-1 11:08:30

hwm都会变化的

千问 · 2010-3-1 11:08:30

hwm 当你插入数据后，会上升的，他表示的是你插入最多的纪录的位置。
当用户扫描全表时，系统到hwm的位置，从未用过的数据块不做扫描，这样可以节省时间，
当用户作delete操作时，数据减少，但hwm不降低，truncate时hwm下降，数据块资源被释放为未用过的状态.

千问 · 2010-3-1 11:08:30

"新的数据块加入到hwm之上，oracle在做全表扫描时不检索到hwm完成么？那么基于hwm之上的数据块该怎么办呢？"
新数据是不会在HWM之上的,数据插入，HWM就会提升
你想想轮船的吃水线，是一个道理.
HWM之上是未格式化的block，是不包含数据的.

千问 · 2010-3-1 11:08:30

SQL> exec show_space('T1','CFH');
Free Blocks.............................0
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................15
Unused Bytes............................61440
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................1
PL/SQL procedure successfully completed--hwm为1
SQL> desc cfh.t1
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
XCHAR(2000) Y

SQL> insert into cfh.t1 values('1');
1 row inserted
SQL> insert into cfh.t1 values('2');
1 row inserted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................1
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3
SQL> delete from cfh.t1;
2 rows deleted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................2
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3

SQL> insert /*+ append */ into cfh.t1 values('3');
1 row inserted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................2
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3
SQL> insert /*+ append */ into cfh.t1 values('4');
1 row inserted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................1
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3
那位给看看。用append方式并没有增加hwm,只是重用的以前的空间呢？
我的数据库为9.2

千问 · 2010-3-1 11:08:30

最初由 xxscfh 发布
[B]SQL> exec show_space('T1','CFH');
Free Blocks.............................0
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................15
Unused Bytes............................61440
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................1
PL/SQL procedure successfully completed--hwm为1
SQL> desc cfh.t1
Name Type Nullable Default Comments
---- ---------- -------- ------- --------
XCHAR(2000) Y

SQL> insert into cfh.t1 values('1');
1 row inserted
SQL> insert into cfh.t1 values('2');
1 row inserted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................1
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3
SQL> delete from cfh.t1;
2 rows deleted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................2
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3

SQL> insert /*+ append */ into cfh.t1 values('3');
1 row inserted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................2
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3
SQL> insert /*+ append */ into cfh.t1 values('4');
1 row inserted
SQL> commit;
Commit complete
SQL> exec show_space('T1','CFH');
Free Blocks.............................1
Total Blocks............................16
Total Bytes.............................65536
Unused Blocks...........................13
Unused Bytes............................53248
Last Used Ext FileId....................11
Last Used Ext BlockId...................33
Last Used Block.........................3
PL/SQL procedure successfully completed--hwm为3
那位给看看。用append方式并没有增加hwm,只是重用的以前的空间呢？
我的数据库为9.2 [/B]
是不是ASSM的？
参考：
http://www.eygle.com/Assm_Hwm_research.htm