索引是我们进行优化的一种重要方式。实际工作中，一个简单的索引，可能就会大大提升提高关键业务作业效率，最终提升用户满意度。在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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-694040/
扫一扫
829
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
