MySQL, Oracle Abc Wiki

Oracle text 概要

全文检索技术是智能信息管理的关键技术之一，Oracle Text作为Oracle9i的一个组件，提供了强大的全文检索功能，用Oracle9i做后台数据库，就可以充分利用其全文检索技术，构建复杂的大型文档管理系统。本文主要介绍了Oracle Text的体系结构及其使用。

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工具来构建基于文本的新的开发工具或对现有应用程序进行扩展。应用程序开发人员可以在任何使用文本的Oracle数据库应用程序中充分利用Oracle Text搜索，应用范围可以是现有应用程序中可搜索的注释字段，也可是实现涉及多种文档格式和复杂搜索标准的大型文档管理系统。Oracle Text支持Oracle数据库所支持的大多数语言的基本全文搜索功能。本文将介绍如何使用Oracle9i的全文检索技术来为自己的应用提供一个优秀的解决方案。
　

Oracle Text的体系架构

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

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

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

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

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

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

Oracle Text 示例I ORACLE全文索引之配置全文检索环境

Reference:
http://space6212.itpub.net/post/12157/234645

一、---— 安装全文检索组件-----

1、建立ctxsys用户需要的表空间（如果需要用已存在的表空间，这一步可以忽略）

以sys用户执行
SQL> create tablespace ts_text datafile 'E:ORACLEORADATAORACLE9Its_text01.dbf' size 100m;

Tablespace created

2、建立sysctx用户
以sys用户执行
SQL>@?/ctx/admin/dr0csys
需要输入输入三个参数，分别设置ctxsys用户的密码、表空间名称、临时表空间名称
SQL> @E:\oracle\ora92\ctx\admin\dr0csys
…creating user CTXSYS
…creating role CTXAPP

3、安装全文检索组件
以ctxsys用户执行
SQL>@E:\oracle\ora92\ctx\admin\dr0inst
需要一个输入参数：E:\oracle\ora92\ctx\lib\libctxx9.so
…….
… creating objects
Removing old object definitions…
Creating new object definitions…
…creating default preferences
Create default preferences
System Parameters

4、设置一些必要的默认值
以ctxsys用户执行
SQL>@E:\oracle\ora92\ctx\admin\defaults\drdefus ctxsys@
oracle9i> @E:\oracle\ora92\ctx\admin\defaults\drdefus
Creating lexer preference…
Creating wordlist preference…
Creating stoplist…
Creating default policy…

二、使用全文检索
1、授权
以sys登录
grant ctxapp to suk;
以ctxsys登录
grant execute on ctx_ddl to suk;

2、建立索引
以suk连接
exec ctx_ddl.create_preference('suk_lexer','CHINESE_LEXER');
其中：第一个参数可以随便命名，第二个参数必须是oracle规定的几个值之一。
第二个参数可以是CHINESE_LEXER、chinese_vgram_lexer；CHINESE_LEXER建索引时间长、但查询速度、查询准确度都比chinese_vgram_lexer高
建立后可以用如下方式查询：
select * FROM CTX_PREFERENCES;
create index idx_item_sku on item ( sku ) indextype is ctxsys.context parameters('lexer suk_lexer');
parameters括号中都第一个值lexer是固定的，第二个值是上一步建立的preference名称

—查询数据
select SKU from ITEM
where ITEM_TYPE='p'
and contains(SKU,'理光')>0
and contains(SKU,'Caplio')>0
and contains(SKU,'R1v')>0;

3、定期同步索引（同步索引为增量同步）
{{
—context这种类型的索引需要手工同步
SQL> create or replace procedure souchang_sync_index as
2 begin
3 ctx_ddl.sync_index('idx_item_sku');
4 end;
5 /

Procedure created.

Elapsed: 00:00:00.08
SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'souchang_sync_index();',
3 SYSDATE, 'SYSDATE + (1/24/4)');
4 commit;
5 END;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27
}}

4、定期优化索引
{{
SQL> create or replace procedure souchang_optimize_index as
2 begin
3 ctx_ddl.optimize_index('idx_item_sku','FULL');
4 end;
5 /

SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'souchang_optimize_index();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
Procedure created.
}}
全文检索的缺点是不能实时保证查询的正确性，所以要合理地定期同步索引。

Oracle Text 示例II

这里先给出一个简单示例说利用Oracle Text实现全文检索的方法与步骤，在后面在进行具体的说明。Orcale9i提供了Oracle Text Manager可以简化许多工作，所有在Oracle Text Manager中完成的工作，都可以在通过PL/SQL来实现。要使用Oracle Text，必须具有CTXAPP角色或者是CTXSYS用户。Oracle Text为系统管理员提供CTXSYS用户，为应用程序开发人员提供CTXAPP角色。

CTXSYS用户可执行以下任务：启动Oracle Text服务器，执行CTXAPP角色的所有任务。
具有CTXAPP角色的用户可执行以下任务： 创建索引，管理 Oracle Text 数据字典，包括创建和删除首选项，进行Oracle Text 查询，使用 Oracle Text PL/SQL程序包。

使用Oracle Text的步骤：

（1）创建表来保存某些文档。该示例使用一个主关键字列来标识每个文档，使用一个小的VARCHAR2列来保存每个文档。

CREATE TABLE docs (id NUMBER PRIMARY KEY, text VACHAR2(80));

（2）将两个示例文档置入该表：

INSERT INTO docs VALUES (1，'the first doc')；
INSERT INTO docs VALUES (2，'the second doc')；
COMMIT；

（3）使用Oracle Text Manager来创建和修改首选项，首选项将与索引相关联。

（4）使用Oracle Text Manager创建文本索引。另外，可以输入以下使用默认首选项的 SQL 语句：

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

（5）使用 CONTAINS 函数，发出基于内容的文档查询。例如：

SELECT id FROM docs WHERE CONTAINS (text, 'first') > 0;

这将在文本列包含单词 first (即文档1) 的 docs 中查找所有行。语句中的>0部分是有效的Oracle SQL所必需的，Oracle SQL不支持函数的布尔返回值。

以上只是一个简单的示例，旨在给出使用Oracle Text建立全文索引的完整步骤，归纳起来如下：

（1）建表并装载文本（包含带有需要检索的文本字段）

（2）配置索引

（3）建立索引

（4）发出查询

（5）索引维护：同步与优化（将在后面介绍）

Oracle Text文本装载

要实现文本的全文检索首先必须把正确的文本加载到数据库表中，默认的建立索引行为要求将文档装载在文本列中，尽管可以用其它方式 (包括文件系统和 URL 形式)存储文档 (在"数据存储"选项进行设置)。默认情况下，系统应该将文档装载在文本列中。文本列可以是VARCHAR2、CLOB、BLOB、CHAR或BFILE。注意，只有在将Oracle7系统移植到Oracle8的情况下才支持用LONG和LONG RAW 这两个相反的列类型存储文本。不能为列类型NCLOB、DATE和NUMBER建立索引。

关于文档格式，因为系统能为包括HTML、PDF、Microsoft Word和纯文本在内的大多数文档格式建立索引，可以将其中的任何文档类型装载到文本列中(在"过滤器"选项中设置)。有关所支持的文档格式的详细信息，可以参阅Oracle Text User's Guide and Reference 中的附录"Supported Filter Formats"。

装载方法主要有以下几种：

（1）SQL INSERT 语句

（2）ctxload 可执行文件

（3）SQL*Loader

（4）从 BFILE 中装载 LOB 的 DBMS_LOB.LOADFROMFILE() PL/SQL 过程

（5）Oracle Call Interface

Oracle text 为文本建立索引

文本装入文本列后，就可以创建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 (包含多种语言中的非索引字的多语言非索引字表)。

Oracle text查询

建立了索引，就可以使用 SELECT 语句中的 CONTAINS 运算符发出文本查询。使用 CONTAINS 可以进行两种查询：单词查询和ABOUT查询。

词查询示例

词查询是对输入到 CONTAINS 运算符中单引号间的精确单词或短语的查询。在以下示例中，我们将查找文本列中包含 oracle 一词的所有文档。每行的分值由使用标签 1 的 SCORE 运算符选定：

SELECT SCORE(1) title from news WHERE CONTAINS(text, 'oracle', 1) > 0;

在查询表达式中，可以使用 AND 和 OR 等文本运算符来获取不同结果。还可以将结构性谓词添加到 WHERE 子句中。可以使用 count(*)、CTX_QUERY.COUNT_HITS 或 CTX_QUERY.EXPLAIN 来计算查询的命中 (匹配) 数目。

ABOUT查询示例

在所有语言中，ABOUT查询增加了某查询所返回的相关文档的数目。在英语中，ABOUT 查询可以使用索引的主题词组件，该组件在默认情况下创建。这样，运算符将根据查询的概念返回文档，而不是仅依据所指定的精确单词或短语。例如，以下查询将查找文本列中关于主题 politics 的所有文档，而不是仅包含 politics 一词的文档：

SELECT SCORE(1) title from news WHERE CONTAINS(text, 'about(politics)', 1) > 0;

Oracle text - 显示满足查询条件的文档

通常，通过使用Oracle Text查询应用程序，用户可查看查询所返回的文档。用户从命中列表中选择一个文档，然后应用程序以某种形式显示该文档。通过Oracle Text，可以用不同的方式再现文档。例如，可以通过突出显示查询词来显示文档。突出显示的查询词可以是相关词查询中的词，也可以是英文 ABOUT 查询中的主题词。

　　以下是关于输出效果和用于每个输出效果的过程的信息：

　　突出显示的文档，纯文本格式版本(CTX_DOC.MARKUP)

　　突出显示的文档，HTML版本(CTX_DOC.MARKUP)

　　突出显示纯文本格式版本的偏移量信息(CTX_DOC.HIGHLIGHT)

　　突出显示HTML 版本的偏移量信息(CTX_DOC.HIGHLIGHT)

　　纯文本格式版本，无突出显示(CTX_DOC.FILTER)

　　HTML版本文档，无突出显示(CTX_DOC.FILTER)

Oracle text索引维护

索引建好后，如果表中的数据发生变化，比如增加或修改了记录，怎么办？由于对表所发生的任何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$ myindex $I where rownum<=20;

查询结果在此省略。可以看到，该表中保存的其实就是Oracle 分析你的文档后，生成的term记录

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

　　同步（sync）：将新的term 保存到I表；

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

Oracle提供了一个所谓的ctx server来做这个同步和优化的工作，只需要在后台运行这个进程，它会监视数据的变化，及时进行同步。另外，也可以用以下的job来完成（该job要建在和表同一个用户下）：

create or replace procedure sync
is
begin
execute immediate
'alter index myindex rebuild online' ||
' parameters ( ''sync'' )' ;
execute immediate
'alter index myindex rebuild online' ||
' parameters ( ''optimize full maxtime unlimited'' )' ;
end sync;
/
 
Set ServerOutput on
declare
v_job number;
begin
Dbms_Job.Submit
(
job => v_job,
what => 'sync;',
next_date => sysdate, /* default */
interval => 'sysdate + 1/720' /* = 1 day / ( 24 hrs * 30 min) = 2 mins */
);
Dbms_Job.Run ( v_job );
Dbms_Output.Put_Line ( 'Submitted as job # ' || to_char ( v_job ) );
end;
/

job的SYSDATE + (1/720)是指每隔2分钟同步一次。具体的时间间隔，可以根据自己的应用的需要而定。

Oracle text小结

文本对于各种规模的公司、机构组织来说，都是包含众多丰富信息的最有效载体，Oracle Text的推出，标志着Oracle提供了一套崭新的技术，可以便捷安全的用于管理企业的文本信息。Oracle Text使应用程序开发者可以透明地将全文检索能力加入到基于SQL的应用程序中，Oracle Text也是其他Oracle产品的核心组件，比如Oracle9iAS Portal，Oracle eBusiness Suite，Oracle Ultra Search和Oracle Internet File System等。灵活运用Oracle Text提供的全文检索技术，就可以使自己的应用具备强大的全文检索能力。

How to install or uninstall (remove) Context on Oracle 9i/10g?

The steps of context install on Oracle 10g.

-- 1, create tablespace drsys
create tablespace drsys datafile '/u01/oradata/ORCL/drsys_01.dbf' size 2m autoextend on next 2m;
-- 2, run catctx.sql as sysdba
conn / as sysdba
@?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock 
-- 3, run drdefus as ctxsys
alter user ctxsys identified by ctxsys;
conn ctxsys/ctxsys
@?/ctx/admin/defaults/drdefus.sql
-- 4, grant exec on ctxsys.ctx_ddl
grant execute on ctxsys.ctx_ddl to public

The steps of context uninstall (remove) on Oracle 10g.

-- run as sys  
@?/ctx/admin/catnoctx.sql

The steps of context install on Oracle 9i.

-- 1, create tablespace drsys
create tablespace drsys datafile '/u01/oradata/ORCL/drsys_01.dbf' size 2m autoextend on next 2m;
-- 2, run dr0csys as sysdba
@?/ctx/admin/dr0csys ctxsys drsys temp01
-- 3, conn to ctxsys user
alter user ctxsys identified by ctxsys;
conn ctxsys/ctxsys
-- 4, run dr0inst
@?/ctx/admin/dr0inst ?/ctx/lib/libctxx9.so
-- 5, run drdefus
@?/ctx/admin/defaults/drdefus.sql
-- 6, grant ctxsys.ctx_ddl
grant execute on ctxsys.ctx_ddl to public;

The steps of context uninstall (remove) on Oracle 9i.

-- run as ctxsys
$ORACLE_HOME/ctx/admin/dr0drop.sql
-- run as sys
$ORACLE_HOME/ctx/admin/dr0dsys.sql

Verification of the context installation.

-- check dba_registry
col version format a14
col comp_name format a30
select COMP_NAME,VERSION,STATUS from dba_registry;
 
-- Create a sample table
create table  cr_ctxdemo (col1 number not null primary key, col2 varchar2(100));
insert into cr_ctxdemo values (1,'Oracle A B C');
commit;
create index idx_cr_ctxdemo_col2 on cr_ctxdemo(col2) indextype is ctxsys.context;
select col1 from cr_ctxdemo where contains(col2,'A or C') > 0;
set autot trace exp
select col1 from cr_ctxdemo where contains (col2,'A') > 0;
set autot off
drop table cr_ctxdemo;

Context index can be considered as a text search tool.

Use The Text Index

begin ctx_ddl.create_preference('smshis_fullidx','chinese_vgram_lexer'); end;  
 
begin   
    ctx_ddl.drop_preference('smshis_fullidx');   
end;
 
begin ctx_ddl.create_preference('smshis_fullidx','BASIC_LEXER'); end; 
 
drop index smshis_text_idx_mobile;  
 
create Index smshis_text_idx_mobile On smshis(mobile) Indextype Is  
ctxsys.context parameters('lexer smshis_fullidx');  
 
select mobile mobileNum,status,to_char(startTime,'yyyymmddhh24miss') sendTime,contents content, alarmType   
from smshis where  mobile LIKE '%13911905263%';  
 
select mobile mobileNum,status,to_char(startTime,'yyyymmddhh24miss') sendTime,contents content, alarmType   
from smshis  
where contains (mobile,'13911905263',1) >0;

Question

1)It will not correct the result?
2)It must sync the text index by running job.

Move Index Tablespace

Moving an index from one tablespace to another tablespace is very easy task. It can be accomplished by using rebuild option:

ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE;
ALTER INDEX index_name REBUILD TABLESPACE NEW_TABLESPACE ONLINE;

But trying to move a domain index (such as Oracle Text Index) is not so simple. If you follow the above way it will cause error:

ALTER INDEX my_text_index REBUILD TABLESPACE NEW_TABLESPACE;
ORA-29871: invalid alter option for a domain index

You may ask What is the reson behind that error and how to resolve it? In fact Domain index is a set of other objects. Oracle Text CONTEXT index is set of tables:

• DR$[index_name]$I
• DR$[index_name]$K
• DR$[index_name]$N
• DR$[index_name]$R

Unfortunately to move context index you have to drop and recreate that text index. But first you need to specify storage parameters:

begin
ctx_ddl.create_preference('TEXT_INDEX_STORE', 'BASIC_STORAGE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'K_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'R_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'N_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'I_INDEX_CLAUSE',
'tablespace NEW_TABLESPACE COMPRESS 2');
ctx_ddl.set_attribute('TEXT_INDEX_STORE', 'P_TABLE_CLAUSE',
'tablespace NEW_TABLESPACE');
end;
/

and then just drop the previously created index and recreate that index with changed parameters

create index MY_TEXT_I on MY_TAB(text_column)
indextype is ctxsys.context parameters('storage TEXT_INDEX_STORE');

Reference:
http://www.hudong.com/wiki/Oracle+text
http://www.dbatools.net/experience/oracle_context_index_install.html
http://microjoey.iteye.com/blog/1033218
http://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:2200894550208
http://forums.oracle.com/forums/thread.jspa?threadID=882001
http://www.oracle-base.com/articles/9i/FullTextIndexingUsingOracleText9i.php
http://blog.csdn.net/hunterdiary/archive/2009/04/02/4043099.aspx