Oracle全文索引技术-使用说明文档

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

2、NS 语法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和Oracle Text，可以方便而有效地利用标准的SQL工具来构建基于文本的新的开发工具或对现有应用程序进

4、行扩展。应用程序开发人员可以在任何使用文本的Oracle数据库应用程序中充分利用Oracle Text搜索，应用范围可以是现有应用程序中可搜索的注释字段，也可是实现涉及多种文档格式和复杂搜索标准的大型文档管理系统。Oracle Text支持Oracle数据库所支持的大多数语言的基本全文搜索功能。扩展阅读： 3 建立全文索引的操作步骤3.1 查看用户与角色检查数据库中是否有 CTXSYS 用户 和 CTXAPP 角色如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys的用

5、户。 - 锁住用户ALTER USER ctxsys ACCOUNT LOCK PASSWORD EXPIRE;- 解锁用户ALTER USER ctxsys ACCOUNT UNLOCK IDENTIFIED BY ctxsys;如果没有该用户，则需要打开dbca工具中选择configrue database options，然后选择所有数据库组件安装即可。3.2 开启目标用户全文索引权限赋权在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户，例：GRANT EXECUTE ON ctx_ddl TO username;3.3 设置语法分析器LexerOracle实现全文

6、检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为 term)找出来，记录在一组 以dr$开头的表中，同时记下该term出现的位置、次数、hash 值等信息。检索时，Oracle 从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分(score),即所谓的匹配率。而lexer则是该机制的核心，它决定了全文检索的效率。Oracle 针对不同的语言提供了不同的 lexer, 而我们通常能用到其中的三个：Ø basic_lexer ：针对英语（默认分析器）Ø chinese_vgram_

7、lexer ：专门针对汉语，对汉字搜索准确性较高如：中国人民站起来了这句话，会被它分析成如下几个term: 中，中国，国人，人民，民站，站起，起来，来了，了Ø chinese_lexer ：新的汉语分析器，只支持 UTF8 字符集，可以将语句分词成常见的词组，能跟有效率的分析语句，对汉字解析搜索效率较高【指定语法分析器：】1. 当前用户下下建立一个preference(例：在pomoho用户下执行以下语句) EXEC ctx_ddl.create_preference('my_lexer','chinese_vgram_lexer');2.

8、在建立全文索引索引时，指明所用的lexer: CREATE INDEX myindex ON mytable(mycolumn) INDEXTYPE IS ctxsys.context PARAMETERS('lexer my_lexer');这样建立的全文检索索引，就会使用chinese_vgram_lexer作为分析器。 3.4 建立索引 通过以下语法建立全文索引 3.5 使用索引 使用全文索引很简单，可以通过： SELECT * FROM PUBMENU WHERE CONTAINS(MENUNAME, '上传图片') > 0;4

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

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

11、ASTORE');- 建立 preference 对应的字段值（以ctxsys登陆）EXEC ctx_ddl.set_attribute('ctx_idx_subject_pref','columns','subjectname,briefintro');-建立全文索引CREATE INDEX ctx_idx_subject ON pmhsubjects(subjectname) INDEXTYPE IS ctxsys.context PARAMETERS('DATASTORE ctxsys.ctx_idx_subject_pre

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

13、询语法有：contains(列名称,查询关键字)Logical Operators：组合搜索条件，通过使用AND，OR等逻辑符号。n AND（&），同时含有所有关键词，如：'cats AND dogs'，'cats & dogs'n OR（|），含有所有关键词中的任意一个，如：'cats | dogs'，'cats OR dogs'sn NOT（），不含该关键词，如：'animals dogs'n ACCUM（，），与|类似，如：'dogs, cats, puppies'n EQUI

14、V（=），如：'German shepherds=alsatians are big dogs'n ABOUT等【示例：】包含：“中国、安徽”关键字的“建设”或“农业”银行SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'( 建设 | 农业 ) & 安徽 & 中国')>0;6.2 全文索引的维护对于CTXSYS.CONTEXT索引，当应用程序对基表进行DML操作后，对基表的索引维护是必须的。索引维护包括索引同步和索引优化。 在索引建好后，我们可以在该用户下

15、查到Oracle自动产生了以下几个表：(假设索引名为myindex)： DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$N其中以I表最重要，可以查询一下该表，看看有什么内容：-IDX_CNDATABANKINFO_PKNMSELECT * FROM cn_common_data_bank_info;SELECT * FROM user_indexes;SELECT t.token_text,t.token_count FROM dr$idx_cndatabankinfo_pknm$i t;这里就不列出查询接过了。可以看到，该表中保存的其实就是

16、Oracle 分析你的文档后，生成的term记录在这里，包括term出现的位置、次数、hash值等。当文档的内容改变后，可以想见这个I表的内容也应该相应改变，才能保证Oracle在做全文检索时正确检索到内容(因为所谓全文检索，其实核心就是查询这个表)。这就用到sync(同步) 和 optimize(优化)了。 n 同步(sync): 将新的term 保存到I表； n 优化(optimize): 清除I表的垃圾，主要是将已经被删除的term从I表删除。 当基表中的被索引文档发生insert、update、delete操作的时候，基表的改变并不能马上影响到索引上直到同步索引。可以查询视图CTX_U

17、SER_PENDING查看相应的改动。例如：SELECT * FROM ctx_user_pending t; SELECT T.PND_INDEX_NAME, T.PND_ROWID, TO_CHAR(T.PND_TIMESTAMP, 'dd-mm-yyyy hh24:mi:ss') TIMESTAMP FROM CTX_USER_PENDING T;6.2.1 同步数据同步和优化方法: 可以使用Oracle提供的ctx_ddl包同步和优化索引 对于CTXCAT类型的索引来说， 当对基表进行DML操作的时候，Oracle自动维护索引。对文档的改变马上反映到索引中。CTXCAT

18、是事务形的索引。 【索引的同步 】在对基表插入，修改，删除之后同步索引。推荐使用sync同步索引。语法： CTX_DDL.SYNC_INDEX( IDX_NAME IN VARCHAR2 DEFAULT NULL,-索引名称 MEMORY IN VARCHAR2 DEFAULT NULL,-指定同步索引需要的内存。默认是系统参数：DEFAULT_INDEX_MEMORY -指定一个大的内存时候可以加快索引效率和查询速度，且索引有较少的碎片 PART_NAME IN VARCHAR2 DEFAULT NULL,-同步哪个分区索引 PARALLEL_DEGREE IN NUMBER DEFAULT

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

20、。当你经常的对表文本数据进行更新，删除操作的时候，垃圾处理是很必要的。 EXEC ctx_ddl.optimize_index('myidx', 'full');实施建议：每天在系统空闲的时候对全文索引进行相应的优化，以提高检索的效率- 同步、优化 全文索引IDX_CNDATABANKINFO_PKNM ，10W数据，执行时间小于1分钟BEGIN ctx_ddl.sync_index('IDX_CNDATABANKINFO_PKNM'); ctx_ddl.optimize_index('IDX_CNDATABANKINFO_PKNM

21、9;,'full');END;/6.3 单个中文字查询问题查询单个中文字时，出现错误：SELECT T.ID, T.BK_CD, T.PTCPT_NM FROM CN_COMMON_DATA_BANK_INFO T WHERE T.STATUS = 1 AND CONTAINS(T.PTCPT_NM, '行 & 银 & 云') > 0; ORA-29902：执行ODCIIndexStart() 例行程序中出错； ORA-20000：Oracle Text Error； DRG-10847：CONTAINS 要求这种类型的查询有前缀索引；改成

22、查询：两个字的中文则正常CONTAINS(T.PTCPT_NM, '分行 & 银行 & 云南')【原因】默认的中文分词器，只将中文分割成如：“中国人民银行” -> “中国 国人 人民 民银 银行”，没有前缀索引，需要手工修改配置，修改如下SELECT * FROM user_indexes ;DROP INDEX IDX_CNDATABANKINFO_PKNM;-BEGIN ctx_ddl.drop_preference('cacps2_ceshi.CHINESE_LEXER'); ctx_ddl.drop_preference('

23、cacps2_ceshi.ChineseWordList'); ctx_ddl.create_preference ('cacps2_ceshi.CHINESE_LEXER', 'CHINESE_VGRAM_LEXER'); ctx_ddl.create_preference('cacps2_ceshi.ChineseWordList', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_I

24、NDEX','TRUE'); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MIN_LENGTH',1); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MAX_LENGTH', 5); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','SUBSTRING_INDEX'

25、, 'YES');END;/-Create INDEX IDX_CNDATABANKINFO_PKNM on cn_common_data_bank_info(ptcpt_nm) indextype is ctxsys.CONTEXT parameters('lexer cacps2_ceshi.chinese_lexer wordlist cacps2_ceshi.chinesewordlist');【总结：】n OracleText要支持中文必须得用CHINESE_VGRAM_LEXER。 n 要支持一个中文字以上的搜索，必须配置一个WordList，且把它

26、的属性Prefix_Index配置为True。7 建立全文索引的完整脚本/* -以下脚本为代收付系统“行名行号表 cn_common_data_bank_info” 银行名称字段“ptcpt_nm ”建立全文索引数据库用户名：cacps2_ceshi索引名称： IDX_CNDATABANKINFO_PKNM- */- 在DBA用户权限下，将 CTX_DDL 使用权限付给 cacps2_ceshi 用户GRANT EXECUTE ON ctx_ddl TO cacps2_ceshi;- 以下操作均使用 cacps2_ceshi 用户进行操作- 查询是否存在索引 IDX_CNDATABANKINF

27、O_PKNMSELECT * FROM user_indexes t WHERE t.index_name='IDX_CNDATABANKINFO_PKNM'- 去除已经存在的索引DROP INDEX IDX_CNDATABANKINFO_PKNM;- 设置全文索引参数 cacps2_ceshi 指的是用户名BEGIN ctx_ddl.drop_preference ('cacps2_ceshi.CHINESE_LEXER'); ctx_ddl.create_preference ('cacps2_ceshi.CHINESE_LEXER', &#

28、39;CHINESE_VGRAM_LEXER'); ctx_ddl.drop_preference ('cacps2_ceshi.ChineseWordList'); ctx_ddl.create_preference('cacps2_ceshi.ChineseWordList', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_INDEX','TRUE'); ctx_ddl.se

29、t_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MIN_LENGTH',1); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MAX_LENGTH', 5); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','SUBSTRING_INDEX', 'YES');END;/- 查看全文索引参数SELEC

30、T * FROM ctx_preference_values ORDER BY 1;SELECT * FROM ctx_preferences ORDER BY 1;- 建立 context 类型的全文索引Create INDEX IDX_CNDATABANKINFO_PKNM on cn_common_data_bank_info(ptcpt_nm) indextype is ctxsys.CONTEXT parameters('lexer cacps2_ceshi.chinese_lexer wordlist cacps2_ceshi.chinesewordlist');-

31、 全文索引查询语句 containsSELECT COUNT(*) FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'农业银行 & 公司')>0;SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'农业 & 安徽 & 中 & 部')>0;SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'( 建设 | 农业 ) & 安徽 & 中国