对于一些删除操作如delete，虽然数据删除了，但是并没有提高性能，原因就是数据删除了，但数据原先占的block的位置置空却不许别的数据使用，存在碎片，高水位线没有降下来。这样对于dml、ddl操作仍然是从HWM线以下收缩，性能并没有提升。
解决办法:1.truncate table或drop table
2.shrink table收缩表
3.move table 移动表
实验之shrink table以降低HWM:
——创建环境在sys用户下建空表emp
SYS@ORA11GR2>create table emp tablespace users as select * from hr.employees where 1=0;
Table created.
SYS@ORA11GR2>select count(*) from emp;
COUNT(*)
----------
0
——建立索引（全局索引，默认）
SYS@ORA11GR2>create index idx_emp_salary on emp(salary) tablespace users;
Index created.
——插入数据：
SYS@ORA11GR2>begin
2 for i in 1.. 500 loop
3 insert into emp select * from hr.employees;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
——查看emp表：
SYS@ORA11GR2>select count(*) from emp;
COUNT(*)
----------
53500
——查看索引状态：
SYS@ORA11GR2>select index_name,status from user_indexes where index_name=upper('idx_emp_salary');
INDEX_NAME STATUS
------------------------------ --------
IDX_EMP_SALARY VALID
——查看emp表的行数、块数等情况：（因为是刚建的新表，没有统计信息，所以值为空）
SYS@ORA11GR2>select num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len from user_tables where table_name='EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
——对sys用户的emp进行手工信息统计：
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'EMP');
PL/SQL procedure successfully completed.
——再次查看emp表的行数、块数等情况（因为有统计信息了，所以有值了）
SYS@ORA11GR2>select num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len from user_tables where table_name='EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
53500 622 0 0 0 69
--对emp表执行查询动作以查看其执行计划，此时的逻辑读为585
开启只收集统计信息模式：
SYS@ORA11GR2>set autot trace stat
操作：
SYS@ORA11GR2>select count(*) from emp;
此时的逻辑读为598
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
585 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@ORA11GR2>set autot off;
--删除大部分数据（剩3500行记录）
SYS@ORA11GR2>delete emp where rownum<=50000;
50000 rows deleted.
SYS@ORA11GR2>commit;
Commit complete.
——再次查看（因为还没有收集统计信息，索引值跟删除前的数据一样）
SYS@ORA11GR2>select num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len from user_tables where table_name='EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
53500 622 0 0 0 69
--再次收集EMP表的统计信息
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'EMP');
PL/SQL procedure successfully completed.
--我们看到，行数已经减少至3500条，占用的block却依然是622个
SYS@ORA11GR2>select num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len from user_tables where table_name='EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
3500 622 0 0 0 69
--下面的执行计划，我们可以看到，逻辑读是572个（降低了一点点）。
SYS@ORA11GR2>set autot trace stat
SYS@ORA11GR2>select count(*) from emp;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
572 consistent gets
0 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SYS@ORA11GR2>set autot off
——解决逻辑读及块的碎片（即降低高水位）
SYS@ORA11GR2>alter table emp enable row movement;（前提：允许行移动）
Table altered.
SYS@ORA11GR2>alter table emp shrink space;（收缩表空间）
Table altered.
查看emp表的块状况：（依然没变，因为统计信息还没有及时收集）
SYS@ORA11GR2>select num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len from user_tables where table_name='EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
3500 622 0 0 0 69
收集统计信息：
SYS@ORA11GR2>exec dbms_stats.gather_table_stats(user,'EMP');
PL/SQL procedure successfully completed.
——再次查看emp表的块状况（块数明显降低了）
SYS@ORA11GR2>select num_rows,blocks,empty_blocks as empty,avg_space,chain_cnt,avg_row_len from user_tables where table_name='EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
3500 36 0 0 0 69
——再次查看统计信息：（逻辑读已经明显降低）
SYS@ORA11GR2>set autot trace stat
SYS@ORA11GR2>select count(*) from emp;
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
55 consistent gets
1 physical reads
0 redo size
526 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
——查看索引状态，依然有效;
SYS@ORA11GR2>set autot off
SYS@ORA11GR2>select index_name,status from user_indexes where index_name=upper('idx_emp_salary');
INDEX_NAME STATUS
------------------------------ --------
IDX_EMP_SALARY VALID
--恢复默认状态，禁用行移动
先查看：
SYS@ORA11GR2>select table_name,row_movement from user_tables where table_name='EMP';
TABLE_NAME ROW_MOVE
--------------- --------
EMP ENABLED
再修改：
SYS@ORA11GR2>alter table emp disable row movement;
Table altered.
验证：
SYS@ORA11GR2>select table_name,row_movement from user_tables where table_name='EMP';
TABLE_NAME ROW_MOVE
--------------- --------
EMP DISABLED
1）很明显，当高水位降低后，逻辑读已经大大减少了。此时的性能一定会好于之前。
2）使用shrink space，索引依然为有效状态。
另：除了使用shrink space外，也可以使用move的方式移动数据，也可以达到降低水位线的效果。如下：
SYS@ORA11GR2>alter table emp move;
Table altered.
——查看索引状态：
SYS@ORA11GR2>select index_name,status from user_indexes where index_name=upper('idx_emp_salary');
INDEX_NAME STATUS
------------------------------ --------
IDX_EMP_SALARY UNUSABLE
——重建索引：
SYS@ORA11GR2>alter index idx_emp_salary rebuild;
Index altered.
——验证：
SYS@ORA11GR2>select index_name,status from user_indexes where index_name=upper('idx_emp_salary');
INDEX_NAME STATUS
------------------------------ --------
IDX_EMP_SALARY VALID
注意：当使用move以后，索引是失效的，需要重新rebuild索引