데이터 베이스 MySQL 튜토리얼 自定义函数索引使用及其注意点

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

Jun 07, 2016 pm 04:37 PM
사용 기능 우리를 알아채다 색인 사용자 정의

???当我们对列使用了函数运算之后,如果此列没有函数索引,那么普通索引是无效的。比如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′)

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

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

뜨거운 도구

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

BTCC 튜토리얼: BTCC 교환에서 MetaMask 지갑을 바인딩하고 사용하는 방법은 무엇입니까? BTCC 튜토리얼: BTCC 교환에서 MetaMask 지갑을 바인딩하고 사용하는 방법은 무엇입니까? Apr 26, 2024 am 09:40 AM

MetaMask(중국어로 Little Fox Wallet이라고도 함)는 무료이며 호평을 받는 암호화 지갑 소프트웨어입니다. 현재 BTCC는 MetaMask 지갑에 대한 바인딩을 지원합니다. 바인딩 후 MetaMask 지갑을 사용하여 빠르게 로그인하고 가치를 저장하고 코인을 구매할 수 있으며 첫 바인딩에는 20 USDT 평가판 보너스도 받을 수 있습니다. BTCCMetaMask 지갑 튜토리얼에서는 MetaMask 등록 및 사용 방법, BTCC에서 Little Fox 지갑을 바인딩하고 사용하는 방법을 자세히 소개합니다. MetaMask 지갑이란 무엇입니까? 3천만 명 이상의 사용자를 보유한 MetaMask Little Fox Wallet은 오늘날 가장 인기 있는 암호화폐 지갑 중 하나입니다. 무료로 사용할 수 있으며 확장으로 네트워크에 설치할 수 있습니다.

golang 함수에서 새 함수를 동적으로 생성하기 위한 팁 golang 함수에서 새 함수를 동적으로 생성하기 위한 팁 Apr 25, 2024 pm 02:39 PM

Go 언어는 클로저와 리플렉션이라는 두 가지 동적 함수 생성 기술을 제공합니다. 클로저는 클로저 범위 내의 변수에 대한 액세스를 허용하며 리플렉션은 FuncOf 함수를 사용하여 새 함수를 생성할 수 있습니다. 이러한 기술은 HTTP 라우터를 사용자 정의하고 고도로 사용자 정의 가능한 시스템을 구현하며 플러그 가능한 구성 요소를 구축하는 데 유용합니다.

C++ 함수 이름 지정 시 매개변수 순서에 대한 고려 사항 C++ 함수 이름 지정 시 매개변수 순서에 대한 고려 사항 Apr 24, 2024 pm 04:21 PM

C++ 함수 이름 지정에서는 가독성을 높이고 오류를 줄이며 리팩토링을 용이하게 하기 위해 매개변수 순서를 고려하는 것이 중요합니다. 일반적인 매개변수 순서 규칙에는 작업-객체, 개체-작업, 의미론적 의미 및 표준 라이브러리 준수가 포함됩니다. 최적의 순서는 함수의 목적, 매개변수 유형, 잠재적인 혼동 및 언어 규칙에 따라 달라집니다.

Java로 효율적이고 유지 관리 가능한 함수를 작성하는 방법은 무엇입니까? Java로 효율적이고 유지 관리 가능한 함수를 작성하는 방법은 무엇입니까? Apr 24, 2024 am 11:33 AM

효율적이고 유지 관리 가능한 Java 함수를 작성하는 핵심은 단순함을 유지하는 것입니다. 의미 있는 이름을 사용하세요. 특별한 상황을 처리합니다. 적절한 가시성을 사용하십시오.

C++ 함수 기본 매개변수와 가변 매개변수의 장단점 비교 C++ 함수 기본 매개변수와 가변 매개변수의 장단점 비교 Apr 21, 2024 am 10:21 AM

C++ 함수에서 기본 매개변수의 장점에는 호출 단순화, 가독성 향상, 오류 방지 등이 있습니다. 단점은 제한된 유연성과 명명 제한입니다. 가변 매개변수의 장점에는 무제한의 유연성과 동적 바인딩이 포함됩니다. 단점은 더 큰 복잡성, 암시적 유형 변환 및 디버깅의 어려움을 포함합니다.

Excel 함수 수식의 전체 모음 Excel 함수 수식의 전체 모음 May 07, 2024 pm 12:04 PM

1. SUM 함수는 열이나 셀 그룹의 숫자를 합하는 데 사용됩니다(예: =SUM(A1:J10)). 2. AVERAGE 함수는 열이나 셀 그룹에 있는 숫자의 평균을 계산하는 데 사용됩니다(예: =AVERAGE(A1:A10)). 3. COUNT 함수, 열이나 셀 그룹의 숫자나 텍스트 수를 세는 데 사용됩니다. 예: =COUNT(A1:A10) 4. IF 함수, 지정된 조건을 기반으로 논리적 판단을 내리고 결과를 반환하는 데 사용됩니다. 해당 결과.

참조 유형을 반환하는 C++ 함수의 이점은 무엇입니까? 참조 유형을 반환하는 C++ 함수의 이점은 무엇입니까? Apr 20, 2024 pm 09:12 PM

C++에서 참조 유형을 반환하는 함수의 이점은 다음과 같습니다. 성능 개선: 참조로 전달하면 객체 복사가 방지되므로 메모리와 시간이 절약됩니다. 직접 수정: 호출자는 반환된 참조 객체를 다시 할당하지 않고 직접 수정할 수 있습니다. 코드 단순성: 참조로 전달하면 코드가 단순화되고 추가 할당 작업이 필요하지 않습니다.

사용자 정의 PHP 함수와 사전 정의된 함수의 차이점은 무엇입니까? 사용자 정의 PHP 함수와 사전 정의된 함수의 차이점은 무엇입니까? Apr 22, 2024 pm 02:21 PM

사용자 정의 PHP 함수와 사전 정의된 함수의 차이점은 다음과 같습니다. 범위: 사용자 정의 함수는 정의 범위로 제한되는 반면, 사전 정의된 함수는 스크립트 전체에서 액세스할 수 있습니다. 정의 방법: 사용자 정의 함수는 function 키워드를 사용하여 정의되는 반면, 사전 정의된 함수는 PHP 커널에 의해 정의됩니다. 매개변수 전달: 사용자 정의 함수는 매개변수를 수신하지만 사전 정의된 함수에는 매개변수가 필요하지 않을 수 있습니다. 확장성: 필요에 따라 사용자 정의 함수를 생성할 수 있으며 사전 정의된 함수는 내장되어 있어 수정할 수 없습니다.

See all articles