Oracle 全文索引

前言：

       Oracle从7.3开始支持全文检索，即用户可以使用Oracle服务器的上下文（ConText）选项完成基于文本的查询。具体可以采用通配符查找、模糊匹配、相关分类、近似查找、条件加权和词意扩充等方法。在Oracle8.0.x中称为ConText ；在Oracle8i中称为interMedia Text ； Oracle9i中称为Oracle Text。

Oracle Text是9i标准版和企业版的一部分。Oracle9i将全文检索功能做为内置功能提供给用户，使得用户在创建数据库实例时自动安装全文检索。Oracle Text的应用领域有很多：

l         搜索文本 ：需要快捷有效搜索文本数据的应用程序。

l         管理多种文档：允许搜索各种混和文档格式的应用程序,包括ord,excel,lotus等。

l         从多种数据源中检索文本：不仅来自Oracle数据库中的文本数据,而且可以来自Internet和文件系统的文本数据。

l         搜索XML应用程序。

1、搜索文本

       不使用Oracle text功能,也有很多方法可以在Oracle数据库中搜索文本.可以使用标准的INSTR函数和LIKE操作符实现.

SELECT *

FROM mytext

WHERE INSTR (thetext, ‘Oracle‘) > 0;

SELECT *

  FROM mytext

WHERE thetext LIKE ‘%Oracle%‘;

有很多时候，使用instr和like是很理想的，特别是搜索仅跨越很小的表的时候。然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵，而且实现的搜索功能也非常有限。

利用Oracle Text，你可以回答如“在存在单词’Oracle’的行同时存在单词’Corporation’而且两单词间距不超过10个单词的文本，查询含有单词’Oracle’或者单词’california’的文本，并且将结果按准确度进行排序，含有词根train的文本”，以下的sql代码实现了如上功能，我们且不管这些语法是如何使用的：

DROP　INDEX index mytext_idx;  --丢弃索引mytext_idx

/

CREATE INDEX mytext_idx

ON mytext( thetext )

INDEXTYPE is CTXSYS.CONTEXT; --创建CONTEXT类型索引mytext_idx

/

SELECT id

FROM mytext

WHERE contains (thetext, ‘near((Oracle,Corporation),10)‘) > 0; --发出contains查询

／

SELECT score (1), id

    FROM mytext

   WHERE contains (thetext, ‘Oracle or california‘, 1) > 0

ORDER BY score (1) DESC

/

SELECT id

  FROM mytext

WHERE contains (thetext, ‘$train‘) > 0;

2、索引介绍

       利用Oracle Text对文档集合进行检索的时候，你必须先在你的文本列上建立索引。索引将文本打碎分成很多记号（token），这些记号通常是用空格分开的一个个单词。

Oracle Text应用的实现实际上就是一个 数据装载—> 索引数据—>执行检索 的一个过程。

建立的Oracle Text索引被称为域索引（domain index），包括4种索引类型：

CONTEXT、CTXCAT、CTXRULE 、CTXXPATH

依据你的应用程序和文本数据类型你可以任意选择一种。可以利用Create Index建立这4种索引。下面说一下这4种索引的使用环境：

l         CONTEXT：用于对含有大量连续文本数据进行检索。支持word、html、xml、text等很多数据格式。支持中文字符集，支持分区索引，唯一支持并行创建索引（Parallel indexing）的索引类型。对表进行DML操作后，并不会自动同步索引。需要手工同步索引。查询操作符：CONTAINS。

l         CTXCAT：当使用混合查询语句的时候可以带来很好的效率。适合于查询较小的具有一定结构的文本段。具有事务性，当更新主表的时候自动同步索引。The CTXCAT index does not support table and index partitioning, documents services (highlighting, markup, themes, and gists) or query services (explain, query feedback, and browse words.)。查询操作符：CATSEARCH

l         CTXRULE：Use to build a document classification application. You create this index on a table of queries, where each query has a classification. Single documents (plain text, HTML, or XML) can be classified by using the MATCHES operator。查询操作符：MATCHES。

l         CTXXPATH：Create this index when you need to speed up ExistsNode() queries on an XMLType column。Can only create this index on XMLType column.。查询操作符：无。

在以上4种索引中，最常用的就是CONTEXT索引，使用CONTAINS操作符进行查询。Oracle Text 索引将文本打碎分成很多的记号（token），例如文本‘I Love www.itpub.net’将会被分成：I，LOVE，WWW，ITPUB，NET这样的记号（token）。

Oracle Text CONTEXT 索引是反向索引（inverted index）。每个记号 （token）都映射着包含它自己的文本位置。在索引建立过程中，单词Cat会包括如下的条目入口：

Cat row1,row2,row3

表示Cat在行row1、row2、row3都出现过，这样通过查找单词所对应的行的rowid就可以迅速找到文本记录。

在索引建好后，我们可以在该用户下查到Oracle自动产生了以下几个表：（假设索引名为myindex）：DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$N其中以I表最重要，默认情况下全文索引是不区分大小写。

赋权限：

grant resource,dba,connect,ctxapp to username;

grant execute on ctxsys.ctx_ddl to username;--用于创建同步和优化索引的存储过程。

说明：ctxapp用于用户建立Oracle Text索引。

3、CONTEXT索引

语法：

CREATE INDEX [schema.]index on [schema.]table(column) INDEXTYPE IS ctxsys.context [ONLINE]

LOCAL [(PARTITION [partition] [PARAMETERS(‘paramstring‘)]

[, PARTITION [partition] [PARAMETERS(‘paramstring‘)]])]

[PARAMETERS(paramstring)] [PARALLEL n] [UNUSABLE];

数据库用创建和插入这些索引的方法叫做索引管道（index Pipeline）。根据不同的参数构建索引，可以应用于很多实际环境。

这些参数在建立CONTEXT索引过程中将按下图顺序对索引进程起作用。在本篇中提供一些简单demo会看到各个参数的作用。

建立索引时，系统默认文档存储在数据库的文本列中。如果不显示的指定索引参数，系统会自动探测文本语言,数据类型和文档格式。

CREATE INDEX myindex ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT;

如上命令在表docs的text列上建立了一个默认参数的CONTEXT类型索引myindex，系统默认：

l         文本存储在数据库中。可以是CLOB, BLOB, BFILE, VARCHAR2, or CHAR类型的文本数据。

l         文本列语言是数据库建立时的默认的字符集。

l         使用数据库默认的终止目录stoplist.stoplist记录存在于文本列中但不对其索引的词。

l         允许模糊查询。

索引参数

Oracle Text 索引文档时所使用的主要参数如下：

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

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

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

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

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

DataStore：指明你的文本是如何存储的。系统默认文档储存在数据库内的文本列（CHAR, VARCHAR, VARCHAR2, BLOB, CLOB, BFILE, or XMLType）中。DataStore对象在由过滤器处理之前从数据库中的列摘录文本。你要索引的文档可以来自多种数据源。

说明：MULTI_COLUMN_DATASTORE类型的DATASTORE必须在ctxsys用户下建立，在使用时还需要指明来源，如：

SQL>CONNECT CTXSYS/CTXSYS@SDH155

SQL>EXEC CTX_DDL.CREATE_PREFERENCE(‘mymds’,’MULTI_COLUMN_DATASTORE’);

SQL>EXEC ctx_ddl.set_attibute(‘mymds‘, ‘columns‘, ‘name, address‘);

SQL>create index doc_idx on docs(doc) indextype is ctxsys.context

parameters(‘DATASTORE ctxsys.mymds’);

Filter 过滤：一旦汇编了文档，它就沿管道传递。接下来这个阶段是过滤（Filter）.如果文档是一种外来格式，就将它转换为可读取的文本，以便进行索引。默认是NULL_FILTER，它简单的直接传递文档，不作任何修改。

通常我们使用NULL_FILTER 过滤普通文本和HTML文档。下面是一个索引HTML文档的例子：

CREATE INDEX myindex

             ON docs(htmlfile)

INDEXTYPE IS ctxsys.CONTEXT  

PARAMETERS(‘filter ctxsys.null_filter  section group ctxsys.html_section_group‘);

我们使用null_filter过滤类和ctxsys用户自带的 html_section_group区段组类。我们会在后面马上介绍区段组（Section Groups）的概念。

Section Groups区分组：区分组（Section Groups）是与interMedia一起使用XML的关键。这些组处理XML（或者HTML）文档，输出两个数据流，即区段界限和文本内容。默认是NULL_SECTION_GROUP,它简单的直接传递文本，不执行任何修改和处理。HTML_SECTION_GROUP是专门用来处理HTML文档的。

Storage 类：Storage(存储空间)组的类只含有BASIC_STORAGE.默认情况下，BASIC_STORAGE对象的属性是空的。我们通常需要定制自己的STORAGE类，来控制索引的存储参数以及存储空间。建立全文索引的时候我们通常会考虑表段dr$indexname$I，，dr$indexname$R，索引段dr$indexname$X的空间分配。

BASIC_STORAGE 有如下参数

默认情况下，4个表段和1个索引段将会建立在拥有该表的用户的默认表空间下。如下:

CREATE INDEX iowner.idx ON towner.tab(b) INDEXTYPE IS ctxsys.CONTEXT;

索引将会建立在IOWNER用户的默认表空间下，而不管发出该语句的用户是否是IOWNER

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

l         basic_lexer: 针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if , is 等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic_lexer 分析的结果只有一个term ,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。

l         chinese_vgram_lexer: 专门的汉语分析器，支持所有汉字字符集（ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 ）。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term: ‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。

l         chinese_lexer: 这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器 能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大 提高了效率。但是它只支持 utf8, 如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer.

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

1）    设置词法分析器：

BEGIN

  ctx_ddl.create_preference (‘my_lexer‘, ‘chinese_vgram_lexer‘);

END;

/

2）    建立索引时指定词法分析器：

CREATE INDEX  myindex ON mytable(mycolumn) indextype is ctxsys.context

parameters(‘lexer my_lexer‘);

这样建立的全文检索索引，就会使用chinese_vgram_lexer作为分析器。 相应的，索引中文就比索引英文占用的表空间多了许多。Oracle Text为了性能不得不牺牲了空间。如下是我的简单存储空间测试：

STOP Lists类：Stop List只不过是被索引忽略的单词的列表。这些通常是常见的单词，正常情况下不会以任何方式查询它们，因此，索引它们纯粹是表格空间和处理器周期的浪费。在具体的应用中，可能存在这样的单词，它们在特定的文集中出现的频率太大，无法提供有意义的内容，特别是常用的单词。Stop List可以含有最多4095个单词，每个单词最多64个字符，同时为英语和其它语言提供了默认列表。

下图是Chinese Stoplist (Simplified)的默认列表:

可以查看英文的默认列表：

SELECT spw_word FROM DR$STOPWORD;

可以查询ctx_stoplists和ctx_stopwords 视图来观察这些语言。

EXECUTE ctx_ddl.create_stoplist(‘stoppref‘);

SELECT *

  FROM ctx_stoplists;

EXECUTE ctx_ddl.add_stopword(‘stoppref‘,‘的‘);

SELECT *

FROM ctx_stoplists;

SELECT spw_word

  FROM dr$stopword;

Lists类：要考虑的最后一个类是单一的Word List类，即BASIC_WORDLIST。创建索引时不使用这个类，这个类只在某些高级形式的查询中使用。茎干查询使用从Xerox公司许可的技术，来匹配单词与通用的语言根。

其它选项：MEMORY参数以通常的方式附着到CREATE INDEX中的PARAMETERS上，设置用于构建或更改索引的内存量。这个量不能超过MAX_INDEX_MEMEORY,使用CTX_ADM.SET_PARAMETER 对其进行设置。

查看系统默认参数项：

SELECT par_name, par_value FROM ctx_parameters;

设置系统默认参数：

CTX_ADM.SET_PARAMETER(param_name IN VARCHAR2,

                      param_value IN VARCHAR2);

Oracle Text使用的索引机制比通常的ORACLE B-TREE索引更复杂，且文档实际是在内存中构建的，而不是一次一行的添加到B-TREE。到达内存参数指定的值时，更新磁盘山的索引，接着，缓冲区由下一组文档重用。任一时刻缓冲区内的文档数会有所不同，并且在索引处理之前不进行任何排序。因此，在少量的内存中索引大量文档会导致出现碎片索引情况。

4、管理DML操作

对于CTXSYS.CONTEXT索引，当应用程序对基表进行DML操作后，对基表的索引维护是必须的。索引维护包括索引同步和索引优化。在索引建好后，我们可以在该用户下查到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表删除。 

当基表中的被索引文档发生insert、update、delete操作的时候，基表的改变并不能马上影响到索引上直到同步索引。可以查询视图CTX_USER_PENDING查看相应的改动。例如：

SELECT pnd_index_name, pnd_rowid,

      TO_CHAR (pnd_timestamp, ‘dd-mon-yyyy hh24:mi:ss‘) timestamp

  FROM ctx_user_pending;

该语句的输出类似如下：

PND_INDEX_NAME                 PND_ROWID          TIMESTAMP

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

MYINDEX                        AAADXnAABAAAS3SAAC 06-oct-1999 15:56:50

同步和优化方法: 可以使用Oracle提供的ctx_ddl包同步和优化索引，通过

DESC CTX_DDL

可以查看ctx_ddl包的所有过程。

索引同步

l        CTXSRV(同步进程)

Oracle提供一个全文索引同步服务进程负责监视索引表变动并且第一时间同步索引。

只需要在后台运行这个进程，它会监视数据的变化，及时进行同步。但由于存在一些问题在未来的ORACLE版本中将要被取代。启动同步索引服务进程方法：

HOST ctxsrv -user ctxsys/ctxsys>&/tmp/ctx.log&

当你启动了CTXSRV服务进程，在后台的同步请求处理就会像实时一样，在你提交修改1，2秒后新的数据马上就被索引了。 与手工同步相比，自动索引同步更容易使索引变的稀疏，需要执行DBMS_JOB定期优化和重建索引rebuild parameters( ‘sync‘ )。 默认情况下，如果你不启动CTXSRV进程，索引不会自动更新除非你手工告诉它们去更新自己。你可以使用 alter index <iname> rebuild parameters (‘sync‘) 更新索引。

ALTER INDEX search_idx  REBUILD parameters( ‘sync‘ )

/

Index altered.

9i提供了新的专门用于更新索引的包ctx_ddl.sync_index(…)

l        CTX_DDL.SYNC_INDEX（同步索引）

在对基表插入，修改，删除之后同步索引。推荐使用sync同步索引。

语法：

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_MEMORY 。指定一个大的内存时候可以加快索引效率和查询速度，且索引有较少的碎片

part_name  同步哪个分区索引。

parallel_degree 并行同步索引。设置并行度。 例如：

使用2M内存同步索引myindex:

BEGIN

   ctx_ddl.sync_index (‘myindex‘, ‘2M‘);

END;

NOTE：执行者必须是索引所有者或者CTXSYS用户。如果执行者是CTXSYS用户，索引名称可以是空NULL，这样默认优化全部的CONTEXT索引。这样的同步效果就如同ctxsrv. 我们推荐定期执行作业job同步索引。-- 为每一个索引制定单独的作业job, 一个 ctxsys 作业job同步全部索引。这样就减少了使用ctxsrv的机率，也不用在每次数据库启动后都要启动CTXSRV服务进程。由于CTXSRV有一些缺陷，在未来将不再会被ORACLE使用或者被取代。

INSERT INTO mytable

     VALUES (2, ‘first,second.this is the second rows before indexed‘);

COMMIT ;

EXEC ctx_ddl.sync_index(‘mytable_idx‘);--执行同步

SELECT /*+ FIRST_ROWS() */ ID, SCORE(1), TEXT

   FROM MYTABLE               

   WHERE CONTAINS (TEXT, ‘searchterm‘, 1) > 0               

ORDER BY SCORE(1) DESC;

其中score(1)为Oracle为全文查询计算的主题符合程度。

索引优化

经常的索引同步将会导致CONTEXT索引产生碎片，索引碎片严重的影响了查询的反应速度。你可以定期优化索引来减少碎片，减少索引大小，提高查询效率。为了更好的理解索引优化，我们先看看索引的结构以及碎片是如何产生的。

CONTEXT索引是反向索引，每一个索引项目都包括单词和这个单词所出现过的文档地址。例如在一个初始化索引过程中，单词DOG可以包括如下条目

DOG DOC1 DOC3 DOC5

当新的文档被包含到表的时候，索引被同步。如果新行DOC7也包括单词DOG，将会形成如下条目。

DOG DOC1 DOC3 DOC5

DOG DOC7

很多的DML操作以后，单词DOG的条目可能如下情况：

DOG DOC1 DOC3 DOC5

DOG DOC7

DOG DOC9

DOG DOC11

同步新增加的文档产生了索引碎片，单词DOG的文挡列表会越来越长，索引越来越大。

你可以优化索引（CTX_DDL.OPTIMIZE_INDEX），使用FULL或者FAST参数都可以降低索引碎片，提高索引效率。

文档垃圾处理

当文本从表中删除的时候，Oracle Text标记删除的文档，但是并不马上修改索引。因此，就的文档信息占据了不必要的空间，导致了查询额外的开销。你必须以FULL模式优化索引，从索引中删除无效的旧的信息。这个过程叫做垃圾处理。当你经常的对表文本数据进行更新，删除操作的时候，垃圾处理是很必要的。

BEGIN

   ctx_ddl.optimize_index (‘myidx‘, ‘full‘);

END;

Single Token Optimization

除了优化整个索引以外，你还可以专门对某个标记（token）进行优化。你可以仅仅优化那些经常查询的标记（token）,而不必花太多时间在很少查询的单词上。 例如，你可以专门优化token DOG，它经常被检索或者经常被更新。这样可以提高查询这个token的查询效率。

BEGIN

   ctx_ddl.optimize_index (‘myidx‘, ‘token‘, token => ‘DOG‘);

END;

FAST MODE

这种方法仅仅使碎片行紧凑。但是，旧的数据并不从索引中删除。

BEGIN

   ctx_ddl.optimize_index (‘myidx‘, ‘fast‘);

END;

使用job定时同步和优化

用以下的两个job来完成(该job要建在和表同一个用户下) :

-- sync:

VARIABLE jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,‘ctx_ddl.sync_index(‘‘myindex‘‘);‘,

SYSDATE, ‘SYSDATE + (1/24/4)‘);

commit;

END;

-- optimizer

VARIABLE jobno number;

BEGIN

DBMS_JOB.SUBMIT(:jobno,‘ctx_ddl.optimize_index(‘‘myindex‘‘,‘‘FULL‘‘);‘,

SYSDATE, ‘SYSDATE + 1‘);

commit;

  END;

其中， 第一个job的SYSDATE + (1/24/4)是指每隔15分钟同步一次，第二个job的SYSDATE + 1是每隔1天做一次全优化。具体的时间间隔，你可以根据自己的应用的需要而定。至此，你的全文检索功能已设置完成。

6、查询语法

使用contains时，主要查询语法有：

1)       Logical Operators：组合搜索条件，通过使用AND，OR等逻辑符号。

a）AND（&），同时含有所有关键词，如：‘cats AND dogs‘ ，‘cats & dogs‘

b）  OR（|），含有所有关键词中的任意一个，如：‘cats | dogs‘ ，‘cats OR dogs‘s

c） NOT（~），不含该关键词，如：‘animals ~ dogs‘

d）  ACCUM（，），与|类似，如：‘dogs, cats, puppies‘

e）EQUIV（=），如：‘German shepherds=alsatians are big dogs‘

f）  ABOUT等

具体参见：http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm#CCREF03006

操作示例请参见附录

2)        Query templates :查询模板

7、示例

a）  创建用户：

CREATE USER KNOWLEDGE IDENTIFIED BY KNOWLEDGE

b）  授权：

GRANT CONNECT,DBA,RESOURCE,CTXAPP TO KNOWLEDGE;

GRATN EXECUTE ON CTXSYS.CTX_DDL TO KNOWLEDGE;

c）建表：

CREATE TABLE DOCS(ID NUMBER,NAME VARCHAR2(200),ADDRESS  VARCHAR2(200));

d）  插入数据：

INSERT INTO DOCS VALUES(1, ‘John Smith’,’ 123 Main Street biti’);

e）  建立默认参数索引：

CREATE INDEX DOC_IDX ON DOCS(NAME)

INDEXTYPE IS CTXSYS.CONTEXT

注意：在建立索引的时候会自动将表中已有的数据进行同步。

f） 查询：

SELECT * FROM DOCS WHERE CONTAINS(NAME,’John’)>0;

g）  设置参数：

用ctxsys用户登录，设置多字段数据存储：

EXEC ctx_ddl.create_preference(‘mymds‘, ‘MULTI_COLUMN_DATASTORE‘);

EXEC ctx_ddl. set_attribute (‘mymds‘, ‘columns‘, ‘name, address‘);

用KNOWLEDGE登录设置词法分析器：

BEGIN

  ctx_ddl.create_preference (‘my_lexer‘, ‘chinese_vgram_lexer‘);

END;

h）  重新建立索引：

DROP INDEX DOC_IDX;

CREATE INDEX DOC_IDX ON DOCS(NAME) INDEXTYPE IS CTXSYS.CONTEXT

PARAMETERS(‘DATASTORE CTXSYS.MYMDS LEXER MY_LEXER’);

              说明：PARAMETERS中包含有7个首选项参数，没有指定的为默认，上面的参数中指定了“数据存储”为”ctxsys.mymds”，“词法分析器”为”my_lexer”。也可以通过登录oracle控制台，工具——>数据库应用程序——>Text Manager打开全文索引的创建窗口。

i）  查询：

SELECT * FROM DOCS WHERE CONTAINS(NAME,’ Main’)>0;

j）  创建同步存储过程：

CREATE OR REPLACE PROCEDURE SYNC_Index IS

/******************************************************************************

  同步索引

******************************************************************************/

BEGIN

   CTXSYS.CTX_DDL.SYNC_INDEX(‘ DOC_IDX ‘);

   EXCEPTION

     WHEN NO_DATA_FOUND THEN

       NULL;

     WHEN OTHERS THEN

       -- Consider logging the error and then re-raise

       RAISE;

END SYNC_Index;

/

k） 创建优化存储过程：

CREATE OR REPLACE PROCEDURE OPTIMIZE_Index IS

/******************************************************************************

    优化索引

******************************************************************************/

BEGIN

   CTXSYS.CTX_DDL.OPTIMIZE_INDEX(‘ DOC_IDX ‘,‘FULL‘,null ,null ,null);

   EXCEPTION

     WHEN NO_DATA_FOUND THEN

       NULL;

     WHEN OTHERS THEN

       -- Consider logging the error and then re-raise

       RAISE;

END OPTIMIZE_Index;

/

l）      创建job定时执行同步和优化：

SQL>variable job1 number;

SQL>begin

Dbms_job.submit(:job1,’ SYNC_Index;’,sysdate,’sysdate+1/144’);

End;

/

SQL>variable job1 number;

SQL>begin

Dbms_job.submit(:job1,’ OPTIMIZE_Index;’,sysdate,’sysdate+1/28’);

End;

/

m）  手动执行同步和优化：

执行同步和优化的存储过程：

      exec OPTIMIZE_Index;

   exec SYNC_Index;

直接执行：

BEGIN

    ctx_ddl.sync_index (‘myindex‘, ‘2M‘);

     ctx_ddl.optimize_index (‘myindex‘, ‘full‘,null,null,null);

END;

执行job：

SQL>begin

dbms_job.run(225);

dbms_job.run(226);

End;

/

8、CTXCAT索引

CTXCAT索引是CONTEXT索引的简化版，事务型的索引，CTXCAT索引支持的PREFERENCE包括：LEXER、STOPLIST、WORDLIST和STORAGE参数。不支持其他的参数如：DATASTORE、FILTER、SECTION GROUP。虽然支持LEXER但不支持THEME查询，而且不支持FORMAT、CHARSET和LANGUAGE列，另外不支持表和索引分区。

CTXCAT索引仅仅包含了CONTEXT索引的部分内容，但是CTXCAT索引有其自身的优点，其中最突出的优点就是支持DML同步。CONTEXT索引由于结构过于复杂，且索引的数据量一般较大，因此CONTEXT索引并不是自动同步的。而CTXCAT索引是自动同步的，当发生了DML修改时，Oracle会自动同步CTXCAT索引，降低了索引的维护成本。

CTXCAT索引的另外一个优点就是这里要介绍的INDEX SET属性，这也是CTXCAT索引特有的属性。简单的说，CTXCAT可以建立一个索引集。可以把一些经常与CTXCAT查询组合使用的查询列的索引添加到索引集中，比如，如果在查询文章内容的同时，经常需要查询文章的作者、标题或创建时间等信息，则可以将这些信息列的索引添加到索引集中，Oracle可以将这些查询封装到CATSEARCH操作中，从而提高全文索引的效率。

CTXCAT查询语法：

1)       Logical Operators：组合搜索条件，通过使用AND，OR等逻辑符号

a） AND（&），如：a b c

b）OR (|) ，如：a|b|c

c） NOT (-)，如：a – b，注意与：a-b不同

d）" " (quoted phrases) ，如：”a b c”

e） *(Wildcarding)，如：a*

f）  ()，如：(a&b)|c

2)        Query templates :查询模板，通过查询模板，可以使用CONTAINS的查询语法。

查询模板语法格式：

9、附录

参考文档：http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/csql.htm

以下是做测试过程的所有代码。

以ctxsys用户创建首选项：

EXEC ctx_ddl.drop_preference(‘mymds‘);

EXEC ctx_ddl.create_preference(‘mymds‘, ‘MULTI_COLUMN_DATASTORE‘);

EXEC ctx_ddl.set_attribute(‘mymds‘, ‘columns‘, ‘name, address‘);

desc ctx_ddl;

以当前用户登录：

--删除表

drop table docs;

--创建表

create table docs(

id number,

name varchar2(200),

address varchar2(2000)

);

--插入数据

insert into docs values(1,‘John Smith‘,‘Room 403,No.37,ShiFan Residential Quarter,BaoShan District‘);/

insert into docs values(2,‘Noah Abelard‘,‘Room 201,No.34,Lane 125,XiKang Road(South),HongKou District‘);/

insert into docs values(3,‘Michael Cole‘,‘Room 42， Zhongzhou Road，Nanyang City， Henan Prov. ‘);/

insert into docs values(4,‘Thomas Matthew‘,‘Hongyuan Hotel, Jingzhou city, Hubei Prov. ‘);/

insert into docs values(5,‘Joseph‘,‘Special Steel Corp，No.272， Bayi　Road，Nanyang City， Henan Prov. ‘);/

insert into docs values(6,‘Lauren‘,‘Room 702, 7th Building, Hengda Garden, East District, Zhongshan ‘);/

insert into docs values(7,‘Kevin Victoria‘,‘Room 601, No.34 Long Chang Li, Xiamen, Fujian ‘);/

insert into docs values(8,‘Michael‘,‘Cheng Nuo Ban, Gong Jiao Zong Gong Si, Xiamen, Fujian ‘);/

insert into docs values(9,‘Timothy Katherine‘,‘NO. 204,Entrance A, Building NO. 1, The 2nd Dormitory of the NO. 4 State-owned Textile Factory, 53 Kaiping Road, Qingdao, Shandong‘);/

insert into docs values(10,‘Zhou Wangcai‘,‘Room 601, No.34 Long Chang Li,Xiamen, Fujian, China 361012‘);/

insert into docs values(11,‘Sebastian Jared‘,‘Cheng Nuo Ban, Gong Jiao Zong Gong SiXiamen, Fujian, China 361004‘);/

insert into docs values(12,‘Jenna‘,‘NO. 204, A, Building NO. 1,The 2nd Dormitory of the NO. 4 State-owned Textile Factory,53 Kaiping Road, Qingdao,Shandong, China 266042 ‘);/

insert into docs values(13,‘Catherine‘,‘Room403,No.37,SiFanResidentialQuarter,BaoShanDistrict‘);/

insert into docs values(14,‘Sebastian Cole‘,‘1 Team CaiQi ChuanXiBei Mining Area JiangYou City SiChuan Province China‘);/

insert into docs values(15,‘Timothy Jared ‘,‘Room 201,No.34,Lane 125,XiKang Road(South),HongKou District‘);/

--建立里索引

drop index doc_idx;

create index doc_idx on docs(name) indextype is ctxsys.context;--默认索引,单字段，name

create index doc_idx on docs(name) indextype is ctxsys.context parameters(‘DATASTORE ctxsys.mymds‘);--多字段，name,address

--单字段（name）索引查询

select * from docs where contains(name,‘Jared‘)>0;--id为15

select * from docs where contains(name,‘Building‘)>0;--没有结果

select * from docs where contains(name,‘Sebastian & Cole‘)>0;--注意&之间的空格

select * from docs where contains(name,‘Sebastian | Cole‘)>0;--id分别为3，11，14

select * from docs where contains(name,‘Sebastian ~ Cole‘)>0;--id为11

select * from docs where contains(name,‘Sebastian , Cole‘)>0;--id分别为3，11，14

select * from docs where contains(name,‘Michael = Cole‘)>0;--id分别为3，11，14

select * from docs where contains(name,‘about(Cole)‘)>0;

select * from docs where contains(name,‘Michael‘)>0;

select * from docs where contains(name,‘Michael ~ Cole‘)>0;

select * from docs where contains(name,‘Michael , Cole‘)>0;

select * from docs where contains(name,‘Michael | Cole‘)>0;

select * from docs where contains(name,‘Michael = Cole‘)>0;

select * from docs where contains(name,‘Michael - Cole‘)>0;

select * from docs where contains(name,‘near((Michael,Cole),50,true)‘)>0;

select * from docs where contains(name,‘near((Michael,Cole),50,false)‘)>0;

select * from docs where contains(name,‘near((Michael,Cole),6)‘)>0;

--建立多字段索引（name,address）查询

select * from docs where contains(name,‘$build‘)>0;--Build的所有时态

select * from docs where contains(name,‘about(building)‘)>0;--似乎不区分大小写

select * from docs where contains(name,‘near((Michael,Road),50,true)‘)>0;--取值范围0~100

select * from docs where contains(name,‘near((Michael,Road),50,false)‘)>0;

select * from docs where contains(name,‘near((Michael,Road),,false)‘)>0;

select * from docs where contains(name,‘!Michael,Road‘)>0;

select score(1),id,name,address from docs where contains(name,‘road‘,1) > 0;

select score(1),id,name,address from docs where contains(name,‘road‘,1) = 0;

select score(1),id,name,address from docs where contains(name,‘road‘,1) < 3;

drop index doc_idx_x;

drop index doc_idx_cx;

create index doc_idx_x on docs(name) indextype is ctxsys.context;

create index doc_idx_cx on docs(address) indextype is ctxsys.ctxcat;

select id||‘==>‘||name from docs where contains(name,‘Cole‘)>0;

select id||‘==>‘||name from docs where catsearch(name,‘Cole‘,‘‘)>0;

select id||‘==>‘||name from docs where contains(name,

‘<query>

    <textquery grammar="CTXCAT">Cole</textquery>

    <score datatype="integer" />

</query>‘)>0;

select * from docs where catsearch(address,‘Hongyuan 7th Long‘,null)>0;--同时包含所有关键字

select * from docs where catsearch(address,‘Hongyuan | 7th | Long‘,null)>0;--包含所有关键字中的任意

select * from docs where catsearch(address,‘Hongyuan - 7th‘,null)>0;--含有前面的关键字，同时不含后面的关键字，注意中间的空格

select * from docs where catsearch(address,‘Hongyuan-7th‘,null)>0;--中间的是一个关键词

select * from docs where catsearch(address,‘”Hongyuan  7th“‘,null)>0;--中间的是一个关键词

select * from docs where catsearch(address,‘（Hongyuan 7th）| Long‘,null)>0;--组合查询

select * from docs where catsearch(address,‘Hongy*‘,null)>0;--关键词为字符串中的一部分

select * from docs where catsearch(address,‘Hongy*n‘,null)>0;--关键词中间含有任意字符

select * from docs where contains(name,‘Jiao‘)>0;--是一个单词，要用单引号括起来

select * from docs where contains(name,‘Zong Gong‘)>0;

select * from docs where contains(name,‘"Zong Gong"‘)>0;--一个词组，在单引号里还要用双引号括起来

select * from docs where contains(name,‘"Zong Gong" | China‘)>0;--可以指定逻辑操作符(包括 AND ，AND NOT，OR )

select * from docs where contains(name,‘Zong near China‘)>0;--A NEAR B，就表示条件： A 靠近 B

select * from docs where contains(name,‘Zo*‘)>0;

select * from docs where contains(name,‘ISABOUT (Zong weight (.8), China wright (.4))‘)>0;

select * from docs where contains(name,‘FORMSOF (INFLECTIONAL,street)‘)>0;

select * from docs where catsearch(address,‘Zo*‘,null)>0;--查询将返回包含 ‘hubei‘，‘hunan‘ 等字样的地址。记住是 *，不是 %

select * from docs where catsearch(address,‘ISABOUT (Zong weight (.8), China wright (.4))‘,null)>0;--ISABOUT 是这种查询的关键字，weight 指定了一个介于 0~1之间的数，类似系数。表示不同条件有不同的侧重。

select * from docs where catsearch(address,‘FORMSOF (INFLECTIONAL,street)‘,null)>0;--查询将返回包含 ‘street‘，‘streets‘等字样的地址。对于动词将返回它的不同的时态，如：dry，将返回 dry，dried，drying 等等。

select * from docs where contains(name,‘$build‘)>0;

select * from docs where contains(name,‘?build‘)>0;

select * from docs where catsearch(address,‘build‘,null)>0;

select * from docs where catsearch(address,‘build*‘,null)<1;

select * from docs where catsearch(address,

‘<query>

  <textquery grammar="CONTEXT">

     building

  </textquery>

  <score datatype="float"/>

</query>‘

,‘‘)=0;

select sysdate from dual;--查询系统时间格式

desc ctx_ddl;--显示 ctx_ddl结构

create table auction(category_id number primary key,title varchar2(20),bid_close date);

insert into auction values(1,‘Sony CD Player‘,to_date(‘2008-11-27‘,‘yyyy-mm-dd‘));

insert into auction values(2,‘Sony CD Player‘,to_date(‘2008-01-27‘,‘yyyy-mm-dd‘));

insert into auction values(3,‘Pioneer DVD Player‘,to_date(‘2008-10-27‘,‘yyyy-mm-dd‘));

insert into auction values(4,‘Sony CD Player‘,to_date(‘2008-02-27‘,‘yyyy-mm-dd‘));

insert into auction values(5,‘Bose Speaker‘,to_date(‘2008-04-27‘,‘yyyy-mm-dd‘));

insert into auction values(6,‘Tascam CD Bumer‘,to_date(‘2008-05-27‘,‘yyyy-mm-dd‘));

insert into auction values(7,‘Nikon digital camera‘,to_date(‘2008-06-27‘,‘yyyy-mm-dd‘));

insert into auction values(8,‘Canon digital camera‘,to_date(‘2008-07-27‘,‘yyyy-mm-dd‘));

begin

ctx_ddl.create_index_set(‘auction_iset‘);--创建索引集

ctx_ddl.add_index(‘auction_iset‘,‘bid_close‘);

end;

begin

ctx_ddl.create_stoplist(‘TAG_STOPLIST‘,‘BASIC_STOPLIST‘);--创建停用词表

ctx_ddl.add_stopword(‘TAG_STOPLIST‘,‘游戏‘);--添加停用词

ctx_ddl.add_stopword(‘TAG_STOPLIST‘,‘Player‘);

end;

select * from ctx_stoplists;--查询停词表

select * from ctx_stopwords;--查询停词表中的停用单词

select * from ctx_index_sets;--查看索引集视图

create index auction_titlex on auction(title) indextype is ctxsys.ctxcat parameters(‘index set auction_iset‘);--创建ctxcat索引

select * from auction where catsearch(title,‘camera‘,null)>0 order by bid_close desc;--与下一条等价

select * from auction where catsearch(title,‘camera‘,‘order by bid_close desc‘)>0;

select * from auction where catsearch(title,‘"Sony CD Player"‘,null)>0 and bid_close=to_date(‘2008-01-27‘,‘yyyy-mm-dd‘);

select * from auction where catsearch(title,‘"Sony CD Player"‘,‘bid_close=to_date(‘‘2008-01-27‘‘,‘‘yyyy-mm-dd‘‘)‘)>0;--注意时间的引号使用

select * from auction where catsearch(title,‘CD Player‘,null)>0;--显示含有‘‘CD Player‘‘的所有记录

select * from auction where catsearch(title,‘CD - Player‘,null)>0;--显示含有CD不含Player的所有记录

select * from auction where catsearch(title,‘CD- Player‘,null)>0;--显示含有CD Player的所有记录

select * from auction where catsearch(title,‘CD -Player‘,null)>0;--显示含有CD不含Player的所有记录

select * from auction where catsearch(title,‘CD-Player‘,null)>0;--显示含有CD Player的所有记录

select * from auction where catsearch(title,‘"CD Player"‘,null)>0;--显示含有CD Player的所有记录,不记中间空格的数目

select * from auction where catsearch(title,‘Sony Player‘,null)>0;--显示含有Sony Player的所有记录

select * from auction where catsearch(title,‘"Sony-Player"‘,null)>0;--没有记录

select * from auction where catsearch(title,‘"Sony Player"‘,null)>0;--没有记录

select * from auction where catsearch(title,‘Sony|Player‘,null)>0;--显示含有Sony或Player的所有记录

select * from auction where catsearch(title,‘S*y|P*‘,null)>0;--显示含有S和y或P的所有记录，不记大小写

select * from auction where catsearch(title,‘ $Sony ‘,null)>0;

select * from auction where catsearch(title,‘ABOUT(audio equipment)‘,null)>0;

select * from auction where catsearch(title,‘CD‘,null)>0;

select * from auction where catsearch(title,

  ‘<query>

    <textquery>CD</textquery>

   </query>‘,null)>0;

SELECT err_index_name, err_timestamp,err_text FROM ctx_user_index_errors;

create table t(id number,docs varchar2(1000));

insert into t values(1,‘IT IS A EXAMPLE FOR QUERY TEMPLETE.‘);

insert into t values(2,‘USING THE TEMPLETE CONTAIN OPERATION CAN BE PERFORM ON CTXCAT INDEX.‘);

insert into t values(3,‘AND THE CATSEARCH OPERATION CAN BE PERFORM ON CONTENT INDEX.‘);

commit;

drop index ind_docs;

create index ind_docs on t(docs) indextype is ctxsys.context;

select id from t where contains(docs,‘templete‘,1)>0;

select id from t where contains(docs,‘$USE‘,1)>0;

select id from t where contains(docs,

                               ‘<query>

                                  <textquery>TEMPLETE</textquery>

                                </query>‘)>0;

drop index ind_docs;

create index ind_docs on t(docs) indextype is ctxsys.ctxcat;

select id from t where catsearch(docs,‘using‘,null)>0;

select id from t where catsearch(docs,‘$USE‘,null)>0;

select id from t where catsearch(docs,

                  ‘<query>

                    <textquery grammar="CONTEXT">$use</textquery>

                    <score datatype="integer"/>

                   </query>‘,null)>0;

select * from t;

select * from t where catsearch(docs,‘USING‘,null)>0;

select * from t where contains(docs,‘$USE‘)>0;

select * from t where contains(docs,

                                 ‘<query>

                                    <textquery lang="ENGLISH" grammar="CATSEARCH">

                                      $USE

                                    </textquery>

                                    <score datatype="INTEGER"/>

                                  </query>‘)>0;

select * from t where catsearch(docs,

                                 ‘<query>

                                    <textquery grammar="contains">

                                      |$USE|

                                    </textquery>

                                  </query>‘,null)>0;

select * from t where catsearch(docs,

                                 ‘<query>

                                    <textquery grammar="CONTEXT">

                                      |$USE|

                                    </textquery>

                                  </query>‘,null)>0;

/*<TOAD_FILE_CHUNK>*/

select * from v$timer;

desc ctx_query;

--根据进程号获取该进程所在客户端的ip地址

create PROCEDURE dbo.SP_SPIDtoIP @SPID int

AS

-- SPID to MAC

-- lj

DECLARE @MAC as varchar(12)

SELECT @MAC = NET_ADDRESS FROM master..sysprocesses WHERE SPID = @SPID

-- MAC to IP

DECLARE @MACDisplay as varchar(18)

DECLARE @IP as varchar(15)

CREATE TABLE #temp (OUTPUT varchar(255) null)

SET NOCOUNT ON

INSERT INTO #temp EXEC master..xp_cmdshell ‘arp -a‘

if @@error<>0

begin

RAISERROR (‘The level for job_id:%d should be between %d and %d.‘, 16, 1)

--ROLLBACK TRANSACTION

end

SELECT @MACDisplay = LEFT(@MAC, 2) + ‘-‘ + SUBSTRING(@MAC, 3, 2) + ‘-‘ + SUBSTRING(@MAC, 5, 2) + ‘-‘ + SUBSTRING(@MAC, 7, 2) + ‘-‘ + SUBSTRING(@MAC, 9, 2) + ‘-‘ + SUBSTRING(@MAC, 11, 2) SELECT @IP = SUBSTRING(output, 3, 15) FROM #temp WHERE output LIKE ‘%‘ + @MACDisplay + ‘%‘

-- Resolve the IP

--DECLARE @CMD as varchar(100)

--select @CMD = ‘master..xp_cmdshell "ping -a ‘ + @IP + ‘"‘

--exec (@CMD)

DROP TABLE #temp

SET NOCOUNT OFF

GO

select * from auction where catsearch(title,‘<query>

                                                <textquery>CD|DVD|Speaker</textquery>

                                              </query‘,null)>0;

select * from auction where catsearch(title,‘<query>

                                                <textquery grammar="contains">|Sony|</textquery>

                                                <score datatype="float" />

                                              </query‘,null)>0;

select * from auction where catsearch(title,‘Sony‘,null)>0;

drop index ind_docs;

create index ind_docs on t(docs) indextype is ctxsys.ctxcat;

create index ind_docs on t(docs) indextype is ctxsys.context;

create or replace procedure T_OPTIMIZE_Index IS

begin

ctxsys.ctx_ddl.OPTIMIZE_INDEX(‘ind_docs‘,‘FULL‘,null,null,null);

exception

when no_data_found then

   null;

when others then

  -- Consider logging the error and then re-raise

       RAISE;

end T_OPTIMIZE_Index;

/                                 

CREATE OR REPLACE PROCEDURE T_SYNC_Index IS

BEGIN

   ctx_ddl.sync_index(‘ind_docs‘);

   EXCEPTION

     WHEN NO_DATA_FOUND THEN

       NULL;

     WHEN OTHERS THEN

       -- Consider logging the error and then re-raise

       RAISE;

END T_SYNC_Index;

/

Oracle Text(全文索引),布布扣,bubuko.com

Oracle Text(全文索引)