Oracle9i中全文检索的创建与使用
作者：玉面飞龙

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


本篇主要介绍Oracle Text的基本结构和简单应用。


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

Oracle Text的应用领域有很多：




l         搜索文本 ：需要快捷有效搜索文本数据的应用程序
l         管理多种文档：允许搜索各种混和文档格式的应用程序,包括ord,excel,lotus等
l         从多种数据源中检索文本：不仅来自Oracle数据库中的文本数据,而且可以来自Internet和文件系统的文本数据
l         搜索XML应用程序
 
1.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
/
 
CREATE INDEX mytext_idx
ON mytext( thetext )
INDEXTYPE is CTXSYS.CONTEXT
/
 
SELECT id
  FROM mytext
 WHERE contains (thetext, 'near((Oracle,Corporation),10)') > 0
／
 
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;
 
 
1.2设置
 
首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能。你必须修改数据库以安装这项功能。
 
还可以检查服务器是否有对PLSExtProc服务的监听。




lsnrctl status  
should give status
          LSNRCTL for Solaris: Version
          8.1.5.0.0 - Production on 31-MAR-99    18:57:49
 
          (c) Copyright 1998 Oracle Corporation. All rights reserved.
 
          Connecting to
          (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC0)))
          STATUS of the LISTENER
          ------------------------
 
          Alias LISTENER
          Version TNSLSNR for Solaris: Version 8.1.5.0.0 - Production
          Start Date 30-MAR-99 15:53:06
          Uptime 1 days 3 hr. 4 min. 42 sec
          Trace Level off
          Security OFF
          SNMP OFF
          Listener Parameter File
          /private7/Oracle/Oracle_home/network/admin/listener.ora
          Listener Log File
          /private7/Oracle/Oracle_home/network/log/listener.log
          Services Summary...
          PLSExtProc has 1 service handler(s)
          oco815 has 3 service handler(s)
          The command completed successfully  
 
Oracle 是通过所谓的‘外部调用功能’(external procedure)来实现intermedia的。
 

B. Create a user/table/index/query thus: As SYS or SYSTEM:

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

CREATE USER ctxtest IDENTIFIED BY ctxtest;
GRANT CONNECT, RESOURCE, ctxapp TO ctxtest;      

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

Do any other grants, quotas, tablespace etc. for the new user. As CTXTEST:

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

CREATE TABLE quick  (
             quick_id NUMBER PRIMARY KEY,              
             text VARCHAR(80));




INSERT INTO  quick
             (quick_id, text)
     VALUES  (1, 'The cat sat on the mat');


INSERT INTO  quick
             (quick_id, text)
     VALUES  (2, 'The quick brown fox jumped over the lazy dog');

COMMIT ;       
    
CREATE INDEX quick_text

          ON quick ( text )

INDEXTYPE IS ctxsys.CONTEXT;  
----------------------------------------------------------

 
             
此时如果监听没有配置好创建索引将会失败。
 
CREATE INDEX quick_text ON quick ( text )；




* 
           ERROR at line 1: 
           ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine 
           ORA-20000: ConText error: 
           DRG-50704: Net8 listener is not running or cannot start external procedures 
           ORA-28575: unable to open RPC connection to external procedure agent 
           ORA-06512: at "CTXSYS.DRUE", line 122 
           ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 34 
           ORA-06512: at line 1 
      

如果一切正常，你将得到： 

           Index created. 
    

现在尝试写一些查询： 

           SQL> SELECT quick_id　FROM quick　 WHERE contains (text, 'cat') > 0;   
     
   QUICK_ID 
           ---------- 
           1 
                
           SQL> SELECT quick_id FROM quick WHERE contains(text, 'fox') > 0; 
 
           QUICK_ID 
           ---------- 
           2
 
 

2建立索引

 

2．1索引简介

 

利用Oracle Text对文档集合进行检索的时候，你必须先在你的文本列上建立索引。索引将文本打碎分成很多记号（token），这些记号通常是用空格分开的一个个单词。
 
Oracle Text应用的实现实际上就是一个 数据装载—> 索引数据—>执行检索 的一个过程。




 
2．1．1索引类型和限制
 
建立的Oracle Text索引被称为域索引（domain index），包括4种索引类型： 




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

索引类型

描述

查询操作符

CONTEXT

用于对含有大量连续文本数据进行检索。支持word、html、xml、text等很多数据格式。支持中文字符集，支持分区索引，唯一支持并行创建索引（Parallel indexing）的索引类型。

对表进行DML操作后，并不会自动同步索引。需要手工同步索引

CONTAINS

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

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

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 ConText的介绍。




2．1．2权限和临时表空间

权限

用户不需要具有CTXAPP脚色便可以建立Oracle Text索引。你只需要对某一文本列有创建B树索引的权限。查询用户，索引用户，表拥有者可以是不同的用户。

临时表空间要求

建立Oracle Text索引需要消耗CTXSYS用户默认的临时表空间空间。如果空间不够的话将导致 ORA-01652 错误。你可以扩展CTXSYS的临时表空间，而不是发出命令的用户默认的临时表空间。

对于索引全英文的文本列来说，需要临时表空间大小通常是其文本数据量的50%-200%不等。而对索引包含中文文本列来说需要的表空间会更多。

2.2 CONTEXT 索引
 
2.2.1 CONTEXT 索引的结构

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表最重要，可以查询一下该表，看看有什么内容：

SQL> CREATE TABLE mytext (text VARCHAR2(100));
SQL> INSERT INTO mytext

     VALUES ('I Love www.itpub.net');

SQL> COMMIT;

SQL> CREATE INDEX mytext_idx ON mytext(text) INDEXTYPE IS ctxsys.CONTEXT;

SQL> SELECT token_text, token_count FROM dr$mytext_idx$i;

TOKEN_TEXT                           TOKEN_COUNT

I                                             1                     

ITPUB                                         1                               

LOVE                                          1                        

NET                                           1                             

WWW                                           1

注意TOKEN_TEXT里面字符全部是大写的，默认情况下全文索引是不区分大小写的.                                                                  

SQL> DESC dr$mytext_idx$i;

名称                                      是否为空  类型

TOKEN_TEXT                                NOT NULL VARCHAR2(64)

TOKEN_TYPE                                NOT NULL NUMBER(3)

TOKEN_FIRST                               NOT NULL NUMBER(10)

TOKEN_LAST                                NOT NULL NUMBER(10)

TOKEN_COUNT                               NOT NULL NUMBER(10)

TOKEN_INFO                                         BLOB

 

可以看到，该表中保存的其实就是Oracle 分析你的文档后，生成的token记录在这里，包括token出现的位置、次数、hash值等。

2.2.2建立索引

建立索引的语法如下。索引建立好后可以用ConTains进行查询。
 
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）。根据不同的参数构建索引，可以应用于很多实际环境。

类别

描述

Datastore

从哪里得到数据？

Filter

将数据转换成文本

Lexer

正在索引什么语言？

Wordlist

应该如何展开茎干和模糊查询

Storage

如何存储索引 

Stop List

什么单词或者主题不被索引？

Section Group

允许在区段内查询吗？如何定义文档区段。这把文档转换成普通文本

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

Default CONTEXT Index Example

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

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

如上命令建立了一个默认参数的CONTEXT索引myindex.系统默认：

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

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

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

4.        允许模糊查询。

 
2.3索引参数
 
2.3.1DataStore




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

Datastore Type

Use When

DIRECT_DATASTORE

Data is stored internally in the text column. Each row is indexed as a single document.

MULTI_COLUMN_DATASTORE

Data is stored in a text table in more than one column. Columns are concatenated to create a virtual document, one per row.

DETAIL_DATASTORE

Data is stored internally in the text column. Document consists of one or more rows stored in a text column in a detail table, with header information stored in a master table.

FILE_DATASTORE

Data is stored externally in operating system files. Filenames are stored in the text column, one per row.

NESTED_DATASTORE

Data is stored in a nested table.

URL_DATASTORE

Data is stored externally in files located on an intranet or the Internet. Uniform Resource Locators (URLs) are stored in the text column.

USER_DATASTORE

Documents are synthesized at index time by a user-defined stored procedure.

DataStore参数应用.

问：我有一张表记录着雇员的名称和住址，我想在这两列中查找某个单词是否存在这两个列之一。

答:方法1,在雇员名称和住址上建立两个ConText索引。查询:

SELECT *

  FROM emp

 WHERE contains (ename, 'biti') > 0

    OR contains (address, 'biti') > 0;

方法2, 定制

n      Only CTXSYS is allowed to create preferences for the MULTI_COLUMN_DATASTORE type. Any other user who attempts to create a MULTI_COLUMN_DATASTORE preference receives an error.so it run on ctxsys schema
 
CREATE TABLE mc(id NUMBER PRIMARY KEY, NAME VARCHAR2(10), address VARCHAR2(80))
/
 
INSERT INTO mc
     VALUES (1, 'John Smith', '123 Main Street biti');
EXEC ctx_ddl.create_preference('mymds', 'MULTI_COLUMN_DATASTORE');
EXEC ctx_ddl.set_attibute('mymds', 'columns', 'name, address');
CREATE INDEX mc_idx ON mc(NAME) INDEXTYPE IS ctxsys.CONTEXT PARAMETERS('datastore mymds')
/
 
SELECT *
  FROM mc
 WHERE contains (name, 'biti') > 0;
 
 
问：如何实现对主/从表的全文检索？
 
答：使用类Detail_DataStore。这个类经过设计，供主/从表格使用，其中大量文本存储在从表表格列中。在进行索引之前把多个从表行联接为一个文档，使用外来关键码识别行，外键关系必须已逻辑的方式存在，但不必作为数据库约束条件。注意在不更改主表的情况下更改从表，不会更新索引。解决这个的办法是更新主列的值，触发索引的重新构建，或者手工设置和重新构建索引。否则，应该存在未使用的列，来保持SQL语法的完整性。如下面例子中的purchase_order表的line_item_body列。




------------------BEGIN---------------------
SET echo on
DROP TABLE purchase_order;
CREATE TABLE purchase_order
( id                  NUMBER PRIMARY KEY,
  description         VARCHAR2(100),
  line_item_body      CHAR(1)
)
/
DROP TABLE line_item;
CREATE TABLE line_item
( po_id            NUMBER,
  po_sequence      NUMBER,
  line_item_detail VARCHAR2(1000)
)
/
INSERT INTO purchase_order
            (id, description)
     VALUES (1, 'Many Office Items')
/
INSERT INTO line_item
            (po_id, po_sequence, line_item_detail)
     VALUES (1, 1, 'Paperclips to be used for many reports')
/
INSERT INTO line_item
            (po_id, po_sequence, line_item_detail)
     VALUES (1, 2, 'Some more Oracle letterhead')
/
INSERT INTO line_item
            (po_id, po_sequence, line_item_detail)
     VALUES (1, 3, 'Optical mouse')
/
COMMIT ;
BEGIN
   ctx_ddl.create_preference ('po_pref', 'DETAIL_DATASTORE');
   ctx_ddl.set_attribute ('po_pref', 'detail_table', 'line_item');
   ctx_ddl.set_attribute ('po_pref', 'detail_key', 'po_id');
   ctx_ddl.set_attribute ('po_pref', 'detail_lineno', 'po_sequence');
   ctx_ddl.set_attribute ('po_pref', 'detail_text', 'line_item_detail');
END;
/
DROP INDEX po_index;
CREATE INDEX po_index ON purchase_order( line_item_body )
INDEXTYPE IS ctxsys.CONTEXT
PARAMETERS( 'datastore po_pref' )
/
SELECT id
  FROM purchase_order
 WHERE contains (line_item_body, 'Oracle') > 0
/

-------------------END----------------------




2.3.2 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）的概念。

 
2.3.2 Section Groups区分组


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


下面的例子中显示如何处理HTML文档。




------------------BEGIN---------------------


SET echo on
DROP TABLE my_html_docs;
CREATE TABLE my_html_docs( id NUMBER PRIMARY KEY,  html_text VARCHAR2(4000))
/




INSERT INTO my_html_docs
            (id,
             html_text)
     VALUES (1,
             '<html><title>Oracle Technology</title><body>This is about the wonderful marvels of 8i and 9i</body></html>')
/
 
COMMIT ;
 
CREATE INDEX my_html_idx ON my_html_docs( html_text )INDEXTYPE IS ctxsys.CONTEXT
/




-- 默认使用NULL_SECTION_GROUP 不对文档做任何数据流处理




SELECT id
  FROM my_html_docs
 WHERE contains (html_text, 'Oracle') > 0

/
-- 可以检索到区段界限之间的文本

SELECT id

  FROM my_html_docs

 WHERE contains (html_text, 'title') > 0

/

SELECT id

  FROM my_html_docs

 WHERE contains (html_text, 'html') > 0
/            

/* 也可以检索到 区段界限内的文本。但由于HTML文件中的一些标记如<b> <body> <A href=…..>等对我们没有提供有用的信息。而且在索引中还浪费了空间和CPU。因此HTML标记不应该被索引 */

--我们可以定制我们自己的区段标记。可以查询在某个区段出现的单词

BEGIN

   ctx_ddl.create_section_group ('my_section_group', 'BASIC_SECTION_GROUP');

   ctx_ddl.add_field_section (

      group_name=> 'my_section_group',

      section_name=> 'Title',

      tag   => 'title',

      visible=> FALSE

   );

END;

/


DROP INDEX my_html_idx;

CREATE INDEX my_html_idx ON my_html_docs( html_text )

INDEXTYPE IS ctxsys.CONTEXT

PARAMETERS( 'section group my_section_group' )

/

SELECT id

  FROM my_html_docs

 WHERE contains (html_text, 'Oracle within title') > 0;



------------------END---------------------




 

下面是如何检索XML文档的例子

InterMedia Text 支持索引XML文档通过指定区段组。区段组就是XML文档中预先定义的节点.你可以用WithIn在指定检索某个节点，提高了检索的准确性。

 

1) 首先,创建一个表来存储我们的XML文档:

CREATE TABLE employee_xml(

             id NUMBER PRIMARY KEY,

             xmldoc CLOB )

/

2) 插入一个简单的文档(the DTD is not required)


INSERT INTO employee_xml

VALUES (1,

 '<?xml version="1.0"?>

<!DOCTYPE employee [

<!ELEMENT employee (Name, Dept, Title)>

<!ELEMENT Name (#PCDATA)>

<!ELEMENT Dept (#PCDATA)>

<!ELEMENT Title (#PCDATA)>

]>

<employee>

<Name>Joel Kallman</Name>

<Dept>Oracle Service Industries Technology Group</Dept>

<Title>Technologist</Title>

</employee>');

 

3)创建一个叫'xmlgroup'的interMedia Text section group , 添加 Name和Dept tag到section group中。(Caution: in XML, tag names are case-sensitive, but

  tag names in section groups are case-insensitive)

BEGIN

   ctx_ddl.create_section_group ('xmlgroup', 'XML_SECTION_GROUP');

   ctx_ddl.add_zone_section ('xmlgroup', 'Name', 'Name');

   ctx_ddl.add_zone_section ('xmlgroup', 'Dept', 'Dept');

END;

 

4)Create our interMedia Text index, specifying the section  group we created above. 
Also, specify the null_filter, as the Inso filter is not required.

 

CREATE INDEX employee_xml_index

          ON employee_xml( xmldoc )

INDEXTYPE IS ctxsys.CONTEXT    PARAMETERS(

'filter ctxsys.null_filter section group xmlgroup' )

/

 

5) 现在,执行一个查询,搜寻特定Section中的Name:

   SELECT id

     FROM employee_xml

    WHERE contains (xmldoc, 'Joel within Name') > 0;

6)Only non-empty tags will be indexed, but not the tag names themselves.
 Thus, the following queries will return zero rows.

 

  SELECT id

    FROM employee_xml

   WHERE contains (xmldoc, 'title') > 0;

  SELECT id

    FROM employee_xml