有很多时候，使用 instr 和 like 是很理想的，特别是搜索仅跨越很小的表的时候。然而通过这些文本定位的方法将导致全表扫描，对资源来说消耗比较昂贵，而且实现的搜索功能也非常有限，因此对海量的文本数据进行搜索时，建议使用 oracle 提供的全文检索功能。 # 原理 Oracle 的全文检索实现逻辑，通过 lexer 词法分析器，讲把值的 term 找出来。记录在一组的 DR$ 开头的表里面（主要是 DR$xxxI 表），主要记录 term 的位置，frequency 频率，hash 等信息，oracle 根据谓词，找到对应的 term。 # 分析器种类 - basic_lexer（默认的分析器）：英语和大多数西欧语言使用空格分隔词。 - chinese_vgram_lexer：专用汉语分析器，支持所有汉字字符集（ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8）因为采用了分词的方法，可以查到所有的分词，效率差 - chinese_lexer：用于从中文文本中提取符号的词法分析器。与chinese_vgram_lexer 相比，这个 lexer 提供了以下好处： - 生成更小的索引 - 更好的查询响应时间 - 生成真实世界的令牌，从而获得更好的查询精度 - 支持停止的词 > 追求效率那么使用 CHINESE_LEXER，如果追求准确度那么使用 CHINESE_VGRAM_LEXER（推荐使用chinese_vgram_lexer） # 操作 ### 创建 ```plsql -- 授权执行权限： GRANT EXECUTE ON "CTXSYS"."CTX_DDL" TO "BEIJING"; -- 创建文本解析器 exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); -- 验证 SELECT * FROM CTX_PREFERENCES WHERE PRE_NAME='LEXER'; -- 创建全文索引 CREATE INDEX ind_lot ON lot(lot_id) indextype is ctxsys.context parameters('lexer my_lexer'); ``` ### 使用 ```sql select * from lot where contains(lot_id,'北京')>0 ``` ### 过滤词 ```plsql exec ctx_ddl.create_stoplist('北京'); ``` ### 删除 ```sql 删除全文索引 Drop index index_name force; ``` # 维护全文索引 对于 CTXSYS.CONTEXT 索引，当应用程序对基表进行 DML 操作后，对基表的索引维护是必须的。索引维护包括索引同步和索引优化。 在索引建好后，我们可以在该用户下查到 Oracle 自动产生了以下几个表：(假设索引名为 myindex)： - DR$myindex$I：该表中保存的其实就是 Oracle 分析你的文档后，生成的 term 记录在这里，包括 term 出现的位置、次数、hash 值等。 - DR$myindex$K - DR$myindex$R - DR$myindex$N 当基表中的被索引文档发生 insert、update、delete 操作的时候，基表的改变并不能马上影响到索引上直到同步索引。 ### 查看改动 ```sql SELECT PND_INDEX, PND_ROWID, TO_CHAR(PND_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') PND_TIMESTAMP FROM CTX_USER_PENDING; ``` ### 同步 将新的 term 保存到 I 表； ```plsql ctx_ddl.sync_index( idx_name IN VARCHAR2 DEFAULT NULL memory IN VARCHAR2 DEFAULT NULL, part_name IN VARCHAR2 DEFAULT NULL parallel_degree IN NUMBER DEFAULT 1) ``` - idx_name：索引名称 - memory：指定同步索引需要的内存。默认是系统参数 DEFAULT_INDEX_MRMORY。指定一个大的内存的时候可以加快索引效率和查询速度，且索引有较少的碎片。 - part_name：同步那个分区索引 - parallel_degree：并行同步索引。设置并行度 ##### 示例 ```plsql exec ctx_ddl.sync_index('ind_lot','500M','p1'); ``` ##### 自动 ```plsql DECLARE VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'ctx_ddl.sync_index(''ind_lot'');', SYSDATE, 'SYSDATE + (1/24/6)'); commit; END; / ``` ### 优化 清除 I 表的垃圾，主要是将已经被删除的 term 从 I 表删除。 ##### 示例 ```plsql exec ctx_ddl.optimize_index('ind_lot,'FULL); ``` ##### 自动 ```plsql DECLARE VARIABLE jobno number; BEGIN DBMS_JOB.SUBMIT(:jobno, 'ctx_ddl.optimize_index(''ind_lot'',''FULL'');', SYSDATE, 'SYSDATE + 1'); commit; END; / ```