ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引失效系列——索引本身不可用导致的失效

索引失效系列——索引本身不可用导致的失效

原创 Linux操作系统 作者:realkid4 时间:2011-04-28 19:54:18 0 删除 编辑

 

索引是我们进行优化的一种重要方式。实际工作中,一个简单的索引,可能就会大大提升提高关键业务作业效率,最终提升用户满意度。在CBO时代,DBA和开发人员经常为索引为什么不出现在执行计划中而困惑。

 

 

问题提出

 

 

在之前的系列中,我们一直强调数据因素、优化器因素和统计量因素对执行计划的影响。本篇我们聚焦一下索引本身的特性,谈谈由于索引状态的问题引起的不可用失效现象。

 

 

SQL> create table t as select * from dba_objects;

 

Table created

 

SQL> create index idx_t_owner on t(owner);

 

Index created

 

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

 

PL/SQL procedure successfully completed

 

 

 

此时,我们观察实验环境的统计信息。

 

 

SQL> explain plan for select * from t where wner='SCOTT';

 

Explained

 

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

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |  3009 |   285K|    88   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  3009 |   285K|    88   (0)|

|*  2 |   INDEX RANGE SCAN       | IDX_T_OWNER |  3009 |       |     8   (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

 

14 rows selected

 

 

当我们使用scott作为搜索条件的时候,是可以生成索引执行路径的。

 

此时,我们的索引状态为:

 

 

SQL> col owner for a10;

SQL> col index_type for a15;

SQL> select owner,index_name, index_type, table_name, status from dba_indexes where index_name='IDX_T_OWNER';

 

OWNER      INDEX_NAME          INDEX_TYPE      TABLE_NAME  STATUS

---------- ------------------------------ --------------- ------------------------------ --------

SYS        IDX_T_OWNER           NORMAL          T      VALID

 

 

SQL> select segment_name, segment_type, bytes from dba_segments where segment_name='IDX_T_OWNER';

 

SEGMENT_NAME         SEGMENT_TYPE            BYTES

-------------------- ------------------ ----------

IDX_T_OWNER          INDEX                 2097152

 

 

 

索引idx_t_owner的状态为valid,表示为有效索引。有效索引的含义就是保证索引结构和索引列的分布相一致,索引会随着dml操作的进行而自动更新。存储结构方面,索引作为一个段segment结构,占空间为约2M左右。

 

 

索引是可以设置为失效的。强制的切断索引结构和索引列的DML联动机制。

 

 

SQL> alter index idx_t_owner unusable;

 

Index altered

 

 

此时我们观察数据字典中的情况,如:

 

 

SQL> select segment_name, segment_type, bytes from dba_segments where segment_name='IDX_T_OWNER';

 

SEGMENT_NAME         SEGMENT_TYPE            BYTES

-------------------- ------------------ ----------

IDX_T_OWNER          INDEX                 2097152

 

SQL> select owner,index_name, index_type, table_name, status from dba_indexes where index_name='IDX_T_OWNER';

 

OWNER      INDEX_NAME     INDEX_TYPE      TABLE_NAME    STATUS

---------- ------------------------------ --------------- ------------------------------ --------

SYS        IDX_T_OWNER      NORMAL          T                UNUSABLE

 

 

索引的状态已经变化为不可用unusable。那么,这时候我们的执行计划就变化为。

 

 

SQL> explain plan for select * from t where wner='SCOTT';

 

Explained

 

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

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  3009 |   285K|   281   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL| T    |  3009 |   285K|   281   (1)| 00:00:04 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OWNER"='SCOTT')

 

13 rows selected

 

 

 

数据分布无变化、统计量无变化,此时CBO没有选择之前的索引路径,而是选择了全表扫描的方式。CBO不选择特定路径的原因大体三个:统计量错误、成本核算无优势和路径不可行。此时,我们认为只有可能是路径不可行的原因。

 

 

那么,如果我们强制性的使用hint要求CBO执行索引路径呢?

 

 

SQL> select /*+index(t idx_t_owner)*/* from t where wner='SCOTT';

 

select /*+index(t idx_t_owner)*/* from t where wner='SCOTT'

 

ORA-01502: 索引 'SYS.IDX_T_OWNER' 或这类索引的分区处于不可用状态

 

 

结果是报错,Oracle认为,如果没有强制走索引,失效的索引只是不被考虑如执行计划。如果强制其生成索引路径,只有报错提示。

 

此时,我们得到了结论:当索引状态不可用的时候,索引路径是不会出现在生成的执行计划的。如果我们强制要求执行,结果就是报错。

 

 

下面我们测试一下索引和数据列之间的联动关系。

 

//变化数据,增加表容量

SQL> insert into t select * from t;

72209 rows inserted

 

SQL> commit;

Commit complete

 

SQL> col segment_name for a20;

SQL> select segment_name, segment_type, bytes from dba_segments where segment_name='IDX_T_OWNER';

 

SEGMENT_NAME         SEGMENT_TYPE            BYTES

-------------------- ------------------ ----------

IDX_T_OWNER          INDEX                 2097152

 

SQL> select owner,index_name, index_type, table_name, status from dba_indexes where index_name='IDX_T_OWNER';

 

OWNER           INDEX_NAME    INDEX_TYPE       TABLE_NAME  STATUS

------------------------------ ------------------------------ ---------------------------

SYS          IDX_T_OWNER       NORMAL           T           UNUSABLE

 

 

添加了一倍的数据量,索引结构容量没有变化。说明索引和索引列取值之间的关联完全被打断。

 

 

要恢复索引,要通过重建rebuild索引的方式实现。

 

 

SQL> alter index idx_t_owner rebuild;

Index altered

 

SQL> select segment_name, segment_type, bytes from dba_segments where segment_name='IDX_T_OWNER';

 

SEGMENT_NAME         SEGMENT_TYPE            BYTES

-------------------- ------------------ ----------

IDX_T_OWNER          INDEX                 3145728

 

 

SQL> select owner,index_name, index_type, table_name, status from dba_indexes where index_name='IDX_T_OWNER';

 

OWNER       INDEX_NAME     INDEX_TYPE       TABLE_NAME       STATUS

------------------------------ ------------------------------ ---------------------------

SYS       IDX_T_OWNER        NORMAL          T              VALID

 

 

重建后,索引体积立刻增加到合适状态,同时状态恢复为valid

 

//hint起效开始

SQL> select /*+index(t idx_t_owner)*/count(*) from t where wner='SCOTT';

 

  COUNT(*)

----------

        22

 

SQL> explain plan for select * from t where wner='SCOTT';

Explained

 

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

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1516787156

--------------------------------------------------------------------------------

| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |             |  3009 |   285K|   174   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| T           |  3009 |   285K|   174   (0)|

|*  2 |   INDEX RANGE SCAN      | IDX_T_OWNER |  6017 |       |    15   (0)|

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OWNER"='SCOTT')

 

 

索引恢复之后,路径和各方面效果恢复正常。

 

 

索引不可用场景

 

那么,出现索引不可用的场景是否多呢?其实,无论是开发还是日常运维,出现索引不可用的情况还是常见的。下面列举几个:

 

ü        大量数据导入,为提高性能暂时禁止索引。索引的同步是要消耗数据库DML时间的。很多时候为了进行数据导入方便,操作人员就将索引禁用掉。导入之后就忘记了重建。这是投产前会出现的一个场景;

ü        数据表物理位置前移,rowid对应变化,所有的索引结构都是失效的。如下实验所示:

 

 

SQL> select owner,index_name, index_type, table_name, status from dba_indexes where index_name='IDX_T_OWNER';

 

OWNER   INDEX_NAME  INDEX_TYPE  TABLE_NAME           STATUS

------------------------------ ------------------------------

SYS             IDX_T_OWNER        NORMAL  T             VALID

 

SQL> alter table t move;

 

Table altered

 

SQL> select owner,index_name, index_type, table_name, status from dba_indexes where index_name='IDX_T_OWNER';

 

OWNER      INDEX_NAME         INDEX_TYPE       TABLE_NAME  STATUS

------------------------------ ------------------------------ ---------------------------

SYS                            IDX_T_OWNER    NORMAL   T  UNUSABLE

 

 

Alter table XXX move;命令就是将数据表的位置进行前移,借助tablespace参数还可以实现数据表在不同表空间之间移动。默认move的效果,可以有效降低高水位线情况,整理物理存储。Rowid的变化,必然是所有索引叶节点对应的rowid信息全部失效,索引结构失效也就是必然了。

 

ü        分区表中的全局索引也经常遇到失效的场景。当删除一个分区的时候,全局索引会变为失效。局部索引则不会有这个问题。

 

 

索引不可用在实际生产环境下,还是常见的问题,特别是进行移植或者维护工作的时候。所以,在进行这类工作的时候,最好做到合理全面规划。完成之后要监控系统一段时间,看看有没有性能的陡变或者报错信息出现,维持一个平稳过渡的过程。

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

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7752635