search
HomeDatabaseMysql Tutorial自定义函数索引使用及其注意点

???当我们对列使用了函数运算之后,如果此列没有函数索引,那么普通索引是无效的。比如where substr(name,1,3)=abc;如果建立了create INDEX idx_t ON t(NAME); ? 那么谓词是无法使用此索引做范围扫描的。在oracle中允许定义函数索引(FUNCTION BASED INDEX,

???当我们对列使用了函数运算之后,如果此列没有函数索引,那么普通索引是无效的。比如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不会自动REBUILD函数索引对应的值,这样如果继续使用函数 ? ? ? ? 索引,必然结果可能出错。

? ? ?下面分别对上面的内容举例说明:
针对第1点的例子:

–使用自定义函数索引,必须加DETERMINISTIC,并且实际对应一样的输入参数,返回的结果就是一样的,否则会导致错误
dingjun123@ORADB> CREATE OR REPLACE FUNCTION get_date(param_in VARCHAR2)
? 2 ?RETURN DATE DETERMINISTIC
? 3 ?AS
? 4 ?BEGIN
? 5 ? RETURN?TO_DATE(param_in,’yyyy’);
? 6 ?END;
? 7 ?/

Function created.

dingjun123@ORADB> DROP TABLE t;
Table dropped.

dingjun123@ORADB> CREATE TABLE t(a VARCHAR2(10));
Table created.

dingjun123@ORADB> CREATE INDEX idx_t ON t(get_date(a));
Index created.

–2013-年5月份插入
dingjun123@ORADB> INSERT INTO t VALUES(’2013′);
1 row created.

dingjun123@ORADB> commit;
Commit complete.

dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′;
A
———-
2013
1 row selected.

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

–2013年6月份插入
dingjun123@ORADB> INSERT INTO t VALUES(’2013′);
1 row created.

dingjun123@ORADB> COMMIT;
Commit complete.

dingjun123@ORADB> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’;
Session altered.

dingjun123@ORADB> select a from t;
A
———-
2013
2013
2 rows selected.

?现在是查询:

dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′;
A
———-
2013

1 row selected.

Execution Plan
———————————————————-
Plan hash value: 1594971208
————————————————————————————-
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ?| Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————-
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? | ? ? 1 | ? ?16 | ? ? 1 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| T ? ? | ? ? 1 | ? ?16 | ? ? 1 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX_T | ? ? 1 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“DINGJUN123″.”GET_DATE”(“A”)=TO_DATE(‘ 2013-05-01 00:00:00′,
? ? ? ? ? ? ? ‘syyyy-mm-dd hh24:mi:ss’))

? ???上面的结果是令人迷惑的,因为表里存储的有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下无影响,后来看了yangtingkun大师的文章,原来早就没有影响了。http://space.itpub.net/4227/viewspace-68620

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

???走全表扫描,函数会对每行都调用1次(当然DETERMINSTIC函数是可以有缓存效果的,以后再说明)

dingjun123@ORADB> DROP TABLE tt;
Table dropped.

dingjun123@ORADB> CREATE TABLE ?tt(NAME VARCHAR2(10));
Table created.

dingjun123@ORADB> INSERT INTO tt
? 2 ? ? ?SELECT LEVEL FROM dual CONNECT BY LEVEL
999 rows created.

–DBMS_APPLICATION_INFO包监控函数的调用次数
dingjun123@ORADB> CREATE OR REPLACE FUNCTION func_tt(x IN VARCHAR2)
? 2 ?RETURN VARCHAR2 DETERMINISTIC AS
? 3 ?BEGIN
? 4 ? ? ?DBMS_APPLICATION_INFO.set_client_info(USERENV(‘client_info’)+1 );
? 5 ? ? ?RETURN ‘o’ || x;
? 6 ?END;
? 7 ?/
Function created.

dingjun123@ORADB> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL procedure successfully completed.

dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME)
no rows selected

dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

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

–重置计数器
dingjun123@ORADB> EXEC DBMS_APPLICATION_INFO.set_client_info(0);
PL/SQL procedure successfully completed.

dingjun123@ORADB> CREATE INDEX ?idx_tt ON tt(func_tt(NAME));
Index created.

–创建索引的时候就调用函数了
dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

dingjun123@ORADB> set autotrace traceonly

–使用的时候不再调用函数,因为已经调用过函数,函数运算的结果已经存储到索引中了
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
1 row selected.

Execution Plan
———————————————————-
Plan hash value: 6977672
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| TT ? ? | ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX_TT | ? ? 4 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“DINGJUN123″.”FUNC_TT”(“NAME”)=’o1′)
Note
—–
? ?- dynamic sampling used for this statement (level=2)

Statistics
———————————————————-
? ? ? ? ?24 ?recursive calls
? ? ? ? ? 0 ?db block gets
? ? ? ? ?14 ?consistent gets
? ? ? ? ? 0 ?physical reads
? ? ? ? ? 0 ?redo size
? ? ? ? 417 ?bytes sent via SQL*Net to client
? ? ? ? 416 ?bytes received via SQL*Net from client
? ? ? ? ? 2 ?SQL*Net roundtrips to/from client
? ? ? ? ? 0 ?sorts (memory)
? ? ? ? ? 0 ?sorts (disk)
? ? ? ? ? 1 ?rows processed

dingjun123@ORADB> select userenv(‘client_info’) from dual;
USERENV(‘CLIENT_INFO’)
—————————————————————-
999
1 row selected.

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

dingjun123@ORADB> CREATE OR REPLACE FUNCTION func_tt(x IN VARCHAR2)
? 2 ?RETURN VARCHAR2 DETERMINISTIC AS
? 3 ?BEGIN
? 4 ? ? ?DBMS_APPLICATION_INFO.set_client_info(USERENV(‘client_info’)+1 );
? 5 ? ???RETURN ‘a’ || x;
? 6 ?END;
? 7 ?/
Function created.

–查询不对,函数应该运算结果’o1′应该没有行,但是因为索引没有被rebuild
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
NAME
———-
1
1 row selected.

–强制全表扫描,正确
dingjun123@ORADB> SELECT/*+full(tt)*/ * FROM tt WHERE func_tt(NAME) = ‘o1′;

no rows selected

–rebuild索引后也正确
dingjun123@ORADB> alter index idx_tt rebuild;
Index altered.

dingjun123@ORADB> set autotrace traceonly
dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) = ‘o1′;
no rows selected

Execution Plan
———————————————————-
Plan hash value: 6977672
————————————————————————————–
| Id ?| Operation ? ? ? ? ? ? ? ? ? | Name ? | Rows ?| Bytes | Cost (%CPU)| Time ? ? |
————————————————————————————–
| ? 0 | SELECT STATEMENT ? ? ? ? ? ?| ? ? ? ?| ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
| ? 1 | ?TABLE ACCESS BY INDEX ROWID| TT ? ? | ? ?10 | 20090 | ? ? 2 ? (0)| 00:00:01 |
|* ?2 | ? INDEX RANGE SCAN ? ? ? ? ?| IDX_TT | ? ? 4 | ? ? ? | ? ? 1 ? (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
? ?2 – access(“DINGJUN123″.”FUNC_TT”(“NAME”)=’o1′)

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

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
如何在Netflix中快速设置自定义头像如何在Netflix中快速设置自定义头像Feb 19, 2024 pm 06:33 PM

Netflix上的头像是你流媒体身份的可视化代表。用户可以超越默认的头像来展示自己的个性。继续阅读这篇文章,了解如何在Netflix应用程序中设置自定义个人资料图片。如何在Netflix中快速设置自定义头像在Netflix中,没有内置功能来设置个人资料图片。不过,您可以通过在浏览器上安装Netflix扩展来实现此目的。首先,在浏览器上安装Netflix扩展的自定义个人资料图片。你可以在Chrome商店买到它。安装扩展后,在浏览器上打开Netflix并登录您的帐户。导航至右上角的个人资料,然后单击

Win11如何自定义背景图片Win11如何自定义背景图片Jun 30, 2023 pm 08:45 PM

Win11如何自定义背景图片?在最新发布的win11系统中,里面有许多的自定义功能,但是很多小伙伴不知道应该如何使用这些功能。就有小伙伴觉得背景图片比较单调,想要自定义背景图,但是不知道如何操作自定义背景图,如果你不知道如何定义背景图片,小编下面整理了Win11自定义背景图片步骤,感兴趣的话一起往下看看把!Win11自定义背景图片步骤1、点击桌面win按钮,在弹出的菜单中点击设置,如图所示。2、进入设置菜单,点击个性化,如图所示。3、进入个性化,点击背景,如图所示。4、进入背景设置,点击浏览图片

如何在Python中创建和自定义Venn图?如何在Python中创建和自定义Venn图?Sep 14, 2023 pm 02:37 PM

维恩图是用来表示集合之间关系的图。要创建维恩图,我们将使用matplotlib。Matplotlib是一个在Python中常用的数据可视化库,用于创建交互式的图表和图形。它也用于制作交互式的图像和图表。Matplotlib提供了许多函数来自定义图表和图形。在本教程中,我们将举例说明三个示例来自定义Venn图。Example的中文翻译为:示例这是一个创建两个维恩图交集的简单示例;首先,我们导入了必要的库并导入了venns。然后我们将数据集创建为Python集,之后,我们使用“venn2()”函数创

如何在CakePHP中创建自定义分页?如何在CakePHP中创建自定义分页?Jun 04, 2023 am 08:32 AM

CakePHP是一个强大的PHP框架,为开发人员提供了很多有用的工具和功能。其中之一是分页,它可以帮助我们将大量数据分成几页,从而简化浏览和操作。默认情况下,CakePHP提供了一些基本的分页方法,但有时你可能需要创建一些自定义的分页方法。这篇文章将向您展示如何在CakePHP中创建自定义分页。步骤1:创建自定义分页类首先,我们需要创建一个自定义分页类。这个

Eclipse中自定义快捷键设置的方法Eclipse中自定义快捷键设置的方法Jan 28, 2024 am 10:01 AM

如何在Eclipse中自定义快捷键设置?作为一名开发人员,在使用Eclipse进行编码时,熟练掌握快捷键是提高效率的关键之一。Eclipse作为一款强大的集成开发环境,不仅提供了许多默认的快捷键,还允许用户根据自己的偏好进行个性化的定制。本文将介绍如何在Eclipse中自定义快捷键设置,并给出具体的代码示例。打开Eclipse首先,打开Eclipse,并进入

Vue3中的render函数:自定义渲染函数Vue3中的render函数:自定义渲染函数Jun 18, 2023 pm 06:43 PM

Vue是一款流行的JavaScript框架,它提供了许多方便的功能和API以帮助开发者构建交互式的前端应用程序。随着Vue3的发布,render函数成为了一个重要的更新。本文将介绍Vue3中render函数的概念、用途和如何使用它自定义渲染函数。什么是render函数在Vue中,template是最常用的渲染方式,但是在Vue3中,可以使用另外一种方式:r

如何在装有 iOS 17 的 iPhone 上的 Apple Music 中启用和自定义交叉淡入淡出如何在装有 iOS 17 的 iPhone 上的 Apple Music 中启用和自定义交叉淡入淡出Jun 28, 2023 pm 12:14 PM

适用于iPhone的iOS17更新为AppleMusic带来了一些重大变化。这包括在播放列表中与其他用户协作,在使用CarPlay时从不同设备启动音乐播放等。这些新功能之一是能够在AppleMusic中使用交叉淡入淡出。这将允许您在曲目之间无缝过渡,这在收听多个曲目时是一个很棒的功能。交叉淡入淡出有助于改善整体聆听体验,确保您在音轨更改时不会受到惊吓或退出体验。因此,如果您想充分利用这项新功能,以下是在iPhone上使用它的方法。如何為AppleMusic啟用和自定Crossfade您需要最新的

如何在CodeIgniter中实现自定义中间件如何在CodeIgniter中实现自定义中间件Jul 29, 2023 am 10:53 AM

如何在CodeIgniter中实现自定义中间件引言:在现代的Web开发中,中间件在应用程序中起着至关重要的作用。它们可以用来执行在请求到达控制器之前或之后执行一些共享的处理逻辑。CodeIgniter作为一个流行的PHP框架,也支持中间件的使用。本文将介绍如何在CodeIgniter中实现自定义中间件,并提供一个简单的代码示例。中间件概述:中间件是一种在请求

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

Dreamweaver Mac version

Dreamweaver Mac version

Visual web development tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

SublimeText3 Linux new version

SublimeText3 Linux new version

SublimeText3 Linux latest version