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

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

索引是我们进行优化的一种重要方式。实际工作中，一个简单的索引，可能就会大大提升提高关键业务作业效率，最终提升用户满意度。在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