move tablespace回收表空间

当对表多次执行dml操作后，就产生空间碎片，影响数据库的性能，对此我们就要进行空间碎片整理来回收表空间。回收表空间的方法有以下几种：
1.drop and recreate;
2.truncate and restore from backup;
3.exp/imp;
4.alter table xx shrink space;
5.alter table xx move tablespace XX;

在这里我们主要针对第五种方法move tablespace进行介绍，首先我们来看一下alter table xx move tablespace XX的特点：
1、降低高水位；
2、回收数据块的空闲空间，使数据紧密码放；
3、消除行迁移；
4、可以实现表的压缩；
5、改变存储空间；
6、索引失效，需要重建。

注意在我们使用move tablespace回收表空间时，对象的索引会失效，我们需要重建，否则在执行查询操作时，会大大影响数据库的性能。对此我们就来进行验证：

1、以scott的emp表为例，查看索引情况；

SQL> select table_name,index_name,status from all_indexes where table_name='EMP';

TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
EMP                            PK_EMP                         VALID

2、在move tablespace之前，执行查询操作；

SQL> conn scott/tiger;
Connected.

SQL> set autot on;

SQL> select * from emp where empno='7839';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81       5102                    10

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    32 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    32 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

由此看出在执行select语句时，使用了索引PK_EMP。

3、移动表空间；

SQL> alter table emp move tablespace users;
Table altered.

4、执行查询操作，看索引的使用情况；

SQL> select * from emp where empno='7900';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81       1052                    30

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    32 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

由此看出在执行select语句时，没有使用索引，而是走的全表扫描，sql语句的执行性能降低。


5、重建索引；

SQL> alter index PK_EMP rebuild;
Index altered.


6、在此执行上面的sql语句；

SQL> select * from emp where empno='7900';

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81       1052                    30

Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    32 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    32 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

由此看出重建索引PK_EMP后，sql语句执行时使用的是索引唯一扫描。


经以上步骤我们证明了move tablespace与index rebuilds是分不开的，当我们在使用move tablespace回收表空间时，如果对象存在索引，一定要重建索引，否则索引会失效 。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29457434/viewspace-1224767/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29457434/viewspace-1224767/