ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 表挪动存储空间后,对之上的sql的执行计划的影响的探究

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

原创 Linux操作系统 作者:YallonKing 时间:2012-03-07 18:39:55 0 删除 编辑
以前的一个测试例子,置于此以作记录。
数据库版本:
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的执行计划也会发生变化。

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

请登录后发表评论 登录
全部评论

注册时间:2011-08-07

  • 博文量
    72
  • 访问量
    246088