降低HWM时需要注意的问题
想要降低HWM，有2中方法，alter table MOVE， alter table SHRINK 。
ALTER TABLE MOVE 步骤:
1. desc username.table_name ----检查表中是否有LOB
2. 如果表没有LOB字段
直接 alter table move; 然后 rebuild index
如果表中包含了LOB字段
alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lobsegment tablespace tablespace_name;
也可以单独move lob，但是表上面的index 同样会失效，这是不推荐的
alter table owner.table_name move lob(lob_column) store as lobsegment tablespace tablespace_name ;
3. rebuild index
首先用下面的SQL查看表上面有哪类索引:
select a.owner,a.index_name,a.index_type,a.partitioned,a.status,b.status p_status,b.composite from dba_indexes
a left join dba_ind_partitions b on a.owner=b.index_owner and a.index_name=b.index_name where a.owner='&owner' and a.table_name='&table_name';
对于普通索引直接rebuild online nologging parallel,对于分区索引，必须单独rebuild 每个分区,对于组合分区索引,必须单独rebuild 每个子分区。
4.对表收集统计信息
我通常采取
Move 来降低HWM，因为Move 与 Shrink算法不一样，Move 操作比Shrink快
来自metalink note:577375.1:
The shrink algorithm starts from the bottom of the segment and starts moving those rows to the beginning of the segment. Shrink is a combination of delete/insert pair for every row movement and this generates many UNDO and REDO blocks .
Move从segment的底部开始，move这些rows到segment的头部。Shrink则是delete/insert相结合，这样会产生非常多的UNDO和REDO。
关于MOVE , SHRINK效率比较实验
SQL> create table t as select * from dba_objects;
Table created.
SQL> delete from t where rownum<=20000;
20000 rows deleted.
SQL> commit;
Commit complete.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter table t move;
Table altered.
SQL> alter session set events '10046 trace name context off';
---找到trace文件，我将其改名为 move.trc tkprof move.trc move.txt sys=yes waits=yes explain=robinson/oracle
alter table t move
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.45 0.71 198 741 1057 30446
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.46 0.72 198 742 1057 30446
SQL> drop table t purge;
Table dropped.
SQL> create table t as select * from dba_objects;
Table created.
SQL> delete from t where rownum<=20000;
20000 rows deleted.
SQL> commit;
Commit complete.
SQL> alter table t enable row movement;
Table altered.
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter table t shrink space;
Table altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
---找到trace文件，我将其改名为shrink.trc tkprof shrink.trc shrink.txt sys=yes waits=yes explain=robinson/oracle
alter table t shrink space
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 2.67 14.94 183 1265 50349 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 2.67 14.94 183 1265 50349 0
可以看到move速度是 shrink的 14.94/0.72=20.75倍，shrink耗费cpu,产生很多current block这样生成巨大的redo与undo 所以强烈推荐用MOVE降低HWM
