Oracle全文索引技术

1、Oracle 全文索引技术 使用说明文档金联万家 （北京 ）电子支付科技发展有限公司文档编号保密等级机密作者宋成最后修改日期审核人最后审批日期批准人最后批准日期修改记录日期版本修改人备注2013-03-20V1.0宋成初稿目录1 前言 31.1 编写目的 31.2 名词解释 31.3 参考资料 32 oracle 全文索引技术 43 建立全文索引的操作步骤 43.1 查看用户与角色 43.2 开启目标用户全文索引权限 43.3 设置语法分析器 Lexer 53.4 建立索引 53.5 使用索引 64 全文索引的种类 65 对多字段建立索引 66 Oracle 全文索引之 - CONTEXT 7

2、6.1 函数 CONTAINS 语法 76.2 全文索引的维护 76.2.1 同步数据 86.3 单个中文字查询问题 97 建立全文索引的完整脚本 108 附录 141 前言1.1 编写目的本文档主要说明 Oracle 全文索引技术的使用与维护，为数据库操作使用人员提供参考维护手册。1.2 名词解释名词解释表 1. 术语表1.3 参考资料资料名说明表 2. 参考资料列表2 oracle 全文索引技术全文检索：是指计算机索引程序通过扫描文章中的每一个词，对每一个词建立一个索引，指明该词在文章中出 现的次数和位置，当用户查询时，检索程序就根据事先建立的索引进行查找，并将查找的结果反馈给用户的检索方

3、 式。这个过程类似于通过字典中的检索字表查字的过程。【 Oracle Text 全文检索】Oracle 一直致力于全文检索技术的研究，当 Oracle9i Rlease2 发布之时， Oracle 数据库的全文检索技术已经 非常完美， Oracle Text 使 Oracle9i 具备了强大的文本检索能力和智能化的文本管理能力。 Oracle Text 是 Oracle9i 采用的新名称，在 Oracle8/8i 中它被称作 Oracle interMedia Text ，在 Oracle8 以前它的名称是 Oracle ConText Cartridge 。使用 Oracle9i 和 Ora

4、cle Text ，可以方便而有效地利用标准的 SQL工具来构建基于文本的新的开发工 具或对现有应用程序进行扩展。应用程序开发人员可以在任何使用文本的 Oracle 数据库应用程序中充分利用 Oracle Text 搜索，应用范围可以是现有应用程序中可搜索的注释字段，也可是实现涉及多种文档格式和复杂搜索 标准的大型文档管理系统。 Oracle Text 支持 Oracle 数据库所支持的大多数语言的基本全文搜索功能。扩展阅读： 3 建立全文索引的操作步骤3.1 查看用户与角色 检查数据库中是否有 CTXSYS用 户 和 CTXAPP角 色 如果没有这个用户和角色，意味着你的数据库创建时未安装

5、intermedia 功能。你必须修改数据库以安装这项功能。 默认安装情况下， ctxsys 用户是被锁定的，因此要先启用 ctxsys 的用户。- 锁住用户ALTERUSER ctxsys ACCOUNLTOCKPASSWOREDXPIRE;- 解锁用户ALTERUSER ctxsys ACCOUNUTNLOCKIDENTIFIED BY ctxsys;如果没有该用户，则需要打开 dbca 工具中选择 configrue database options ，然后选择所有数据库组件安装即可。3.2 开启目标用户全文索引权限赋权 在 ctxsys 用户下把 ctx_ddl 的执行权限赋于要使用全

6、文索引的用户，例：RANTEXECUTEON ctx ddl TO username;3.3 设置语法分析器 LexerOracle 实现全文检索，其机制其实很简单。即通过 Oracle 专利的词法分析器 (lexer), 将文章中所有的表意单 元(Oracle 称为 term) 找出来，记录在一组 以 dr$ 开头的表中，同时记下该 term 出现的位置、次数、 hash 值等信 息。检索时，Oracle 从这组表中查找相应的 term ，并计算其出现频率， 根据某个算法来计算每个文档的得分 (score), 即所谓的匹配率。而 lexer 则是该机制的核心，它决定了全文检索的效率。 Ora

7、cle 针对不同的语言提供了不 同的 lexer, 而我们通常能用到其中的三个：basic_lexer ：针对英语(默认分析器)chinese_vgram_lexer ：专门针对汉语，对汉字搜索准确性较高 如：中国人民站起来了这句话，会被它分析成如下几个 term: 中，中国，国人，人民， 民站，站起，起来，来了，了chinese_lexer ：新的汉语分析器，只支持 UTF8 字符集，可以将语句分词成常见的词组，能跟有效率的分析 语句，对汉字解析搜索效率较高【指定语法分析器： 】1. 当前用户下下建立一个 preference( 例：在 pomoho 用户下执行以下语句 )EXEC ctx_

8、ddl.create_preference(my_lexer , chinese_vgram_lexer );2. 在建立全文索引索引时，指明所用的 lexer:CREATEINDEX myindex ON mytable(mycolumn) INDEXTYPEIS ctxsys.context PARAMETE(RlSexer my_lexer );这样建立的全文检索索引，就会使用 chinese_vgram_lexer 作为分析器。3.4 建立索引通过以下语法建立全文索引3.5 使用索引使用全文索引很简单，可以通过：SELEC T* FROM PUBMENUW HER CEONTAINS（

9、MENUNAME上, 传图片 ） 0;4 全文索引的种类建立的 Oracle Text 索引被称为域索引 （domain index） ，包括 4 种索引类型：CONTEXT用于对含有大量连续文本数据进行检索。支持word、 html 、xml、text 等很多数据格式。支持中文字符集，支持分区索引，唯一支持并行创建索引（ Parallel indexing ）的索引类型。对表进行 DML 操作后，并不会自动同步 索引。需要手工同步索引。 查询操作符： CONTAINSCTXCAT 当使用混合查询语句的时候可以带来很好的效率。适合于查询较小的具有一定结构的文本段。具有事务性，当 更新主表的时候

10、自动同步索引。 作符： CATSEARCHCTXRULE主要用于文档分类。 查询操作符： MATCHESCTXXPATH快速查找 XML文档 NODE节点 XPATH路径5 对多字段建立索引 很多时候需要从多个文本字段中查询满足条件的记录，这时就需要建立针对多个字段的全文索引，例如需要从 pmhsubjects（ 专题表 ）的 subjectname（ 专题名称 ） 和 briefintro（ 简介 ）上进行全文检索，则需要按以下步骤进行操作：- 建议多字段索引的 preference ，以 ctxsys 登录，并执行EXEC ctx_ddl.create_preference(ctx_idx

11、_subject_pref, MULTI_COLUMN_DATASTO);R E- 建立 preference 对应的字段值(以 ctxsys 登陆)EXEC ctx_ddl.set_attribute(ctx_idx_subject_pref , columns , subjectname,briefintro);- 建立全文索引CREATEINDEX ctx_idx_subject ON pmhsubjects(subjectname) INDEXTYPEIS ctxsys.context PARAMETE(RDSATASTORE ctxsys.ctx_idx_subject_pref l

12、exer my_lexer);- 使用索引SELEC T* FROM pmhsubjects WHER cEontains(subjectname, 李宇春 );6 Oracle 全文索引之 - CONTEXTCONTEX：T 用于对含有大量连续文本数据进行检索。支持word、 html 、xml 、text 等很多数据格式。支持中文字符集，支持分区索引，唯一支持并行创建索引（ Parallel indexing ）的索引类型。对表进行 DML操作后，并不会自 动同步索引。需要手工同步索引。 查询操作符： CONTAINS6.1 函数 CONTAINS 语法使用 contains 时，主要查询

13、语法有： contains（ 列名称 , 查询关键字 ）Logical Operators ：组合搜索条件，通过使用 AND，OR等逻辑符号。AND（ &），同时含有所有关键词，如： cats AND dogs ， cats & dogsOR（ | ），含有所有关键词中的任意一个，如：cats | dogs，cats OR dogssNOT（ ），不含该关键词，如： animals dogsACCU（M ，），与 | 类似，如： dogs, cats, puppiesEQUIV（ =），如： German shepherds=alsatians are big dogsABOUT等【示例：】包

14、含：“中国、安徽”关键字的“建设”或“农业”银行FROM cn common data bank info t WHER cEontains(t.ptcpt nm,( 建设 | 农业 ) & 安徽 & 中国 )0;6.2 全文索引的维护对于 CTXSYS.CONTEX索T 引，当应用程序对基表进行 DML操作后，对基表的索引维护是必须的。索引维护包括索引 同步和索引优化。 在索引建好后，我们可以在该用户下查到 Oracle 自动产生了以下几个表： （ 假设索引名为myindex) ：DR$myindex$I 、 DR$myindex$K、DR$myindex$R、DR$myindex$N其中以

15、 I 表最重要，可以查询一下该表，看看有什么内容：-IDX_CNDATABANKINFO_PKNMSELEC T* FROM cn_common_data_bank_info;SELEC T* FROM user_indexes;SELEC Tt.token text,t.token countFRO Mdr$idx cndatabankinfo pknm$i t;这里就不列出查询接过了。 可以看到， 该表中保存的其实就是 Oracle 分析你的文档后， 生成的 term 记录在这 里，包括 term 出现的位置、次数、 hash 值等。当文档的内容改变后，可以想见这个 I 表的内容也应该相应

16、改变， 才能保证 Oracle 在做全文检索时正确检索到内容 （ 因为所谓全文检索，其实核心就是查询这个表 ） 。这就用到 sync（ 同步 ） 和 optimize（ 优化 ） 了。同步 （sync）: 将新的 term 保存到 I 表；优化 （optimize）: 清除 I 表的垃圾，主要是将已经被删除的 term 从 I 表删除。当基表中的被索引文档发生 insert 、 update 、 delete 操作的时候，基表的改变并不能马上影响到索引上直到 同步索引。可以查询视图 CTX_USER_PENDIN查G看相应的改动。例如： SELEC T* FROM ctx user pendi

17、ng t;SELEC T.PND_INDEX_NAME,T.PND_ROWID,TO_CHAR（T.PND_TIMESTAMP, dd-mm-yyyy hh24:mi:ss ） TIMESTAMPFRO MCTX_USER_PENDING T;6.2.1 同步数据同步和优化方法 : 可以使用 Oracle 提供的 ctx_ddl 包同步和优化索引对于 CTXCAT类型的索引来说， 当对基表进行 DML操作的时候， Oracle 自动维护索引。 对文档的改变马上反映 到索引中。 CTXCAT是事务形的索引。【索引的同步 】 在对基表插入，修改，删除之后同步索引。推荐使用 sync 同步索引。语法

18、：CTX_DDL.SYNC_INDEX（IDX_NAMEIN VARCHARD2EFAULTNULL, - 索引名称MEMORYIN VARCHARD2EFAULTNULL, - 指定同步索引需要的内存。默认是系统参数：DEFAULT_INDEX_MEMORY- 指定一个大的内存时候可以加快索引效率和查询 速度，且索引有较少的碎片PART_NAME IN VARCHARD2EFAULTNULL, - 同步哪个分区索引 PARALLEL_DEGREIEN NUMBERDEFAULT1 - 并行同步索引。设置并行度。）;例如：同步索引 myindex:EXEC ctx_ddl.sync_index

19、( myindex );实施建议：建议通过 oracle 的 job 对索引进行同步【索引的优化 】经常的索引同步将会导致你的 CONTEXT索引产生碎片。索引碎片严重的影响了查询的反应速度。你可以定期优 化索引来减少碎片，减少索引大小，提高查询效率。当文本从表中删除的时候， Oracle Text 标记删除的文档，但是并不马上修改索引。因此，就的文档信息占据 了不必要的空间，导致了查询额外的开销。你必须以FULL 模式优化索引，从索引中删除无效的旧的信息。这个过程叫做垃圾处理。当你经常的对表文本数据进行更新，删除操作的时候，垃圾处理是很必要的。EXEC ctx_ddl.optimize_in

20、dex(myidx , full );实施建议：每天在系统空闲的时候对全文索引进行相应的优化，以提高检索的效率- 同步、优化 全文索引 IDX_CNDATABANKINFO_PK，N1M0 W数据，执行时间小于 1分钟 BEGINctx_ddl.sync_index(IDX_CNDATABANKINFO_PK)N; Mctx_ddl.optimize_index( IDX_CNDATABANKINFO_PK,NfMull );END;6.3 单个中文字查询问题查询单个中文字时，出现错误：SELEC T.ID, T.BK_CD, T.PTCPT_NMFRO MCN_COMMON_DATA_BAN

21、K_INFO TWHER TE.STATUS = 1AND CONTAINS(T.PTCPT NM, 行 & 银 & 云 ) 0;ORA-29902 ：执行 ODCIIndexStart() 例行程序中出错；ORA-20000 ：Oracle Text Error ；DRG-10847 ：CONTAINS要 求这种类型的查询有前缀索引；改成查询：两个字的中文则正常CONTAINS(T.PTCPT_NM ,分 行 & 银行 & 云南 )原因】默认的中文分词器，只将中文分割成如：中国人民银行”-中国 国人 人民 民银 银行”，没有前缀索引，需要手工修改配置，修改如下SELEC T* FROM us

22、er_indexes ;DROPINDEX IDX_CNDATABANKINFO_PKNM;BEGINctx_ddl.drop_preference( ctx_ddl.drop_preference( ctx_ddl.create_preference ( ctx_ddl.create_preference( ctx_ddl.set_attribute( ctx_ddl.set_attribute( ctx_ddl.set_attribute( ctx_ddl.set_attribute(END;/cacps2_ceshi.CHINESE_LEXER);cacps2_ceshi.Chinese

23、WordList );cacps2_ceshi.CHINESE_LEXER, CHINESE_VGRAM_LEX)E; Rcacps2_ceshi.ChineseWordList , BASIC_WORDLIST);cacps2_ceshi.ChineseWordListcacps2_ceshi.ChineseWordListcacps2_ceshi.ChineseWordListcacps2_ceshi.ChineseWordList, PREFIX_INDEX, TRUE );, PREFIX_MIN_LENGTH, 1 );, PREFIX_MAX_LENGT,H 5);, SUBSTR

24、ING_INDEX, YES );Create INDEX IDX_CNDATABANKINFO_PKoNnM c n_common_data_bank_info(ptcpt_nm) ndextype is ctxsys.CONTEXT parameters ( lexer cacps2_ceshi.chinese_lexer wordlist acps2 ceshi.chinesewordlist);总结：】OracleText 要支持中文必须得用 CHINESE_VGRAM_LEX。ER要支持一个中文字以上的搜索，必须配置一个 WordList ，且把它的属性 Prefix_Index 配

25、置为 True 。7 建立全文索引的完整脚本/* 以下脚本为代收付系统“行名行号表 cn_common_data_bank_info ” 银行名称字段“ ptcpt_nm ”建 立全文索引 数据库用户名： cacps2_ceshi索引名称： IDX_CNDATABANKINFO_PKNM */ - 在 DBA用户权限下，将 CTX_DDL 使用权限付给 cacps2_ceshi 用户GRANTEXECUTEON ctx ddl TO cacps2 ceshi;- 以下操作均使用 cacps2_ceshi 用户进行操作- 查询是否存在索引 IDX_CNDATABANKINFO_PKNMSELEC

26、 T* FROM user_indexes t WHER tE.index_name= IDX_CNDATABANKINFO_PK;N M - 去除已经存在的索引DROPINDEX IDX_CNDATABANKINFO_PKNM;- 设置全文索引参数 cacps2_ceshi 指的是用户名 BEGINctx_ddl.drop_preference ( ctx_ddl.create_preference (ctx_ddl.drop_preference (ctx_ddl.create_preference(cacps2_ceshi.CHINESE_LEXER);cacps2_ceshi.CHIN

27、ESE_LEXER, CHINESE_VGRAM_LEX)E; Rcacps2_ceshi.ChineseWordList );cacps2_ceshi.ChineseWordList , BASIC_WORDLIST);ctx_ddl.set_attribute(ctx_ddl.set_attribute(cacps2_ceshi.ChineseWordListcacps2_ceshi.ChineseWordList, PREFIX_INDEX, TRUE );, PREFIX_MIN_LENGTH, 1 );, PREFIX_MAX_LENGT,H 5); , SUBSTRING INDE

28、X, YES );ctx_ddl.set_attribute(cacps2_ceshi.ChineseWordListctx_ddl.set_attribute(cacps2_ceshi.ChineseWordListEND;- 查看全文索引参数SELEC T* FROM ctx_preference_values ORDERBY 1; SELEC T* FROM ctx_preferences ORDERBY 1;- 建立 context 类型的全文索引Create INDEX IDX_CNDATABANKINFO_PKoNnM c n_common_data_bank_info(ptcpt_nm) ndextype is ctxsys.CONTEXT parameters ( lexer cacps2_ceshi.chinese_lexer wordlist acps2 ceshi.chinesewordlist);- 全文索引查询语句 containsSELECTCOUN(T*) FRO Mcn_common_data_bank_info t WHER cEontains(t.ptcpt_nm, 农业银行 & 公 司 )0;SELECT* FROMcn_common_data_bank_info t WHERcEontains(t.ptcpt_nm, 农业 & 安徽 & 中