降低高水位
    
SQL> create table emp tablespace users as select * from hr.employees where 1=0;

Table created.

SQL> create index idx_emp_salary on emp(salary) tablespace users;

Index created.

SQL>
SQL>
SQL> 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.

SQL> select count(*) from emp;       

  COUNT(*)
----------
     53500

SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_EMP_SALARY                 VALID

SQL> SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
  2         FROM user_tables where table_name = 'EMP';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------


SQL> exec dbms_stats.gather_table_stats(user,'EMP')

PL/SQL procedure successfully completed.
 
  SQL> SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
  2         FROM user_tables where table_name = 'EMP';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
     53500        622          0          0          0          69

SQL>
SQL> set autot trace stat
SQL>
SQL> select count(*) from emp;


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        573  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
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autot off;
SQL>
 SQL> delete  emp where rownum<=50000;

50000 rows deleted.

SQL> commit
  2  ;

Commit complete.

SQL>  exec dbms_stats.gather_table_stats(user,'EMP')

PL/SQL procedure successfully completed.

SQL> SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
  2      FROM user_tables where table_name = 'EMP';

  NUM_ROWS     BLOCKS      EMPTY  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
      3500        622          0          0          0          69
 SQL>  set autotrace trace stat
SQL>
SQL> select count(*) from emp;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        569  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
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> alter table emp enable row movement
  2  ;

Table altered.

SQL>
SQL> alter table emp shrink space;

Table altered.

SQL>
SQL> set autot trace stat
SQL>
SQL> select count(*) from emp ;


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         38  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

SQL> set autot off
SQL>
SQL>
SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_EMP_SALARY                 VALID

SQL> select table_name,ROW_MOVEMENT from user_tables where table_name='EMP';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
EMP                            ENABLED

SQL> alter table emp disable row  movement;

Table altered.

SQL> select table_name,ROW_MOVEMENT from user_tables where table_name='EMP';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
EMP                            DISABLED

SQL>
1：很明显，当高水位降低后，逻辑读已经大大减少了。此时的性能一定会好于之前。
2：使用shrink space，索引依然为有效状态。


————————————————————
另：除了使用shrink space外，也可以使用move的方式移动数据，也可以达到降低水位线的效果。如下：

SQL> alter table emp move;

Table altered.

SQL>
SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_EMP_SALARY                 UNUSABLE

SQL> alter index IDX_EMP_SALARY rebuild;

Index altered.

SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

INDEX_NAME                     STATUS
------------------------------ --------
IDX_EMP_SALARY                 VALID

SQL>
****-但是需要注意的是，当使用move以后，索引是失效的，需要重新rebuild索引。