索引失效系列索引本身不可用导致的失效.docx
- 文档编号:5059122
- 上传时间:2022-12-12
- 格式:DOCX
- 页数:7
- 大小:17.11KB
索引失效系列索引本身不可用导致的失效.docx
《索引失效系列索引本身不可用导致的失效.docx》由会员分享,可在线阅读,更多相关《索引失效系列索引本身不可用导致的失效.docx(7页珍藏版)》请在冰豆网上搜索。
索引失效系列索引本身不可用导致的失效
索引失效系列——索引本身不可用导致的失效
索引是我们进行优化的一种重要方式。
实际工作中,一个简单的索引,可能就会大大提升提高关键业务作业效率,最终提升用户满意度。
在CBO时代,DBA和开发人员经常为索引为什么不出现在执行计划中而困惑。
问题提出
在之前的系列中,我们一直强调数据因素、优化器因素和统计量因素对执行计划的影响。
本篇我们聚焦一下索引本身的特性,谈谈由于索引状态的问题引起的不可用失效现象。
SQL>createtabletasselect*fromdba_objects;
Tablecreated
SQL>createindexidx_t_owneront(owner);
Indexcreated
SQL>execdbms_stats.gather_table_stats(user,'T',cascade=>true);
PL/SQLproceduresuccessfullycompleted
此时,我们观察实验环境的统计信息。
SQL>explainplanforselect*fromtwherewner='SCOTT';
Explained
SQL>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"='SCOTT')
14rowsselected
当我们使用scott作为搜索条件的时候,是可以生成索引执行路径的。
此时,我们的索引状态为:
SQL>colownerfora10;
SQL>colindex_typefora15;
SQL>selectowner,index_name,index_type,table_name,statusfromdba_indexeswhereindex_name='IDX_T_OWNER';
OWNERINDEX_NAMEINDEX_TYPETABLE_NAMESTATUS
---------------------------------------------------------------------------------------------
SYSIDX_T_OWNERNORMALTVALID
SQL>selectsegment_name,segment_type,bytesfromdba_segmentswheresegment_name='IDX_T_OWNER';
SEGMENT_NAMESEGMENT_TYPEBYTES
------------------------------------------------
IDX_T_OWNERINDEX2097152
索引idx_t_owner的状态为valid,表示为有效索引。
有效索引的含义就是保证索引结构和索引列的分布相一致,索引会随着dml操作的进行而自动更新。
存储结构方面,索引作为一个段segment结构,占空间为约2M左右。
索引是可以设置为失效的。
强制的切断索引结构和索引列的DML联动机制。
SQL>alterindexidx_t_ownerunusable;
Indexaltered
此时我们观察数据字典中的情况,如:
SQL>selectsegment_name,segment_type,bytesfromdba_segmentswheresegment_name='IDX_T_OWNER';
SEGMENT_NAMESEGMENT_TYPEBYTES
------------------------------------------------
IDX_T_OWNERINDEX2097152
SQL>selectowner,index_name,index_type,table_name,statusfromdba_indexeswhereindex_name='IDX_T_OWNER';
OWNERINDEX_NAMEINDEX_TYPETABLE_NAMESTATUS
---------------------------------------------------------------------------------------------
SYSIDX_T_OWNERNORMALTUNUSABLE
索引的状态已经变化为不可用unusable。
那么,这时候我们的执行计划就变化为。
SQL>explainplanforselect*fromtwherewner='SCOTT';
Explained
SQL>select*fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Planhashvalue:
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:
00:
04|
--------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
1-filter("OWNER"='SCOTT')
13rowsselected
数据分布无变化、统计量无变化,此时CBO没有选择之前的索引路径,而是选择了全表扫描的方式。
CBO不选择特定路径的原因大体三个:
统计量错误、成本核算无优势和路径不可行。
此时,我们认为只有可能是路径不可行的原因。
那么,如果我们强制性的使用hint要求CBO执行索引路径呢?
SQL>select/*+index(tidx_t_owner)*/*fromtwherewner='SCOTT';
select/*+index(tidx_t_owner)*/*fromtwherewner='SCOTT'
ORA-01502:
索引'SYS.IDX_T_OWNER'或这类索引的分区处于不可用状态
结果是报错,Oracle认为,如果没有强制走索引,失效的索引只是不被考虑如执行计划。
如果强制其生成索引路径,只有报错提示。
此时,我们得到了结论:
当索引状态不可用的时候,索引路径是不会出现在生成的执行计划的。
如果我们强制要求执行,结果就是报错。
下面我们测试一下索引和数据列之间的联动关系。
//变化数据,增加表容量
SQL>insertintotselect*fromt;
72209rowsinserted
SQL>commit;
Commitcomplete
SQL>colsegment_namefora20;
SQL>selectsegment_name,segment_type,bytesfromdba_segmentswheresegment_name='IDX_T_OWNER';
SEGMENT_NAMESEGMENT_TYPEBYTES
------------------------------------------------
IDX_T_OWNERINDEX2097152
SQL>selectowner,index_name,index_type,table_name,statusfromdba_indexeswhereindex_name='IDX_T_OWNER';
OWNERINDEX_NAMEINDEX_TYPETABLE_NAMESTATUS
---------------------------------------------------------------------------------------
SYSIDX_T_OWNERNORMALTUNUSABLE
添加了一倍的数据量,索引结构容量没有变化。
说明索引和索引列取值之间的关联完全被打断。
要恢复索引,要通过重建rebuild索引的方式实现。
SQL>alterindexidx_t_ownerrebuild;
Indexaltered
SQL>selectsegment_name,segment_type,bytesfromdba_segmentswheresegment_name='IDX_T_OWNER';
SEGMENT_NAMESEGMENT_TYPEBYTES
------------------------------------------------
IDX_T_OWNERINDEX3145728
SQL>selectowner,index_name,index_type,table_name,statusfromdba_indexeswhereindex_name='IDX_T_OWNER';
OWNERINDEX_NAMEINDEX_TYPETABLE_NAMESTATUS
---------------------------------------------------------------------------------------
SYSIDX_T_OWNERNORMALTVALID
重建后,索引体积立刻增加到合适状态,同时状态恢复为valid。
//hint起效开始
SQL>select/*+index(tidx_t_owner)*/count(*)fromtwherewner='SCOTT';
COUNT(*)
----------
22
SQL>explainplanforselect*fromtwherewner='SCOTT';
Explained
SQL>select*fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Planhashvalue:
1516787156
--------------------------------------------------------------------------------
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|
--------------------------------------------------------------------------------
|0|SELECTSTATEMENT||3009|285K|174(0)|
|1|TABLEACCESSBYINDEXROWID|T|3009|285K|174(0)|
|*2|INDEXRANGESCAN|IDX_T_OWNER|6017||15(0)|
--------------------------------------------------------------------------------
PredicateInformation(identifiedbyoperationid):
---------------------------------------------------
2-access("OWNER"='SCOTT')
索引恢复之后,路径和各方面效果恢复正常。
索引不可用场景
那么,出现索引不可用的场景是否多呢?
其实,无论是开发还是日常运维,出现索引不可用的情况还是常见的。
下面列举几个:
✓ 大量数据导入,为提高性能暂时禁止索引。
索引的同步是要消耗数据库DML时间的。
很多时候为了进行数据导入方便,操作人员就将索引禁用掉。
导入之后就忘记了重建。
这是投产前会出现的一个场景;
✓ 数据表物理位置前移,rowid对应变化,所有的索引结构都是失效的。
如下实验所示:
SQL>selectowner,index_name,index_type,table_name,statusfromdba_indexeswhereindex_name='IDX_T_OWNER';
OWNERINDEX_NAMEINDEX_TYPETABLE_NAMESTATUS
------------------------------------------------------------
SYSIDX_T_OWNERNORMALTVALID
SQL>altertabletmove;
Tablealtered
SQL>selectowner,index_name,index_type,table_name,statusfromdba_indexeswhereindex_name='IDX_T_OWNER';
OWNERINDEX_NAMEINDEX_TYPETABLE_NAMESTATUS
---------------------------------------------------------------------------------------
SYSIDX_T_OWNERNORMALTUNUSABLE
AltertableXXXmove;命令就是将数据表的位置进行前移,借助tablespace参数还可以实现数据表在不同表空间之间移动。
默认move的效果,可以有效降低高水位线情况,整理物理存储。
Rowid的变化,必然是所有索引叶节点对应的rowid信息全部失效,索引结构失效也就是必然了。
✓ 分区表中的全局索引也经常遇到失效的场景。
当删除一个分区的时候,全局索引会变为失效。
局部索引则不会有这个问题。
索引不可用在实际生产环境下,还是常见的问题,特别是进行移植或者维护工作的时候。
所以,在进行这类工作的时候,最好做到合理全面规划。
完成之后要监控系统一段时间,看看有没有性能的陡变或者报错信息出现,维持一个平稳过渡的过程。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 索引 失效 系列 本身 可用 导致