自定义函数索引使用及其注意点
???当我们对列使用了函数运算之后,如果此列没有函数索引,那么普通索引是无效的。比如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; dingjun123@ORADB> CREATE TABLE t(a VARCHAR2(10)); dingjun123@ORADB> CREATE INDEX idx_t ON t(get_date(a)); –2013-年5月份插入 dingjun123@ORADB> commit; dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′; |
? OF COURSE,现在的结果是没有问题的,但是本身这个自定义函数中的TO_DATE(param,’yyyy’)针对不同月份的插入结果返回的都是当月的第一天,如果我是6月插入:
–2013年6月份插入 dingjun123@ORADB> INSERT INTO t VALUES(’2013′); 1 row created. dingjun123@ORADB> COMMIT; dingjun123@ORADB> alter session set nls_date_format=’yyyy-mm-dd hh24:mi:ss’; dingjun123@ORADB> select a from t; |
?现在是查询:
dingjun123@ORADB> SELECT * FROM t WHERE get_date(a)=DATE’2013-5-1′; A ———- 2013 1 row selected. Execution Plan Predicate Information (identified by operation id): |
? ???上面的结果是令人迷惑的,因为表里存储的有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)); dingjun123@ORADB> INSERT INTO tt –DBMS_APPLICATION_INFO包监控函数的调用次数 dingjun123@ORADB> EXEC DBMS_APPLICATION_INFO.set_client_info(0); dingjun123@ORADB> SELECT * FROM tt WHERE func_tt(NAME) dingjun123@ORADB> select userenv(‘client_info’) from dual; |
? ?无函数索引,全表扫描,访问对每行都调用函数,一条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)); –创建索引的时候就调用函数了 dingjun123@ORADB> set autotrace traceonly –使用的时候不再调用函数,因为已经调用过函数,函数运算的结果已经存储到索引中了 Execution Plan Predicate Information (identified by operation id): Statistics dingjun123@ORADB> select userenv(‘client_info’) from dual; |
? ? 使用自定义函数索引是危险的,如果修改函数定义,没有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 –强制全表扫描,正确 no rows selected –rebuild索引后也正确 dingjun123@ORADB> set autotrace traceonly Execution Plan Predicate Information (identified by operation id): |
? ? 在不得不使用函数索引来提高效率的时候,别忘记了,随时准备维护函数索引,而且别弄出奇奇怪怪的函数索引,导致乱七八糟的问题,那样就不好了!
原文地址:自定义函数索引使用及其注意点, 感谢原作者分享。

Alat AI Hot

Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool
Gambar buka pakaian secara percuma

Clothoff.io
Penyingkiran pakaian AI

AI Hentai Generator
Menjana ai hentai secara percuma.

Artikel Panas

Alat panas

Notepad++7.3.1
Editor kod yang mudah digunakan dan percuma

SublimeText3 versi Cina
Versi Cina, sangat mudah digunakan

Hantar Studio 13.0.1
Persekitaran pembangunan bersepadu PHP yang berkuasa

Dreamweaver CS6
Alat pembangunan web visual

SublimeText3 versi Mac
Perisian penyuntingan kod peringkat Tuhan (SublimeText3)

Topik panas

MetaMask (juga dipanggil Little Fox Wallet dalam bahasa Cina) ialah perisian dompet penyulitan percuma dan diterima baik. Pada masa ini, BTCC menyokong pengikatan pada dompet MetaMask Selepas mengikat, anda boleh menggunakan dompet MetaMask untuk log masuk dengan cepat, menyimpan nilai, membeli syiling, dsb., dan anda juga boleh mendapatkan bonus percubaan 20 USDT untuk pengikatan pertama. Dalam tutorial dompet BTCCMetaMask, kami akan memperkenalkan secara terperinci cara mendaftar dan menggunakan MetaMask, dan cara mengikat dan menggunakan dompet Little Fox dalam BTCC. Apakah dompet MetaMask? Dengan lebih 30 juta pengguna, MetaMask Little Fox Wallet ialah salah satu dompet mata wang kripto yang paling popular hari ini. Ia percuma untuk digunakan dan boleh dipasang pada rangkaian sebagai sambungan

Bahasa Go menyediakan dua teknologi penciptaan fungsi dinamik: penutupan dan refleksi. penutupan membenarkan akses kepada pembolehubah dalam skop penutupan, dan refleksi boleh mencipta fungsi baharu menggunakan fungsi FuncOf. Teknologi ini berguna dalam menyesuaikan penghala HTTP, melaksanakan sistem yang sangat boleh disesuaikan dan membina komponen boleh pasang.

Dalam penamaan fungsi C++, adalah penting untuk mempertimbangkan susunan parameter untuk meningkatkan kebolehbacaan, mengurangkan ralat dan memudahkan pemfaktoran semula. Konvensyen susunan parameter biasa termasuk: tindakan-objek, objek-tindakan, makna semantik dan pematuhan perpustakaan standard. Susunan optimum bergantung pada tujuan fungsi, jenis parameter, kemungkinan kekeliruan dan konvensyen bahasa.

Kunci untuk menulis fungsi Java yang cekap dan boleh diselenggara ialah: pastikan ia mudah. Gunakan penamaan yang bermakna. Mengendalikan situasi khas. Gunakan keterlihatan yang sesuai.

Kelebihan parameter lalai dalam fungsi C++ termasuk memudahkan panggilan, meningkatkan kebolehbacaan dan mengelakkan ralat. Kelemahannya ialah fleksibiliti terhad dan sekatan penamaan. Kelebihan parameter variadic termasuk fleksibiliti tanpa had dan pengikatan dinamik. Kelemahan termasuk kerumitan yang lebih besar, penukaran jenis tersirat dan kesukaran dalam penyahpepijatan.

1. Fungsi SUM digunakan untuk menjumlahkan nombor dalam lajur atau sekumpulan sel, contohnya: =SUM(A1:J10). 2. Fungsi AVERAGE digunakan untuk mengira purata nombor dalam lajur atau sekumpulan sel, contohnya: =AVERAGE(A1:A10). 3. Fungsi COUNT, digunakan untuk mengira bilangan nombor atau teks dalam lajur atau sekumpulan sel, contohnya: =COUNT(A1:A10) 4. Fungsi IF, digunakan untuk membuat pertimbangan logik berdasarkan syarat yang ditentukan dan mengembalikan hasil yang sepadan.

Faedah fungsi mengembalikan jenis rujukan dalam C++ termasuk: Peningkatan prestasi: Melewati rujukan mengelakkan penyalinan objek, sekali gus menjimatkan memori dan masa. Pengubahsuaian langsung: Pemanggil boleh mengubah suai secara langsung objek rujukan yang dikembalikan tanpa menugaskannya semula. Kesederhanaan kod: Lulus melalui rujukan memudahkan kod dan tidak memerlukan operasi penugasan tambahan.

Perbezaan antara fungsi PHP tersuai dan fungsi yang dipratentukan ialah: Skop: Fungsi tersuai terhad kepada skop definisinya, manakala fungsi yang dipratentukan boleh diakses di seluruh skrip. Cara mentakrifkan: Fungsi tersuai ditakrifkan menggunakan kata kunci fungsi, manakala fungsi yang dipratakrifkan ditakrifkan oleh kernel PHP. Lulus parameter: Fungsi tersuai menerima parameter, manakala fungsi yang dipratentukan mungkin tidak memerlukan parameter. Kebolehlanjutan: Fungsi tersuai boleh dibuat mengikut keperluan, manakala fungsi yang dipratentukan terbina dalam dan tidak boleh diubah suai.
