警惕!自定义函数索引的那些陷阱及避坑术-阿里云开发者社区

开发者社区> 努力酱> 正文

警惕!自定义函数索引的那些陷阱及避坑术

简介:
+关注继续查看

作者介绍

丁俊,DBAplus社群联合发起人,新炬网络专家团成员,性能优化专家,Oracle ACEA,ITPUB开发版资深版主。十年电信行业从业经验,从事过系统开发与维护、业务架构和数据分析、系统优化等工作。电子工业出版社终身荣誉作者,《剑破冰山-Oracle开发艺术》副主编。

 

当我们对列使用了函数运算之后,如果此列没有函数索引,那么普通索引是无效的。比如where substr(name,1,3)='abc';如果建立了create INDEX idx_t ON t(NAME);

 

那么谓词是无法使用此索引做范围扫描的。在Oracle中允许定义函数索引(FUNCTION BASED INDEX,简称FBI),函数索引可以是基于内置函数的,也可以是自定义函数的,本文主要讲述基于自定义函数的索引用法及其注意点。


当需要对列进行复杂的运算,复杂的规则需要自定义函数的时候,如果需要走索引,那么必须建立自定义函数的索引。建立自定义函数索引有几点要注意:

 

1、自定义函数必须加DETERMINISTIC关键字,让Oracle知道此函数对于每个入参的返回结果都是确定的唯一的。

 

道理很明显,如果一样的入参,结果不同,那么查询的结果必然有问题,必须要用这个关键字告诉Oracle,此函数索引是可以信任的。但是有个问题得注意:因为自定义函数是一系列逻辑规则,就算定义的函数对每个入参返回的值不唯一(比如用了SYSDATE,RANDOM等运算),但是使用了DETERMINISTIC关键字,让Oracle相信唯一,但是实际情况不唯一,那么使用函数索引查询的结果必然也是有问题的。所以使用函数索引要注意:必须从逻辑上确定对于一样的入参返回的结果是一样的,因为Oracle不会检查你的逻辑。

 

2、一旦改变函数定义,必须REBUILD对应的函数索引

 

很显然,函数索引中存储的是表中的列或表达式作为自定义函数的参数的运算结果,如果函数改变,Oracle不会自动rebulid函数索引对应的值,这样如果继续使用函数索引,必然结果可能出错。


下面分别对上面的内容举例说明:

 

   针对第1点的例子:

 

20161009104518761.jpg

 

Of course,现在的结果是没有问题的,但是本身这个自定义函数中的TO_DATE(param,'yyyy')针对不同月份的插入结果返回的都是当月的第一天,如果我是6月插入:
 

20161009104539465.jpg

 

现在是查询:

 

20161009104612544.jpg

 

上面的结果是令人迷惑的,因为表里存储的有2行2013,但是最终结果却只查询出一行。究其原因,就是自定义函数虽然使用了DETERMINISTIC关键字,但是Oracle只管有没有这关键字,而不会管你的函数逻辑是否真的对每个相同的输入,有一样的输出,这里我们使用DETERMINISTIC关键字,欺骗了Oracle。很显然,虽然在表里存储的2行都是2013,但是一个5月份插入的,一个6月份插入的,通过函数运算,一个索引中存储的是2013-5-1,一个是2013-6-1,所以使用2013-5-1里查询的时候,只返回1行。如果自定义中有类似于DBMS_RANDOM,SYS_GUID等不确定或随时间变化值不同的,那么也会产生此混乱结果。

另外很多书上说函数索引必须:


Oracle使用函数索引,会进行查询重写,要求下面两个参数开启:       
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED

 

经过测试,发现在本环境11g下无影响。

 

   针对第2点的例子:

 

函数索引的函数定义不能随便改变,改变就必须rebuild函数索引(or删除重建),因为函数索引中会存储对应函数运算的结果,然后在使用函数索引访问的时候,不用再调用函数,so,函数改变,Oracle不会级联rebuild其函数索引,所以,改变函数逻辑不手动rebuild,必然是危险的。

走全表扫描,函数会对每行都调用1次。

 

20161009104649391.jpg

 

无函数索引,全表扫描,访问对每行都调用函数,一条SQL访问函数999次。如果使用函数索引,那么必然在创建(DML)的时候,会自动调用函数,索引中存储对应的key与函数运算结果值,所以,再使用到函数索引的时候,不用再调用函数,而且索引访问还提高效率,达到多种提高效率的效果。
 

20161009104719197.jpg
20161009105012187.jpg

 

使用自定义函数索引是危险的,如果修改函数定义,没有rebuild或删除重建函数索引,那么函数索引中存储的还是旧的函数运算结果,这样会导致错误:

 

20161009105032700.jpg
20161009104834541.jpg

 

总结:在不得不使用函数索引来提高效率的时候,别忘记了,随时准备维护函数索引,而且别弄出奇奇怪怪的函数索引,导致乱七八糟的问题,那样就不好了!


本文来自云栖社区合作伙伴"DBAplus",原文发布时间:2016-10-09

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
自定义圆角和园边的实现
本来想在网上找个圆角的例子看一看,不尽人意啊,基本都是官方的Demo的那张原理图,稍后会贴出。于是自己自定义了个View,实现图片的圆角以及圆形效果。效果图: 第一个是原图,第二个是圆形效果,第三第四设置了不同的圆角大小。 准备改变一个博客的风格,首先给大家讲一下原理,让大家明白了,然后再贴代码,不然可以直接看那么长的代码也比较痛苦,核心代码其实就那么几行: 核心代码分析:
985 0
Android 自定义Toast,并且勘误Android工具类里面的ToastUtils
前言 相信大部分仁兄在使用系统Toast的时候,都感觉不太尽如人意,因为系统Toast显示的位置比较固定,并且字体颜色等会跟随系统版本变化,那么能不能自己写一个呢,答案是当然的。
931 0
JSP自定义简单标签入门之带有属性
上面写的那个简单标签来控制页面中标签内容的重复次数是固定的,这就是权限“写死了”,这是非常不好的行为,因为其灵活性太差。所以下面就介绍一种使用属性来控制标签内容输出次数的方法。 准备工作 创建实现了SimpleTag接口的实现类(或者是继承了SimpleTagSupport类的子类) 在类中为相应的属性字段添加setter方法,注意字段名称一定要一致。
815 0
Hive内置运算函数,自定义函数(UDF)和Transform
4.Hive函数 4.1 内置运算符 内容较多,见《Hive官方文档》   4.2 内置函数 内容较多,见《Hive官方文档》 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF   测试各种内置函数的快捷方法: 1、创建一个dual表 create table dual(id string); 2、loa
4650 0
Android:随笔——自定义View的好帮手 ViewDragHelper
转载请标明地址 QuincySx: http://www.jianshu.com/p/e19a8735b123 ViewDragHelper是用于编写自定义ViewGroups的实用工具类。
876 0
SQL SERVER CHARINDEX函数
        CHARINDEX函数常常用来在一段字符中搜索字符或者字符串。如果被搜索的字符中包含有要搜索的字符,那么这个函数返回一个非零的整数,这个整数是要搜索的字符在被搜索的字符中的开始位数。即CHARINDEX函数返回字符或者字符串在另一个字符串中的起始位置。         CHARINDEX函数调用方法如下: CHARINDEX ( expression1 , expres
874 0
oracle表空间,角色,权限,表,索引,序列号,视图,同义词,约束条件,存储函数和过程,常用数据字典,基本数据字典信息,查看VGA信息,维护表空间,创建表空间等信息
查看当前用户的缺省表空间   SQL>select username,default_tablespace from user_users;   查看当前用户的角色   SQL>select * from user_role_privs;   查看当前用户的系统权限和表级权限   SQL>select * from user_sys_privs;       
1225 0
Android 自定义ToolBar并沉浸式
  ToolBar是Android 5.0推出的一个新的导航控件用于取代之前的ActionBar,由于其高度的可定制性、灵活性、具有Material Design风格等优点,越来越多的App也用上了ToolBar。
801 0
+关注
287
文章
3
问答
文章排行榜
最热
最新
相关电子书
更多
文娱运维技术
立即下载
《SaaS模式云原生数据仓库应用场景实践》
立即下载
《看见新力量:二》电子书
立即下载