OracleText(全文检索)

Oracle Text(全文检索)

查看相关的信息 * from nls_database_parameters
1、简单应用
1.1如果要使用全文检索，当前ORACLE用户必须具有CTXAPP角色

--创建一个用户
--create user textsearch identified by textsearch;
/**
赋予用户三个角色，其中有一个为CTXAPP角色，
以便该用户可以使用与全文检索相关的PROCEDURE
*/
grant connect,resource,ctxapp to textsearch;
/

使用创建的用户登录
SQL> conn textsearch
输入口令: **********
已连接。

1.2创建要进行全文检索的数据表,准备数据
--drop table textdemo;
create table textdemo(
id number not null primary key,
book_author varchar2(20),--作者
publish_time date,--发布日期
title varchar2(400),--标题
book_abstract varchar2(2000),--摘要
path varchar2(200)--路径
);
commit;

insert into textdemo values(1,'宫琦峻',to_date('2008-10-07','yyyy-mm-dd'),'移动城堡','故事发生在19世纪末的欧洲，善良可爱的苏菲被恶毒的女巫施下魔咒，从18岁的女孩变成90岁的婆婆，孤单无助的她无意中走入镇外的移动城堡，据说它的主人哈尔以吸取女孩的灵魂为乐，但是事情并没有人们传说的那么可怕，性情古怪的哈尔居然收留了苏菲，两个人在四脚的移动城堡中开始了奇妙的共同生活，一段交织了爱与痛、乐与悲的爱情故事在战火中悄悄展开','E:textsearchmoveingcastle.doc');

insert into textdemo values(2,'莫·贝克曼贝托夫',to_date('2008-10-07','yyyy-mm-dd'),'子弹转弯','这部由俄罗斯导演提莫·贝克曼贝托夫执导的影片自6月末在北美上映以来，已经在全球取得了超过3亿美元的票房收入。在亚洲上映后也先后拿下日本、韩国等地的票房冠军宝座。虽然不少网友在此之前也相继通过各种渠道接触到本片，但相信影片凭着在大银幕上呈现出的超酷的视听效果，依然能够吸引大量影迷前往影院捧场。','E:textsearch.pdf');

insert into textdemo values(3,'袁泉',to_date('2008-10-07','yyyy-mm-dd'),'主演吴彦祖和袁泉现身','电影《如梦》在上海同乐坊拍摄，主演吴彦祖和袁泉现身。由于是深夜拍摄，所以周围并没有过多的fans注意到，给了剧组一个很清净的拍摄环境，站在街头的袁泉低着头，在寒冷的夜里看上去还真有些像女鬼，令人毛骨悚然。','E:textsearchdream.txt');

commit;

1.3在摘要字段上创建索引

/*
*创建索引，使用默认的参数
*/
--drop index demo_abstract;
create index demo_abstract on textdemo(book_abstract)
indextype is ctxsys.context
--parameters('datastore ctxsys.default_datastore filter ctxsys.auto_filter ')
;
commit;

(1) 建表并装载文本。

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

SELECT * FROM CTX_PREFERENCES;
(3) SQL查询。

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

授权
执行全文的用户必须具有 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;
然后，执行查询，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());

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

同步sync
将新的term保存到I表。

create or replace procedure sync isbeginexecute 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表删除。

declarev_job number;beginDbms_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语句查看此表的内容。

说明
(1) 本文是在Oracle 9i和10g环境下完全实现Oracle的全文检索，包括建立表和索引，进行同步和优化；

(2) 进行全文检索的SQL语句是"SELECT ID FROM DOCS WHERE CONTAINS(TEXT,'%FIRST%')>0"；

(3) 其中，">0"是有效的Oracle SQL所必需的，因为，Oracle SQL不支持函数的布尔返回值；

(4) 其中，"CONTAINS(TEXT,'%FIRST%')>0"，在Oracle 9i和10g与11g下有所不同；

(5) 最近做项目从Oracle 10g改成11g，在进行全文检索时，Oracle 10g下的代码，在11g下检索不到结果；

(6) 初步认为，Oracle 9i和10g与11g的区别是，在9i和10g下，如果不使用“%”，则是精确检索，否则是模糊检索。而在11g下，则完全不用“%”；

(7) 另外，在9i和10g下，可以使用如：CONTAINS(TEXT,'%FIRST% AND %second%')>0，进行全文检索，但在11g下，是不可以的，要分开写，如：

CONTAINS(TEXT,'%FIRST%')>0 AND CONTAINS(TEXT,'%second%')>0;
(8) 感觉11g下的全文检索更好