甲骨文指数索引无效的原因与解决方法

　　

　　

最近遇到一个Oracle SQL语句的性能问题,修改功能之前的运行时间平均为0.3秒,可是添加新功能后,时间达到了4 ~ 5 s。虽然几张表的数据量都比较大(都在百万级以上),但是也都有正确创建索引,不知道到底慢在了哪里、下面展开调查。

　　

经过几次排除,把问题范围缩小在索引上,首先在确定索引本身没有问题的前提下,考虑索引有没有被使用的到,那么新的问题来了,怎么知道指定索引是否被启用。

　　

　　

<强> 1。分析索引

　　

即将索引至于监控状态下,对索引进行分析。如下对ID_TT_SHOHOU_HIST_002索引进行分析
　　

　　 　　 　　改变指数ID_TT_SHOHOU_HIST_002监测使用; 　　 　　

2。查看v $ object_usage视图中记录的信息
　　

　　 　　 　　select * from v $ object_usage; 　　 　　

凹坠俏闹甘饕扌У脑蛴虢饩龇椒?

　　

字段依次为:
　　

　　

& # 8226; INDEX_NAME——索引名
　　

　　

& # 8226; TABLE_NAME——表名
　　

　　

& # 8226;监测——是否被监控
　　

　　

& # 8226;使用——是否被启用
　　

　　

& # 8226; START_MONITORING——监控开始时间
　　

　　

& # 8226; END_MONITORING——监控结束时间

　　

如上图,虽然索引已经被引用,但是速度依旧很慢,莫非是虽然启用了索引,但是又被其他的一些原因拖慢了速度,继续调查。

　　

调查途中,收集到一些甲骨文数据库不走索引的原因分享给大家

　　

　　

<强> 1。在索引列上使用函数时不会使用索引

　　

例如常见的,TO_CHAR, TO_DATE, TO_NUMBER TRUNC…等等。

　　

此时的解决办法可以使用函数索引,顾名思义就是把使用函数后的字段整体当成索引中的字段。

　　

如下图中的<代码> TO_CHAR (SHOHOU_DATE, '名称')就是一个函数索引,因为日期字段中含有时分秒,进行日期比较的时候,必须转化成固定的格式。
　　

　　 　　 　　创建索引ID_TT_SHOHOU_HIST_003 　　在TT_SHOHOU_HIST 　　(DEL_FLG TO_CHAR (SHOHOU_DATE '名称'),SHOHOU_ID) 　　表空间SALESPA_INDEX 　　 　　

2。索引的列进行隐式的类型转换
　　

　　 　　 　　SELECT *从表INDEX_COLUM=5 　　 　　

上面语句中的INDEX_COLUM字段类型为VARCHAR2,这时就会发生隐式类型转换,类似于
　　

　　 　　 　　SELECT *从表TO_NUMBER (INDEX_COLUM)=5 　　 　　

3。在子句中使用不等于操作

　　

不等于操作包括:& lt;比;!=,科勒姆祝辞=& # 63;,而不是科勒姆& lt;=& # 63;
　　

　　

替代方式可以使用,或者<代码>科勒姆& lt;比;0=====比;科勒姆比;0或科勒姆& lt;0;

　　

4。使用是零和NOT NULL

　　

替代方式:函数索引

　　

通过nvl (b, c)将为空的字段转为不为空的c值,再在函数nvl (b, c)上建立函数索引

　　

转换前
　　

　　 　　 　　SELECT * FROM A, B=NULL 　　 　　

转换后
　　

　　 　　 　　SELECT *从哪里NVL (B, C)=C 　　 　　

5。组合索引

　　

组合索引:由多个列构成的索引。如
　　

　　 　　 　　创建索引INDEX_EMP> 　　选择/* +指数(TTSH ID_TT_SHOHOU_HIST_002) */TO_DATE (TO_CHAR (TTSH。SHOHOU_DATE '名称'),作为SHOHOU_DATE '名称') 　　从TT_SHOHOU_HIST TTSH 　　在哪里…之前 　　 　　

至此,SQL的效率问题已经解决了,但是这不是最好的解决方案。

　　

首先,目前的索引中已经存在包含<代码> TO_CHAR (TTSH。SHOHOU_DATE, '名称')的函数索引,又再创建一个<代码> TO_DATE (TO_CHAR (TTSH。SHOHOU_DATE '名称')'名称')> 　　

其次,强制使用索引的方法需要在SQL中指定索引名,假如数据库中的索引名发生变更,还需去更改SQL。

　　

最好的方法是把索引字段的TO_DATE去掉,统一使用TO_CHAR的索引。
　　

　　 　　 　　和CAL.CALENDER=TO_DATE (TO_CHAR (TTSH。SHOHOU_DATE '名称')'名称') 　　 　　

上面的部分语句因为日历字段是日期类型,所以比较时使用了TO_DATE,其实只要把日历转化成字符类型就行了,虽然看起来要改动的地方很多,其实解决了更大的问题。

甲骨文指数索引无效的原因与解决方法