Oracle 全文索引

本是古典何须时尚 2022-11-12 01:38 566阅读 0赞

### 缘由 ###

我们经常需要对表的内容进行模糊查询，在数据量不算很大的情况下，使用起来还是没什么问题，因为不会出现明显的慢查询问题。但是当某些时候，单表数据量很大的时候，查询速度就会有明显的变慢问题。此时，我们优先应该考虑的方式是选用特长与全文搜索的技术栈，比如ElasticSearch服务。

BUT，有些时候事与愿违，因为一些列因素导致你只能在数据库的现状情况下优化这样的问题。Oracle的全文索引（Oracle9i 开始支持）可能会对你有帮助。

--------------------

**Oracle全文索引的基本知识**

### 一、历史背景 ###

Oracle数据库的全文检索技术已经非常完美，Oracle Text使Oracle9i具备了强大的文本检索能力和智能化的文本管理能力。Oracle Text是Oracle9i采用的新名称，在Oracle8/8i中它被称作Oracle interMedia Text，在Oracle8以前它的名称是Oracle ConText Cartridge。

### 二、Oracle Text 索引文档时所使用的主要逻辑步骤如下： ###

(1) 数据存储逻辑搜索表的所有行，并读取列中的数据。通常，这只是列数据，但有些数据存储使用列数据作为文档数据的指针。

(2) 过滤器提取文档数据并将其转换为文本表示方式。存储二进制文档 (如 Word 或 Acrobat 文件) 时需要这样做。过滤器的输出不必是纯文本格式 – 它可以是 XML 或 HTML 之类的文本格式。

(3) 分段器提取过滤器的输出信息，并将其转换为纯文本。包括 XML 和 HTML 在内的不同文本格式有不同的分段器。转换为纯文本涉及检测重要文档段标记、移去不可见的信息和文本重新格式化。

(4) 词法分析器提取分段器中的纯文本，并将其拆分为不连续的标记。既存在空白字符分隔语言使用的词法分析器，也存在分段复杂的亚洲语言使用的专门词法分析器。

(5) 索引引擎提取词法分析器中的所有标记、文档段在分段器中的偏移量以及被称为非索引字的低信息含量字列表，并构建反向索引。倒排索引存储标记和含有这些标记的文档。

### 三、需要的权限 ###

要使用Oracle Text，必须具有CTXAPP角色或者是CTXSYS用户。Oracle Text为系统管理员提供CTXSYS用户，为应用程序开发人员提供CTXAPP角色。具有CTXAPP角色的用户可执行以下任务：创建索引，管理 Oracle Text 数据字典，包括创建和删除首选项，进行Oracle Text 查询，使用 Oracle Text PL/SQL程序包。

如图所示：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhdG9vcA_size_16_color_FFFFFF_t_70]

### 四、具体的实现 ###

文本装入文本列后，就可以创建Oracle Text索引。文档以许多不同方案、格式和语言存储。因此，每个 Oracle Text 索引有许多需要设置的选项，以针对特定情况配置索引。创建索引时，Oracle Text可使用若干个默认值，但在大多数情况下要求用户通过指定首选项来配置索引。

每个索引的许多选项组成功能组，称为"类"，每个类集中体现配置的某一方面，可以认为这些类就是与文档数据库有关的一些问题。例如：数据存储、过滤器、词法分析器、相关词表、存储等。

每个类具有许多预定义的行为，称之为对象。每个对象是类问题可能具有的答案，并且大多数对象都包含有属性。通过属性来定制对象，从而使对索引的配置更加多变以适应于不同的应用。

**（1）存储（Storage）类**

存储类指定构成Oracle Text索引的数据库表和索引的表空间参数和创建参数。它仅有一个基本对象：BASIC\_STORAGE，其属性包括：`I_Index_Clause、I_Table_Clause、K_Table_Clause、N_Table_Clause、P_Table_Clause、R_Table_Clause`。

**（2）数据存储（Datastore）类**

数据存储：关于列中存储文本的位置和其他信息。默认情况下，文本直接存储到列中，表中的每行都表示一个单独的完整文档。其他数据存储位置包括存储在单独文件中或以其 URL 标识的 Web 页上。七个基本对象包括：`Default_Datastore、Detail_Datastore、Direct_Datastore、File_Datastore、Multi_Column_Datastore 、URL_Datastore、User_Datastore`。

**（3）文档段组（Section Group）类**

文档段组是用于指定一组文档段的对象。必须先定义文档段，然后才能使用索引通过 WITHIN 运算符在文档段内进行查询。文档段定义为文档段组的一部分。包含七个基本对象：`AUTO_SECTION_GROUP、BASIC_SECTION_GROUP、HTML_SECTION_GROUP、NEWS_SECTION_GROUP、NULL_SECTION_GROUP、XML_SECTION_GROUP、PATH_SECTION_GROUP`。

**（4）相关词表（Wordlist）类**

相关词表标识用于索引的词干和模糊匹配查询选项的语言，只有一个基本对象`BASIC_WORDLIST`，其属性有：`Fuzzy_Match、Fuzzy_Numresults、Fuzzy_Score、Stemmer、Substring_Index、Wildcard_Maxterms、Prefix_Index、Prefix_Max_Length、Prefix_Min_Length`。

**（5）索引集（Index Set）**

索引集是一个或多个Oracle 索引 (不是Oracle Text索引) 的集合，用于创建 `CTXCAT`类型的Oracle Text索引，只有一个基本对象`BASIC_INDEX_SET`。

**（6）词法分析器（Lexer）类**

词法分析器类标识文本使用的语言，还确定在文本中如何标识标记。默认的词法分析器是英语或其他西欧语言，用空格、标准标点和非字母数字字符标识标记，同时禁用大小写。包含8个基本对象：`BASIC_LEXER、CHINESE_LEXER、CHINESE_VGRAM_LEXER、JAPANESE_LEXER、JAPANESE_VGRAM_LEXER、KOREAN_LEXER、KOREAN__MORPH_ LEXER、MULTI_LEXER`。

**（7）过滤器（Filter）类**

过滤器确定如何过滤文本以建立索引。可以使用过滤器对文字处理器处理的文档、格式化的文档、纯文本和 HTML 文档建立索引，包括5个基本对象：`CHARSET_FILTER、INSO_FILTER INSO、NULL_FILTER、PROCEDURE_FILTER、USER_FILTER`。

**（8）非索引字表（Stoplist）类**

非索引字表类是用以指定一组不编入索引的单词 (称为非索引字)。有两个基本对象：`BASIC_STOPLIST` (一种语言中的所有非索引字) 、 `MULTI_STOPLIST` (包含多种语言中的非索引字的多语言非索引字表)。

### 五、具体操作实践 ###

**1、创建词法分析器及相关词表**

Begin
    
         -- 定义一个词法分析器（名称为cnlex）
         ctx_ddl.drop_preference('cnlex');
         ctx_ddl.create_preference('cnlex','CHINESE_LEXER');  --针对中文
    
         -- 定义一个相关词表（名称为mywordlist）
        ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
        ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE'); --右模糊匹配如：'a%'
        ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1); --前缀最小长度
        ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 5); --前缀最大长度
        ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES'); --前模糊匹配如：'%a'
    
    end;

上面的语句中，如果是针对英语语种则可以采用下面的语句来定义词法分析器

ctx_ddl.create_preference('mylex','BASIC_LEXER');
    ctx_ddl.set_attribute('mylex','printjoins','_-');

**2、 在需要创建全文索引的表中创建索引**

索引类型必须是 `ctxsys.context`，即应用上下文索引。

如下示例创建索引名称为 `idx_person_desc` 的全文索引，使用上面创建的语法分析器 `cnlex` 和相关词表 `mywordlist`。

create index idx_person_desc on personinfo(persondesc)
       indextype is ctxsys.context
       parameters (
       'DATASTORE CTXSYS.DIRECT_DATASTORE FILTER CTXSYS.NULL_FILTER LEXER cnlex WORDLIST mywordlist'
       );

> 请注意此处 parameters 中采用的是NULL\_FILTER过滤器，如果采用INSO\_FILTER则不能对中文进行全文索引

**3、进行全文索引的同步**

exec ctx_ddl.sync_index('idx_user_info', '20M');

--------------------

### 六、具体理解 ###

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

*  **basic\_lexer**: 针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if , is 等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic\_lexer 分析的结果只有一个term ,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。
 *  **chinese\_vgram\_lexer**: 专门的汉语分析器，支持所有汉字字符集。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term: ‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。
 *  **chinese\_lexer**: 这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese\_lexer的最大改进就是该分析器能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持 utf8, 如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer。

> 以上的说法是针对于Oracle8i或者是更低级版本的，在Oracle 9.2中采用 Chinese\_lexer 分析器测试是没有这个问题的。

如果不做任何设置，Oracle 缺省使用 basic\_lexer 这个分析器。要指定使用哪一个lexer, 可以这样操作：

*  **第一，在ctxsys用户下建立一个preference:**

ctx_ddl.create_preference('cnlex','CHINESE_LEXER');

*  **第二，在建立intermedia索引时，指明所用的lexer:**

> 这样建立的全文检索索引，就会使用CHINESE\_LEXER作为分析器。

### 七、检查、同步和优化 ###

**检查索引是否创建成功**

*  1、检查DR m y i n d e x myindex myindexI是否存在，其中的 myindex 代表建立的索引名称；
 *  2、检查全文索引是否创建成功，最好采用 Contains来检查。

-- 语法
    Contains(ColumnName,SearcherKey) > 0
    -- 参数ColumnName为所需要检查的列名，也即创建了全文索引的列名
    -- 参数SearcherKey 为你需要查找的内容，为字符型
    
    -- 示例
    select * from PERSONINFO Where Containts(PERSONDESC, 'abcd',1) > 0
    select * from PERSONINFO Where Containts(PERSONDESC, 'abcd',1) > 0

> 如果你创建的全文索引不成功，则返回失败，其内容为：`ORA-20000: Oracle Text error: DRG-10599: 列没有编制索引`。当然，如果你创建成功，则会正确返回数据。还有一个检查全文索引是否创建成功的方法是感觉创建全文索引后的查询速度。

在intermedia索引建好后，如果表中的数据发生变化，比如增加或修改了记录，怎么办？由于对表所发生的任何dml语句，都不会自动修改索引，因此，必须定时同步(sync)和优化(optimize)索引，以正确反映数据的变化。

在索引建好后，我们可以在该用户下查到Oracle自动产生了以下几个表：（假设索引名为myindex）：

DR$myindex$I，DR$myindex$K，DR$myindex$R，DR$myindex$N

其中以I表最重要，可以查询一下该表，看看有什么内容：

select token_text, token_count from DR$I_RSK1$I where rownum<=20;

可以看到，该表中保存的其实就是Oracle 分析你的文档后，生成的term记录在这里，包括term出现的位置、次数、hash值等。当文档的内容改变后，可以想见这个I表的内容也应该相应改变，才能保证Oracle在做全文检索时正确检索到内容（因为所谓全文检索，其实核心就是查询这个表）。那么如何维护该表的内容呢？总不能每次数据改变都重新建立索引吧！这就用到sync 和 optimize了。

**同步（sync）**:将新的term 保存到I表。

**优化（optimize）**:清除I表的垃圾，主要是将已经被删除的term从I表删除。

Oracle10g 开始为全文索引新增了两个自动同步语句使用方法（如下2和3为10g新增的）

*  1、ctx\_ddl.sync\_index
 *  2、sync(on commit)
 *  3、sync( every …) in parameter setting while creating index

> sync(on commit)　是采用的子事务autonomous transaction;  
> sync(every …) 是利用的SCHEDULER

在创建全文索引的同时，指定 parameters 参数，直接设定定时任务的规则，如下示例：

create index idx_person_desc on personinfo(persondesc) 
    indextype is ctxsys.context 
    parameters('sync (every "sysdate+(1/96)")');

> 其中 `sync (every "sysdate+(1/96)")` 为设定的规则，sysdate+(1/96) 为当前时间增加15分钟的意思。

下面的语句可以查询到我们通过参数新增的任务信息：

select JOB_ACTION from  DBA_SCHEDULER_JOBS where job_name='DR$MY_INDEX$J';
    
    -- 输出
    JOB_ACTION
    ctxsys.drvdml.auto_sync_index('MY_INDEX', 12582912, NULL,  NULL, NULL, 0);

### 八、测试验证 ###

下面是对一个表进行操作时，在同一台机器中获得的测试数据。  
PersonInfo表中共有 182263 条记录，其中persondesc不为null的记录数为180187 条记录，  
其中，persondesc 包括 “大学”两个汉字的记录数为 21579 条记录  
persondesc 包括 “1999”两个汉字的记录数为 10889条记录

**测试一、直接用like 来查询中文“大学”**

SQL: Select count(*) From personinfo Where persondesc like '%大学%'
    时间开销：耗时 40秒 688 毫秒

**测试二、直接用like 来查询英文“1999”**

SQL: Select count(*) From personinfo Where persondesc like '%1999%'
    时间开销：耗时      47秒 218毫秒

**测试三、未创建全文索引时，直接用dbms\_lob.instr 来查询中文“大学”**

SQL: Select count(*) From personinfo Where dbms_lob.instr(persondesc,'大学',1,1)>0
    时间开销：耗时      47秒 031毫秒

**测试四、未创建全文索引时，直接用dbms\_lob.instr 来查询英文“1999”**

SQL: Select count(*) From personinfo Where dbms_lob.instr(persondesc,'1999',1,1)>0
    时间开销：耗时      44秒 360毫秒

**测试五、未创建全文索引时，直接用Contains 来查询中文“大学”**

SQL: Select count(*) From personinfo Where Contains(persondesc,'大学',1)>0";
    执行失败： ORA-20000: Oracle Text error: DRG-10599: 列没有编制索引

**测试六、未创建全文索引时，直接用Contains 来查询英文“1999”**

SQL: Select count(*) From personinfo Where Contains(persondesc,'1999',1)>0";
    执行失败： ORA-20000: Oracle Text error: DRG-10599: 列没有编制索引

**测试七、采用CHINESE\_LEXER词法分析器创建全文索引后，直接用Contains 来查询英文“1999”**

SQL: Select count(*) From personinfo Where Contains(persondesc,'1999',1)>0";
    时间开销：第一次查询耗时  469毫秒，后面的多次查询耗时   210毫秒左右

> 查询出来的记录数比在未建立索引时用like、dbms\_lob.instr方式查询出来的记录数要少一些

**测试八、采用CHINESE\_LEXER词法分析器创建全文索引后，直接用Contains 来查询中文“大学”**

SQL: Select count(*) From personinfo Where Contains(persondesc,'大学',1)>0";
    时间开销：第一次查询耗时  9秒359毫秒，后面的多次查询耗时    210毫秒左右

> 查询出来的记录数比在未建立索引时用like、dbms\_lob.instr方式查询出来的记录数要少一些

**测试九、采用CHINESE\_LEXER词法分析器创建全文索引后，用dbms\_lob.instr 来查询中文“大学”**

SQL: Select count(*) From personinfo Where dbms_lob.instr(persondesc,'大学',1,1)>0
    时间开销：耗时      54秒 953毫秒

**测试十、采用CHINESE\_LEXER词法分析器创建全文索引后，直接用dbms\_lob.instr 来查询英文“1999”**

SQL: Select count(*) From personinfo Where dbms_lob.instr(persondesc,'1999',1,1)>0
    时间开销：耗时      52秒 652毫秒

实际项目应用中，还需要根据实际需求选择和操作。

--------------------

（END）

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NhdG9vcA_size_16_color_FFFFFF_t_70]: /images/20221022/b874b108256649e79f3524ec70b0e1cc.png