一张表，当用delete删除时，数据是被删除了，可是在数据块中的空间还没有释放，则高水位线
依然在原来的位置，当我们查询该表的数据时候，空块依然还被搜索。为了提高查询效率，降低I/O
的读取，我们不得不进行人为手动降低高水位线。

操作如下：
1、创建表与索引：
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.

2、往表里插入数据：
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.

3、查询数据：
SQL> select count(*) from emp;

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

4、查看表的索引状态：
SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

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

5、查看表的系统信息：
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
---------- ---------- ---------- ---------- ---------- -----------

6、收集emp的统计信息：
SQL> exec dbms_stats.gather_table_stats(user,'EMP')

PL/SQL procedure successfully completed.

7、再次查看表的系统信息：
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

现在看，表中一共有53500条数据，则有53500个rowid，622个快.

8、查看查询执行计划的统计信息：
SQL> set autot trace stat
SQL> select count(*) from emp;

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
现在的该查询语句的 I/O 读取数为585。

9、删除50000条数据再查询：
SQL> set autot off
SQL> delete emp where rownum<=50000;

50000 rows deleted.

SQL> commit;
Commit complete.

10、再次收集emp表的统计信息：
SQL> exec dbms_stats.gather_table_stats(user,'EMP')

PL/SQL procedure successfully completed.

11、再次查询表的系统信息，看到表的数据减少到3500：
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
可以看到，删除了50000条数据后，数据的是少了，可是段中的数据库没有少，依然是622个快，
很明显其中包括了大量的空块。

12、再次收集查询执行计划的统计信息：
SQL> set autotrace trace stat
SQL> select count(*) from emp;

Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
585 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
4 sorts (memory)
0 sorts (disk)
1 rows processed
该查询语句的 I/O 读取数依然为585，可为什么还这么多呢？不是已经删除了的大部分的数据了吗？

13、进行一次行移动：
SQL> set autotrace off
SQL> alter table emp enable row movement;

Table altered.

14、在进行收缩空间：
收缩空快的空间：
SQL> alter table emp shrink space;

Table altered.
这个动作就是回收空块的空间，降低高水位线。

15、收缩后，查看查询的读取统计信息：
SQL> select count(*) from emp;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
38 consistent gets
8 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
这时候，才有效地降低了 I/O 读取数。

16、查看emp索引的状态：
set autot off
SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

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

17、关闭行移动功能：
SQL> alter table emp disable row movement;

Table altered.

18、再次查看行移动功能的状态：
SQL> select table_name,ROW_MOVEMENT from user_tables where table_name='EMP';

TABLE_NAME ROW_MOVE
------------------------------ --------
EMP DISABLED
已关闭。

很明显，当高水位降低后，逻辑读已经大大减少了。此时的性能一定会好于之前。

19、测试行移动或表移动对索引的影响：
SQL> alter table emp move;

Table altered.
表移动.

20、查看表emp索引的状态：
SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

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

表移动后，查看索引状态，发现索引以失效。

21、重建索引：
SQL> alter index IDX_EMP_SALARY rebuild;

Index altered.

22、重建索引后，查看索引的状态：
SQL> select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');

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

表索引已经恢复原来的状态，正常使用。

第一次使用行移动为什么对索引没有影响呢，因为使用shrink收缩表空间维护索引不受影响。
但是需要注意的是，当使用move以后，索引是失效的，需要重新rebuild索引。

ITPub博客

手动降低高水位线

最新文章