Oracle_C# 实现 Oracle Text(全文检索)的一个简单例子

Oracle 全文检索

Oracle 全文检索主要针对 CLOB 或 XMLTYPE 类型的字段，当然 varchar2 也可以，XMLTYPE 本质上也是 CLOB。

本文是用 C# 实现 Oracle Text（Oracle 全文检索）的一个简单例子，旨在初步了解 Oracle 全文检索的大概步骤，你可以此基础上扩展，进行进一步的学习。Oracle 全文检索的步骤如下：

(1) 授权相应 Oracle 用户具有全文检索的权限；

(2) 建表并插入数据；

(3) 建立索引。如果想配置 Oracle 全文检索的索引，可以在建立索引前进行配置，如：改变词法分析器。可以用下面 SQL 语句查看 Oracle 全文检索的配置情况：

SELECT * FROM CTX_PREFERENCES; 

(4) 用 SQL 语句在全文检索中进行检索；

(5) 索引维护：同步与优化。

 

授权

执行全文的用户必须具有 CTXAPP角色 或 CTXSYS用户，以及 CTX_DDL包 执行权限。

(1) 用 SYS用户 授予 SCOTT 用户 CTXAPP 角色，命令如下：

GRANT CTXAPP TO SCOTT; 

(2) 用 CTXSYS 用户 给 SCOTT 用户 授权 CTX_DDL 包的执行权限，命令如下：

GRANT EXECUTE ON CTX_DLL TO SCOTT; 

 

创建表、插入记录和创建全文检索索引

以下 SQL 语句和 JOB 都在 SCOTT 用户下执行。

首先，执行下面 SQL，创建 DOCS 表，并插入两条记录，提交后创建索引 doc_index。

DROP TABLE DOCS;

CREATE TABLE DOCS (

id NUMBER PRIMARY KEY,

text VARCHAR2(80)

); 

INSERT INTO docs VALUES (1,'the first doc');

INSERT INTO docs VALUES (2,'the second doc');

COMMIT; 

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

 

用 SQL 语句在全文检索中进行检索

可以使用下面 SQL 语句进行检索：

SELECT ID FROM DOCS WHERE CONTAINS(TEXT,'%FIRST%')>0 

其中，">0" 是有效的 Oracle SQL 必需的，因为，Oracle SQL 不支持函数布尔返回值，即 "CONTAINS(TEXT,'%FIRST%')>0"。但是在 Oracle 9i 和 10g 与 11g 下有所不同。

若用 C# 执行，则如下：

string connStr="Data Source=ora9; uid=scott; pwd=tiger; unicode=true"; 

string sqlStr = "SELECT ID FROM DOCS WHERE CONTAINS(TEXT,'%FIRST%')>0";

OracleDataAdapter da = new OracleDataAdapter(sqlStr, connStr);

DataTable dt = new DataTable();

da.Fill(dt);

Response.Write(dt.Rows[0][0].ToString()); 

说明：

最近做项目从 Oracle 10g 改成 11g，在进行全文检索时，Oracle 10g 下的代码，在 11g 下检索不到结果。初步认为，Oracle 9i 和 10g 与 11g 的区别是，在 9i 和 10g 下，如果不使用“%”，则是精确检索，否则是模糊检索。而在 11g 下，则完全不用“%”。

另外，在 9i 和 10g 下，可以使用如下 SQL，进行检索：

CONTAINS(TEXT,'%FIRST% AND %second%')>0

但在 11g 下不可以，要分开写：

CONTAINS(TEXT,'%FIRST%')>0 AND CONTAINS(TEXT,'%second%')>0;

感觉 11g 下的全文检索更好。

 

同步和优化

当 DOCS 表发生变化（插入，删除）后，索引也必须能反应这个变化，这就需要对索引进行同步和优化。可以使用下面的 job 来完成 Oracle ctx 的同步和优化。

• 同步 sync

将新的 term 保存到 I 表。

create or replace procedure sync is

begin

execute immediate 'alter index doc_index rebuild online' ||

' parameters ( ''sync'' )';

execute immediate 'alter index doc_index rebuild online' ||

' parameters ( ''optimize full maxtime unlimited'' )';

end sync; 

• 优化

清除 I 表的垃圾，将已经被删除的 term 从 I 表删除。

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 );

end; 

其中，I 表是 dr$doc_index$i 表。用户建立索引后，Oracle 会自动创建四个表，dr$doc_index$i、dr$doc_index$k、dr$doc_index$n 和 dr$doc_index$r。可以用 SELECT 语句查看此表的内容。