oracle全文搜索

标签: oracle 搜索 | 发表时间:2013-11-14 00:52 | 作者:sea878412
分享到:
出处:http://www.iteye.com

1. 全文检索和普通检索的区别

不使用Oracle text功能,当然也有很多方法可以在Oracle数据库中搜索文本,比如INSTR函数和LIKE操作:

1

2

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的用户。

步骤二:赋权

在ctxsys用户下,授予测试用户oratext以下权限:

1

2

3

4

5

6

7

8

9

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;

步骤三:设置词法分析器(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这个分析器。

要指定使用哪一个lexer,可以这样操作:

第一.建立一个preference:

1

EXEC ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

第二.在建立全文索引索引时,指明所用的lexer:

1

CREATE INDEX myindex ON mytable(mycolumn) indextype IS ctxsys.context parameters('lexer my_lexer');

这样建立的全文检索索引,就会使用chinese_vgram_lexer作为分析器。

3. 测试全文检索

测试用户为oratext,建立此用户和对应表空间的内容就不写了:

步骤一:授权,ctxsys登陆并对oratext用户授权:

1

2

3

4

5

6

7

8

9

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作为分析器:

1

2

3

4

BEGIN

-- 设置词法分析器

ctx_ddl.create_preference ('oratext_lexer', 'chinese_vgram_lexer');

END;

可以通过下面的语句查看系统默认及设置的oracle text参数:

1

SELECT pre_name, pre_object FROM ctx_preferences

可以看到我刚刚设置的语法分析器参数oratext_lexer,(默认的有一个MY_LEXER的语法分析器参数)。

步骤三:建立测试表,插入测试数据:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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: \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作为分析器。

1

2

CREATE INDEX demo_abstract ON textdemo(book_abstract) indextype IS ctxsys.context parameters('lexer ORATEXT_LEXER'); 

commit;

之后如上所述多出很多dr$开头的表和索引,系统会创建四个相关的表:

DR$DEMO_ABSTRACT$I(分词后的TOKEN表)\DR$DEMO_ABSTRACT$K\DR$DEMO_ABSTRACT$N \DR$DEMO_ABSTRACT$R

下面的语句可以查看索引创建过程中是否发生了错误:

1

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,&gt,;<, =,between,in

查询标识符CATSEARCH

CTXRULE查询标识符MATCHES。

CTXXPATH(这两个索引没有去更多搜索相关内容)

一般来说我们建立CONTEXT类型的索引(CONTAINS来查询)。

步骤五:查询测试

1

2

3

4

5

6

7

-- 查询或

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登录,并执行:

1

2

3

BEGIN

ctx_ddl.create_preference('ctx_demo_abstract_title','MULTI_COLUMN_DATASTORE');

END;

建立preference对应的字段值(以ctxsys登录) 对应title path book_abstract三个字段建立索引:

1

2

3

BEGIN

ctx_ddl.set_attribute('ctx_demo_abstract_title ','columns','title,path');

END;

建立全文索引:

1

2

CREATE INDEX demo_abstract_title ON textdemo(book_abstract) indextype IS ctxsys.context parameters(' DATASTORE ctxsys. ctx_demo_ abstract_title lexer ORATEXT_LEXER'); 

commit;

测试

1

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 or 俄罗斯',20)>0;

5. 对大字段进行检索测试

1

2

3

4

5

6

7

8

9

10

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;



已有 0 人发表留言,猛击->> 这里<<-参与讨论


ITeye推荐



相关 [oracle 搜索] 推荐:

oracle全文搜索

- - Oracle - 数据库 - ITeye博客
不使用Oracle text功能,当然也有很多方法可以在Oracle数据库中搜索文本,比如INSTR函数和LIKE操作:. 有很多时候,使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候. 然而通过这些文本定位的方法将导致全表扫描,对资源来说消耗比较昂贵,而且实现的搜索功能也非常有限,因此对海量的文本数据进行搜索时,建议使用oralce提供的全文检索功能.

如何提升Oracle数据库搜索效率

- - Oracle - 数据库 - ITeye博客
需要用索引来解决,索引的创建规则如下:. 1、表的主键、外键必须有索引;. 2、数据量超过300的表应该有索引;. 3、经常与其他表进行连接的表,在连接字段上应该建立索引;. 4、经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;. 5、索引应该建在选择性高的字段上;. 6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;.

Oracle 收购 Ksplice

- feng823 - LinuxTOY
实现无需重启即可为 Linux 内核打安全补丁的 Ksplice 被 Oracle 收购. 在被收购前, Ksplice 为 Fedora, Ubuntu 免费提供该功能,对于 RHEL 和 CentOS 则需要订阅其产品. Oracle 表示将把 Ksplice 带来的零宕机安全更新功能添加到 Oracle 产品订阅服务中,同时停止对其他企业级 Linux 发行版的支持,将 Oracle Unbreakable Linux 打造成唯一具备零宕机安全更新功能的企业级 Linux 发行版.

Linux Ksplice,MySQL and Oracle

- Syn - DBA Notes
Oracle 在 7 月份收购了 Ksplice. 使用了 Ksplice 的 Linux 系统,为 Kernel 打补丁无需重启动,做系统维护的朋友应该明白这是一个杀手级特性. 现在该产品已经合并到 Oracle Linux 中. 目前已经有超过 700 家客户,超过 10 万套系统使用了 Ksplice (不知道国内是否已经有用户了.

oracle license计算

- Fenng - eagle&#39;s home
Oracle license的计算是基于CPU core的. 用core的数目乘以一个系数core factor就可以得到所需的oracle license的数目. 对于不同的CPU,core factor是不一样的,可以从oracle提供的这张列表中查到 Oracle Processor Core Factor Table.

Oracle Exadata初探

- - 技术改变世界 创新驱动中国 - 《程序员》官网
在我们看来,它是一个把硬件和软件根据合理的配置整合在一起的 Oracle数据库(在本文编写时是11gR2版本)平台. Exadata数据库机器包含了存储子系统,在存储层上运行着研发的新软件,这使得研发人员可以做一些在其他平台上无法完成的事情. 实际上,Exadata一开始是以一个存储系统形式诞生的,如果你跟参与研发此产品的人交谈,你经常会听到他们称存储组件为Exadata或者是SAGE (Storage Appliance for Grid Environments,网格环境存储设备),这是该产品研发项目的代码名称.

[转]Oracle 碎片

- - 小鸥的博客
  当生成一个数据库时,它会 分成称为表空间( Tablespace )的多个逻辑段( Segment ),如系统( System )表空间 , 临时( Temporary )表空间等. 一个表空间可以包含多个数据范围( Extent )和一个或多个自由范围块,即自由空间( Free Space ).

ORACLE:plsql优化

- - CSDN博客数据库推荐文章
 1、登录后默认自动选中My Objects. 设置方法:Tools菜单--Brower Filters会打开Brower Filters的定单窗口,把“My Objects”设为默认即可. 同理,可以在Tools菜单--Brower Filters中把你经常点的几个目录(比如:tables Views Seq Functions Procedures)移得靠上一点,并加上颜色区分,这样你的平均寻表时间会大大缩短,试试看.

oracle 索引

- - 数据库 - ITeye博客
        自动:在使用primary和unique后系统会自动创建唯一索引.         手动:create   index   索引名  on 表名(字段1,....). 查询表上有哪些索引(网上找的,能用,表名和索引名要大写). 1、查找表的所有索引(包括索引名,类型,构成列):. select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name and t.table_name = 要查询的表.

Oracle MySQL Or NoSQL续

- - Sky.Jian 朝阳的天空
接前面一篇,这里再将之前在“中国系统架构师大会”5周年的时候发布的纪念册“IT架构实录”上的一篇文章发出来,也算是前面博文中PPT的一个文字版解读吧. Oracle,MySQL 还是 NoSQL. 随着阿里系的“去IOE”运动在社区的宣传声越来越大,国内正在掀起一股“去xxx”的技术潮. 不仅仅是互联网企业,包括运营商以及金融机构都已经开始加入到这个潮流之中.