表挪動儲存空間後，對之上的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執行計劃又恢復正常!

結論：將表挪動表空間後，在表之上的sql執行計劃將發生變化，甚至是將表又挪回原來的表空間，表之上的sql的執行計劃也會發生變化。