表挪动存储空间后，对之上的sql的执行计划的影响的探究

数据库版本：

SQL> SELECT * FROM (SELECT * FROM V$VERSION) WHERE ROWNUM<5;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production

测试表为csdn的那个600w+的表，大家懂的...
SQL> desc csdn
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
PASSWD VARCHAR2(100)
EMAIL VARCHAR2(100)

SQL> select * from (select name from csdn) where rownum<3;

NAME
------------------------------
zdg
LaoZheng

SQL> select table_name,tablespace_name from user_tables where table_name='CSDN';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
CSDN A

创建测试索引
SQL> create index idxcsdn on csdn(name);

Index created.

收集并查看执行计划
SQL> explain plan for select name from csdn where name='zdg';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 141053954

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDXCSDN | 1 | 11 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("NAME"='zdg')

13 rows selected.

注：显然是走的索引idxcsdn

挪动表所在表空间
SQL> alter table csdn move tablespace b;

Table altered.

再次收集并查看执行计划
SQL> explain plan for select name from csdn where name='zdg';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3550140955

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 6854 (1)| 00:01:23 |
|* 1 | TABLE ACCESS FULL| CSDN | 1 | 11 | 6854 (1)| 00:01:23 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME"='zdg')

13 rows selected.

注：显然不走索引，而是全表扫描

再次将表挪回原来的表空间
SQL> alter table csdn move tablespace a;

Table altered.

再次收集并查看执行计划
SQL> explain plan for select name from csdn where name='zdg';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3550140955

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 6854 (1)| 00:01:23 |
|* 1 | TABLE ACCESS FULL| CSDN | 1 | 11 | 6854 (1)| 00:01:23 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME"='zdg')

13 rows selected.

注：即使挪回原来的表空间，还是不走索引，走全表扫描

重建索引
SQL> alter index idxcsdn rebuild;

Index altered.

再次收集并查看执行计划
SQL> explain plan for select name from csdn where name='zdg';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 141053954

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDXCSDN | 1 | 11 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("NAME"='zdg')

13 rows selected.

注：sql执行计划又恢复正常!

结论：只要表挪动到表空间，其上的索引均失效，无论是挪到新的表空间或是挪回原始表空间。