- Oracle 全文检索
- 参考资料
- 修改记录
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 语句查看此表的内容。