Oracle建立全文索引详解
Oracle建立全文索引详解
1.全文检索和普通检索的区别
不使用Oracle text功能，当然也有很多方法可以在Oracle数据库中搜索文本，比如INSTR函数和LIKE操作：
SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') > 0;
SELECT * FROM mytext WHERE thetext LIKE '%Oracle%';
有很多时候，使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候。然而通过这些文本定位的方法将导致全表扫描，对资源来说消耗比较昂贵，而且实现的搜索功能也非常有限，因此对海量的文本数据进行搜索时，建议使用oralce提供的全文检索功能。
附：这里顺带记录一下INSTR和LIKE:
Oracle中，可以使用 Instr 函数对某个字符串进行判断，判断其是否含有指定的字符。其语法为：Instr(string, substring, position, occurrence)。
string：代表源字符串（写入字段则表示此字段的内容）。
substring：代表想从源字符串中查找的子串。
position：代表查找的开始位置，该参数可选的，默认为1。
occurrence：代表想从源字符中查找出第几次出现的substring，该参数也是可选的，默认为1。
position 的值为负数，那么代表从右往左进行查找。
instr和like的性能比较
其实从效率角度来看，谁能用到索引，谁的查询速度就会快。
like有时可以用到索引，例如：name like ‘李%’，而当下面的情况时索引会失效：name like ‘%李’。所以一般我们查找中文类似于‘%字符%’时，索引都会失效。与其他数据库不同的是，oracle支持函数索引。例如在name字段上建个instr索引，查询速度就比较快了，这也是为什么instr会比like效率高的原因。
注:instr(title,’手册’)>0 相当于like‘%手册%’
instr(title,’手册’)=0 相当于not like‘%手册%’
2.设置全文检索
步骤步骤一：检查和设置数据库角色
首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能（10G默认安装都有此用户和角色）。你必须修改数据库以安装这项功能。默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys的用户。
--设置词法分析器(lexer)
Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为 term)找出来，记录在一组以dr$开头的表中，同时记下该term出现的位置、次数、hash值等信息。检索时，Oracle从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核心，它决定了全文检索的效率。Oracle针对不同的语言提供了不同的lexer,而我们通常能用到其中的三个：
basic_lexer:针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if,is等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic_lexer分析的结果只有一个term,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。
chinese_vgram_lexer:专门的汉语分析器，支持所有汉字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term:‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。
chinese_lexer:这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持utf8,如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer。如果不做任何设置，Oracle缺省使用basic_lexer这个分析器。
3.测试全文检索
--测试用户为oratext，建立此用户和对应表空间的内容就不写了：
步骤一：授权，ctxsys登陆并对oratext用户授权：
alter user ctxsys identified by oracle account unlock;
create user oratext identified by oracle;
GRANT resource, connect, ctxapp TO oratext;
GRANT execute ON ctxsys.ctx_cls TO oratext;
GRANT execute ON ctxsys.ctx_ddl TO oratext;
GRANT execute ON ctxsys.ctx_doc TO oratext;
GRANT execute ON ctxsys.ctx_output TO oratext;
GRANT execute ON ctxsys.ctx_query TO oratext;
GRANT execute ON ctxsys.ctx_report TO oratext;
GRANT execute ON ctxsys.ctx_thes TO oratext;
GRANT execute ON ctxsys.ctx_ulexer TO oratext;
步骤二：设置词法分析器，使用chinese_vgram_lexer作为分析器：
conn oratext/oracle@service_name
BEGIN --设置词法分析器
ctx_ddl.create_preference ('oratext_lexer', 'chinese_vgram_lexer');
END;
/
--可以通过下面的语句查看系统默认及设置的oracle text参数：
SELECT pre_name, pre_object FROM ctx_preferences;
可以看到我刚刚设置的语法分析器参数oratext_lexer，（默认的有一个MY_LEXER的语法分析器参数）。
步骤三：建立测试表，插入测试数据：
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)--路径
);
INSERT INTO textdemo VALUES(1,'宫琦峻',to_date('2008-10-07','yyyy-mm-dd'),' 移动城堡','故事发生在19世纪末的欧洲，善良可爱的苏菲被恶毒的女巫施下魔咒，从18岁的女孩变成90岁的婆婆，孤单无助的她无意中走入镇外的移动城堡，据说它的主人哈尔以吸取女孩的灵魂为乐，但是事情并没有人们传说的那么可怕，性情古怪的哈尔居然收留了苏菲，两个人在四脚的移动城堡中开始了奇妙的共同生活，一段交织了爱与痛、乐与悲的爱情故事在战火中悄悄展开','E:\textsearch\moveingcastle.doc');
INSERT INTO textdemo VALUES(2,'莫贝克曼贝托夫',to_date('2008-10-07','yyyy-mm-dd'),' 子弹转弯','这部由俄罗斯导演提莫贝克曼贝托夫执导的影片自6 月末在北美上映以来，已经在全球取得了超过3亿美元的票房收入。在亚洲上映后也先后拿下日本、韩国等地的票房冠军宝座。虽然不少网友在此之前也相继通过各种渠道接触到本片，但相信影片凭着在大银幕上呈现出的超酷的视听效果，依然能够吸引大量影迷前往影院捧场。','E:\textsearch\catch.pdf');
INSERT INTO textdemo VALUES(3,'袁泉',to_date('2008-10-07','yyyy-mm-dd'),'主演吴彦祖和袁泉现身','电影《如梦》在上海同乐坊拍摄，主演吴彦祖和袁泉现身。由于是深夜拍摄，所以周围并没有过多的fans注意到，给了剧组一个很清净的拍摄环境，站在街头的袁泉低着头，在寒冷的夜里看上去还真有些像女鬼，令人毛骨悚然。','E:\textsearch\dream.txt');
commit;
--步骤四：在book_abstract字段建立索引使用刚刚设置的ORATEXT_LEXER ：chinese_vgram_lexer作为分析器。
CREATE INDEX demo_abstract ON textdemo(book_abstract) indextype IS ctxsys.context parameters('lexer ORATEXT_LEXER');
--之后如上所述多出很多dr$开头的表和索引，系统会创建四个相关的表：
DR$DEMO_ABSTRACT$I(分词后的TOKEN表)\
DR$DEMO_ABSTRACT$K\
DR$DEMO_ABSTRACT$N \
DR$DEMO_ABSTRACT$R
--下面的语句可以查看索引创建过程中是否发生了错误：
SELECT * FROM ctx_USER_index_errors
附：对于建立索引的类型（例如ctxsys.context），包括四种：context,ctxcat,ctxrule,ctxxpath。
CONTEXT用于对含有大量连续文本数据进行检索。支持word、html、xml、text等很多数据格式。支持范围（range）分区，支持并行创建索引（Parallel indexing）的索引类型。
支持类型：VARCHAR2, CLOB, BLOB, CHAR, BFILE, XMLType, and URIType.DML。操作后，需要CTX_DDL.SYNC_INDEX手工同步索引如果有查询包含多个词语，直接用空格隔开(如 oracle itpub)。
查询标识符CONTAINS
CTXCAT适用于混合查询语句（如查询条件包括产品id，价格，描述等）。适合于查询较小的具有一定结构的文本段。具有事务性。DML 操作后，索引会自动进行同步。
操作符：and,or,>,;<, =,between,in
查询标识符CATSEARCH
CTXRULE查询标识符MATCHES。
CTXXPATH（这两个索引没有去更多搜索相关内容）
一般来说我们建立CONTEXT类型的索引（CONTAINS来查询）。
--步骤五：查询测试
--查询或
SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 or 俄罗斯',20)>0;
SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 or 欧洲',20)>0;
--基本查询
SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡',20)>0;
--查询包含多个词语and测试通过
SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 and 欧洲',20)>0;
4.对多字段建立全文索引（还在揣摩中）
很多时候需要从多个文本字段中查询满足条件的记录，这时就需要建立针对多个字段的全文索引，例如需要从pmhsubjects(专题表)的 subjectname(专题名称)和briefintro(简介)上进行全文检索，则需要按以下步骤进行操作：
--建立多字段索引的preference，以ctxsys登录，并执行：
BEGIN
ctx_ddl.create_preference('ctx_demo_abstract_title','MULTI_COLUMN_DATASTORE');
END;
/
--建立preference对应的字段值(以ctxsys登录) 对应title path book_abstract三个字段建立索引：
BEGIN
ctx_ddl.set_attribute('ctx_demo_abstract_title ','columns','title,path');
END;
/
--建立全文索引:
create table textdemo1 as SELECT * FROM textdemo;
--ORA-29855:执行ODCIINDEXCREATE例行程序出错
CREATE INDEX demo_abstract_title ON textdemo1(book_abstract) indextype IS ctxsys.context parameters('DATASTORE ctxsys. ctx_demo_ abstract_title lexer ORATEXT_LEXER');
--测试
SELECT score(20),t.* FROM textdemo1 t WHERE contains(book_abstract,'移动城堡 or 俄罗斯',20)>0;
--5.对大字段进行检索测试
CREATE TABLE mytable(
id number PRIMARY KEY,
docs clob);
INSERT INTO mytable VALUES(111555,'this text will be indexed');
INSERT INTO mytable VALUES(111556,'this is a direct_datastore example');
Commit;
CREATE INDEX myindex ON mytable(docs) indextype IS ctxsys.context parameters ('datastore ctxsys.default_datastore');
SELECT * FROM mytable WHERE contains(docs, 'text') > 0;
-------------------------------------------------------------------------------------------------------------------------------------------------------
oracle 全文索引
--模糊查询的两种方式
select * from dj_nsrxx where contains(nsrmc,'和谦亨') > 0;
select * from dj_nsrxx where nsrmc like '%康仁如快餐店%';
--建立分词器
BEGIN
ctx_ddl.create_preference ('my_chinese_vgram_lexer', 'chinese_vgram_lexer');
END;
--建立索引
CREATE INDEX IDX_DJ_NSRXX_NSRMC_QW ON dj_nsrxx(nsrmc) indextype is ctxsys.context parameters('lexer my_chinese_vgram_lexer');
--使用实时同步（DML提交时同步全文索引）
CREATE INDEX IDX_DJ_NSRXX_NSRMC_QW ON dj_nsrxx(nsrmc) indextype is ctxsys.context PARAMETERS ('LEXER my_chinese_vgram_lexer SYNC (ON COMMIT)');
--刷新索引
BEGIN
ctx_ddl.sync_index('IDX_DJ_NSRXX_NSRMC_QW');
end;
/
--定时job刷新索引,未测试
BEGIN
DBMS_JOB.SUBMIT(:jobno,ctx_ddl.sync_index('IDX_DJ_NSRXX_NSRMC_QW'),SYSDATE, SYSDATE + (1/24/4));
commit;
END;
/
--查询索引产生的表
select * from dba_tables where table_name like '%IDX_DJ_NSRXX_NSRMC_QW%'
select count(*) from DR$IDX_DJ_NSRXX_NSRMC_QW$I
--删除索引
drop index IDX_DJ_NSRXX_NSRMC_QW;
--删除分词器
begin
ctx_ddl.drop_preference('my_chinese_vgram_lexer');
end;
--查询索引
select * from dba_indexes;
item chinese_vgram chinese comments
测试数据量 80万 80万
创建时间 37s 370S
刷新时间（0条跟新） 0s 0s 增量刷新，刷新速度较快
刷新时间（3条跟新） 0.031S 0.031S
索引条数 123万 68万
==注：
如果一个列没有使用全文索引，而使用了contains的话会报错 column is not indexed
select * from dj_nsrxx where contains(nsrmc,’易家联快餐店’) > 0;==
以下为网上摘取的内容，简单的描述了oracle全文索引的原理
--------------------------------------------------------------------------------
Oracle实现全文检索。
创建即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元（Oracle称为term）找出来，记录在一组以dr$开头的表中，同时记下该term出现的位置、次数、hash值等信息。
检索
检索时，Oracle从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分（score）,即所谓的‘匹配率’。而lexer则是该机制的核心，它决定了全文检索的效率。
Oracle针对不同的语言提供了不同的lexer,而我们通常能用到其中的三个：
basic_lexer:针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if , is等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic_lexer分析的结果只有一个term ,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。
chinese_vgram_lexer:专门的汉语分析器，支持所有汉字字符集（ZHS16CGB231280ZHS16GBKZHT32EUCZHT16BIG5ZHT32TRISZHT16MSWIN950ZHT16HKSCSUTF8）。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term: ‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。
chinese_lexer:这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持utf8,如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer.
==如果不做任何设置，Oracle缺省使用basic_lexer这个分析器。要指定使用哪一个lexer,可以这样操作：==
BEGIN
ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
END;
其中my_lexer是分析器名。
在索引建好后，我们可以在该用户下查到Oracle自动产生了以下几个表：(假设索引名为myindex)：
DRmyindexI、DRmyindexK、DRmyindexR、DRmyindexN其中以I表最重要，可以查询一下该表，看看有什么内容：
SELECT token_text, token_count FROM drirsk1I WHERE ROWNUM <= 20;
这里就不列出查询接过了。可以看到，该表中保存的其实就是Oracle 分析你的文档后，生成的term记录在这里，包括term出现的位置、次数、hash值等。当文档的内容改变后，可以想见这个I表的内容也应该相应改变，才能保证Oracle在做全文检索时正确检索到内容(因为所谓全文检索，其实核心就是查询这个表)。这就用到sync(同步) 和 optimize(优化)了。
同步(sync): 将新的term 保存到I表；
优化(optimize): 清除I表的垃圾，主要是将已经被删除的term从I表删除。
当基表中的被索引文档发生insert、update、delete操作的时候，基表的改变并不能马上影响到索引上直到同步索引。可以查询视图 CTX_USER_PENDING查看相应的改动。例如：
SELECT pnd_index_name, pnd_rowid,
TO_CHAR (pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss') timestamp
FROM ctx_user_pending;
该语句的输出类似如下：
PND_INDEX_NAME PND_ROWID TIMESTAMP
MYINDEX AAADXnAABAAAS3SAAC 06-oct-1999 15:56:50
--------------------------------------------------------------------------------
同步和优化方法:
可以使用Oracle提供的ctx_ddl包同步和优化索引
一. 对于CTXCAT类型的索引来说， 当对基表进行DML操作的时候，Oracle自动维护索引。对文档的改变马上反映到索引中。CTXCAT是事务形的索引。
索引的同步
在对基表插入，修改，删除之后同步索引。推荐使用sync同步索引。 语法：
ctx_ddl.sync_index(
idx_name IN VARCHAR2 DEFAULT NULL
memory IN VARCHAR2 DEFAULT NULL,
part_name IN VARCHAR2 DEFAULT NULL
parallel_degree IN NUMBER DEFAULT 1);
idx_name 索引名称
memory 指定同步索引需要的内存。默认是系统参数DEFAULT_INDEX_MEMORY 。
指定一个大的内存时候可以加快索引效率和查询速度，且索引有较少的碎片
part_name 同步哪个分区索引。
parallel_degree 并行同步索引。设置并行度。
例如：
同步索引myindex:Exec ctx_ddl.sync_index (‘myindex’);
实施建议：建议通过oracle的job对索引进行同步
--------------------------------------------------------------------------------
instr函数在Oracle/PLSQL中是返回要截取的字符串在源字符串中的位置。instr是一个非常好用的字符串处理函数，几乎所有的字符串分隔都用到此函数。
