索引失效系列索引本身不可用导致的失效Word文档格式.docx

索引失效系列索引本身不可用导致的失效Word文档格式.docx

《索引失效系列索引本身不可用导致的失效Word文档格式.docx》由会员分享，可在线阅读，更多相关《索引失效系列索引本身不可用导致的失效Word文档格式.docx（7页珍藏版）》请在冰豆网上搜索。

Indexcreated

execdbms_stats.gather_table_stats（user,'

T'

cascade=>

true）;

PL/SQLproceduresuccessfullycompleted

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

explainplanforselect*fromtwherewner='

SCOTT'

;

Explained

select*fromtable（dbms_xplan.display）;

PLAN_TABLE_OUTPUT

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

Planhashvalue:

1516787156

|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|

|0|SELECTSTATEMENT||3009|285K|88（0）|

|1|TABLEACCESSBYINDEXROWID|T|3009|285K|88（0）|

|*2|INDEXRANGESCAN|IDX_T_OWNER|3009||8（0）|

PredicateInformation（identifiedbyoperationid）:

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

2-access（"

OWNER"

='

）

14rowsselected

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

此时，我们的索引状态为：

colownerfora10;

colindex_typefora15;

selectowner,index_name,index_type,table_name,statusfromdba_indexeswhereindex_name='

IDX_T_OWNER'

OWNERINDEX_NAMEINDEX_TYPETABLE_NAMESTATUS

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

SYSIDX_T_OWNERNORMALTVALID

selectsegment_name,segment_type,bytesfromdba_segmentswheresegment_name='

SEGMENT_NAMESEGMENT_TYPEBYTES

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

IDX_T_OWNERINDEX2097152

索引idx_t_owner的状态为valid，表示为有效索引。

有效索引的含义就是保证索引结构和索引列的分布相一致，索引会随着dml操作的进行而自动更新。

存储结构方面，索引作为一个段segment结构，占空间为约2M左右。

索引是可以设置为失效的。

强制的切断索引结构和索引列的DML联动机制。

alterindexidx_t_ownerunusable;

Indexaltered

此时我们观察数据字典中的情况，如：

SYSIDX_T_OWNERNORMALTUNUSABLE

索引的状态已经变化为不可用unusable。

那么，这时候我们的执行计划就变化为。

1601196873

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

|Id|Operation|Name|Rows|Bytes|Cost（%CPU）|Time|

|0|SELECTSTATEMENT||3009|285K|281

（1）|00:

00:

04|

|*1|TABLEACCESSFULL|T|3009|285K|281

（1）|00:

1-filter（"

13rowsselected

数据分布无变化、统计量无变化，此时CBO没有选择之前的索引路径，而是选择了全表扫描的方式。

CBO不选择特定路径的原因大体三个：

统计量错误、成本核算无优势和路径不可行。

此时，我们认为只有可能是路径不可行的原因。

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

select/*+index（tidx_t_owner）*/*fromtwherewner='

select/*+index（tidx_t_owner）*/*fromtwherewner='

ORA-01502:

索引'

SYS.IDX_T_OWNER'

或这类索引的分区处于不可用状态

结果是报错，Oracle认为，如果没有强制走索引，失效的索引只是不被考虑如执行计划。

如果强制其生成索引路径，只有报错提示。

此时，我们得到了结论：

当索引状态不可用的时候，索引路径是不会出现在生成的执行计划的。

如果我们强制要求执行，结果就是报错。

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

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

insertintotselect*fromt;

72209rowsinserted

commit;

Commitcomplete

colsegment_namefora20;

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

添加了一倍的数据量，索引结构容量没有变化。

说明索引和索引列取值之间的关联完全被打断。

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

alterindexidx_t_ownerrebuild;

IDX_T_OWNERINDEX3145728

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

//hint起效开始

select/*+index（tidx_t_owner）*/count（*）fromtwherewner='

COUNT（*）

----------

22

|0|SELECTSTATEMENT||3009|285K|174（0）|

|1|TABLEACCESSBYINDEXROWID|T|3009|285K|174（0）|

|*2|INDEXRANGESCAN|IDX_T_OWNER|6017||15（0）|

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

索引不可用场景

那么，出现索引不可用的场景是否多呢？

其实，无论是开发还是日常运维，出现索引不可用的情况还是常见的。

下面列举几个：

✓ 

大量数据导入，为提高性能暂时禁止索引。

索引的同步是要消耗数据库DML时间的。

很多时候为了进行数据导入方便，操作人员就将索引禁用掉。

导入之后就忘记了重建。

这是投产前会出现的一个场景；

数据表物理位置前移，rowid对应变化，所有的索引结构都是失效的。

如下实验所示：

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

SYSIDX_T_OWNERNORMALTVALID

altertabletmove;

Tablealtered

SYSIDX_T_OWNERNORMALTUNUSABLE

AltertableXXXmove；

命令就是将数据表的位置进行前移，借助tablespace参数还可以实现数据表在不同表空间之间移动。

默认move的效果，可以有效降低高水位线情况，整理物理存储。

Rowid的变化，必然是所有索引叶节点对应的rowid信息全部失效，索引结构失效也就是必然了。

分区表中的全局索引也经常遇到失效的场景。

当删除一个分区的时候，全局索引会变为失效。

局部索引则不会有这个问题。

索引不可用在实际生产环境下，还是常见的问题，特别是进行移植或者维护工作的时候。

所以，在进行这类工作的时候，最好做到合理全面规划。

完成之后要监控系统一段时间，看看有没有性能的陡变或者报错信息出现，维持一个平稳过渡的过程。