Oracle的全文检索功能

 http://www.oschina.net/code/snippet_113402_5948

 

[代码] [SQL]代码

view source

print ?

001	http://www.ll19.com/#/oracle_text.html

002

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

004

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

006

1

007

2

008

009	SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') > 0;

010	SELECT * FROM mytext WHERE thetext LIKE '%Oracle%';

011

012	有很多时候，使用instr和like是很理想的, 特别是搜索仅跨越很小的表的时候。然而通过这些文本定位的方法将导致全表扫描，对资源来说消耗比较昂贵，而且实现的搜索功能也非常有限，因此对海量的文本数据进行搜索时，建议使用oralce提供的全文检索功能。

013

014	附：这里顺带记录一下INSTR和LIKE:

015

016	Oracle中，可以使用 Instr 函数对某个字符串进行判断，判断其是否含有指定的字符。其语法为：Instr(string, substring, position, occurrence)。

017

018	string：代表源字符串（写入字段则表示此字段的内容）。

019

020	substring：代表想从源字符串中查找的子串。

021

022	position：代表查找的开始位置，该参数可选的，默认为1。

023

024	occurrence：代表想从源字符中查找出第几次出现的substring，该参数也是可选的，默认为1。

025

026	position 的值为负数，那么代表从右往左进行查找。

027

028	instr和like的性能比较

029

030	其实从效率角度来看，谁能用到索引，谁的查询速度就会快。

031

032	like有时可以用到索引，例如：name like ‘李%’，而当下面的情况时索引会失效：name like ‘%李’。所以一般我们查找中文类似于‘%字符%’时，索引都会失效。与其他数据库不同的是，oracle支持函数索引。例如在name字段上建个instr索引，查询速度就比较快了，这也是为什么instr会比like效率高的原因。

033

034	注:instr(title,’手册’)>0 相当于like‘%手册%’

035

036	instr(title,’手册’)=0 相当于not like‘%手册%’

037

038

2.设置全文检索

039

040	步骤步骤一：检查和设置数据库角色

041

042	首先检查数据库中是否有CTXSYS用户和CTXAPP脚色。如果没有这个用户和角色，意味着你的数据库创建时未安装intermedia功能（10G默认安装都有此用户和角色）。你必须修改数据库以安装这项功能。默认安装情况下，ctxsys用户是被锁定的，因此要先启用ctxsys的用户。

043

044

步骤二：赋权

045

046	在ctxsys用户下，授予测试用户oratext以下权限：

047

048

1

049

2

050

3

051

4

052

5

053

6

054

7

055

8

056

9

057

058

059

060	GRANT resource, connect, ctxapp TO oratext;

061	GRANT execute ON ctxsys.ctx_cls TO oratext;

062	GRANT execute ON ctxsys.ctx_ddl TO oratext;

063	GRANT execute ON ctxsys.ctx_doc TO oratext;

064	GRANT execute ON ctxsys.ctx_output TO oratext;

065	GRANT execute ON ctxsys.ctx_query TO oratext;

066	GRANT execute ON ctxsys.ctx_report TO oratext;

067	GRANT execute ON ctxsys.ctx_thes TO oratext;

068	GRANT execute ON ctxsys.ctx_ulexer TO oratext;

069

070	步骤三：设置词法分析器(lexer)

071

072	Oracle实现全文检索，其机制其实很简单。即通过Oracle专利的词法分析器(lexer),将文章中所有的表意单元(Oracle 称为 term)找出来，记录在一组以dr$开头的表中，同时记下该term出现的位置、次数、hash值等信息。检索时，Oracle从这组表中查找相应的term，并计算其出现频率，根据某个算法来计算每个文档的得分(score),即所谓的‘匹配率’。而lexer则是该机制的核心，它决定了全文检索的效率。Oracle针对不同的语言提供了不同的lexer,而我们通常能用到其中的三个：

073

074	basic_lexer:针对英语。它能根据空格和标点来将英语单词从句子中分离，还能自动将一些出现频率过高已经失去检索意义的单词作为‘垃圾’处理，如if,is等，具有较高的处理效率。但该lexer应用于汉语则有很多问题，由于它只认空格和标点，而汉语的一句话中通常不会有空格，因此，它会把整句话作为一个term,事实上失去检索能力。以‘中国人民站起来了’这句话为例，basic_lexer分析的结果只有一个term,就是‘中国人民站起来了’。此时若检索‘中国’，将检索不到内容。

075

076	chinese_vgram_lexer:专门的汉语分析器，支持所有汉字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个term:‘中’，‘中国’，‘国人’，‘人民’，‘民站’，‘站起’，起来’，‘来了’，‘了’。可以看出，这种分析方法，实现算法很简单，并且能实现‘一网打尽’，但效率则是差强人意。

077

078

chinese_lexer:这是一个新的汉语分析器，只支持utf8字符集。上面已经看到，chinese vgram lexer这个分析器由于不认识常用的汉语词汇，因此分析的单元非常机械，像上面的‘民站’，‘站起’在汉语中根本不会单独出现，因此这种term是没有意义的，反而影响效率。chinese_lexer的最大改进就是该分析器能认识大部分常用汉语词汇，因此能更有效率地分析句子，像以上两个愚蠢的单元将不会再出现，极大提高了效率。但是它只支持utf8,如果你的数据库是zhs16gbk字符集，则只能使用笨笨的那个Chinese vgram lexer。如果不做任何设置，Oracle缺省使用basic_lexer这个分析器。

079

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

081

082	第一．建立一个preference:

083

084

1

085

086

087

088	exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

089

090	第二．在建立全文索引索引时，指明所用的lexer:

091

092

1

093

094

095

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

097

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

099

100

3.测试全文检索

101

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

103

104	步骤一：授权，ctxsys登陆并对oratext用户授权：

105

106

1

107

2

108

3

109

4

110

5

111

6

112

7

113

8

114

9

115

116

117

118	GRANT resource, connect, ctxapp TO oratext;

119	GRANT execute ON ctxsys.ctx_cls TO oratext;

120	GRANT execute ON ctxsys.ctx_ddl TO oratext;

121	GRANT execute ON ctxsys.ctx_doc TO oratext;

122	GRANT execute ON ctxsys.ctx_output TO oratext;

123	GRANT execute ON ctxsys.ctx_query TO oratext;

124	GRANT execute ON ctxsys.ctx_report TO oratext;

125	GRANT execute ON ctxsys.ctx_thes TO oratext;

126	GRANT execute ON ctxsys.ctx_ulexer TO oratext;

127

128	步骤二：设置词法分析器，使用chinese_vgram_lexer作为分析器：

129

130

1

131

2

132

3

133

4

134

135

136

137

BEGIN

138

--设置词法分析器

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

140

END;

141

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

143

144

1

145

146

147

148	SELECT pre_name, pre_object FROM ctx_preferences

149

150	可以看到我刚刚设置的语法分析器参数oratext_lexer，（默认的有一个MY_LEXER的语法分析器参数）。

151

152	步骤三：建立测试表，插入测试数据：

153

154

1

155

2

156

3

157

4

158

5

159

6

160

7

161

8

162

9

163

10

164

11

165

12

166

13

167

14

168

15

169

16

170

17

171

18

172

173

174

175	CREATE TABLE textdemo(

176	id number NOT NULL PRIMARY KEY,

177	book_author varchar2(20),--作者

178	publish_time date,--发布日期

179	title varchar2(400),--标题

180	book_abstract varchar2(2000),--摘要

181	path varchar2(200)--路径

182

);

183

184

commit;

185

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

187

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

189

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

191

192

commit;

193

194	步骤四：在book_abstract字段建立索引使用刚刚设置的ORATEXT_LEXER ：chinese_vgram_lexer作为分析器。

195

196

1

197

2

198

199

200

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

202

commit;

203

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

205

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

207

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

209

210

1

211

212

213

214	SELECT * FROM ctx_USER_index_errors

215

216	附：对于建立索引的类型（例如ctxsys.context），包括四种：context,ctxcat,ctxrule,ctxxpath。

217

218	CONTEXT用于对含有大量连续文本数据进行检索。支持word、html、xml、text等很多数据格式。支持范围（range）分区，支持并行创建索引（Parallel indexing）的索引类型。

219

220	支持类型：VARCHAR2, CLOB, BLOB, CHAR, BFILE, XMLType, and URIType.DML。操作后，需要CTX_DDL.SYNC_INDEX手工同步索引如果有查询包含多个词语，直接用空格隔开(如 oracle itpub)。

221

222	查询标识符CONTAINS

223

224	CTXCAT适用于混合查询语句（如查询条件包括产品id，价格，描述等）。适合于查询较小的具有一定结构的文本段。具有事务性。DML 操作后，索引会自动进行同步。

225

226	操作符：and,or,&gt,;<, =,between,in

227

228	查询标识符CATSEARCH

229

230	CTXRULE查询标识符MATCHES。

231

232	CTXXPATH（这两个索引没有去更多搜索相关内容）

233

234	一般来说我们建立CONTEXT类型的索引（CONTAINS来查询）。

235

236

步骤五：查询测试

237

238

1

239

2

240

3

241

4

242

5

243

6

244

7

245

246

247

248

--查询或

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

250	SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 or 欧洲',20)>0;

251

--基本查询

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

253	--查询包含多个词语and测试通过

254	SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移动城堡 and 欧洲',20)>0;

255

256

测试通过。

257

258	4.对多字段建立全文索引（还在揣摩中）

259

260	很多时候需要从多个文本字段中查询满足条件的记录，这时就需要建立针对多个字段的全文索引，例如需要从pmhsubjects(专题表)的 subjectname(专题名称)和briefintro(简介)上进行全文检索，则需要按以下步骤进行操作：

261

262	建立多字段索引的preference，以ctxsys登录，并执行：

263

264

1

265

2

266

3

267

268

269

270

BEGIN

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

272

END;

273

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

275

276

1

277

2

278

3

279

280

281

282

BEGIN

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

284

END;

285

286

建立全文索引:

287

288

1

289

2

290

291

292

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

294

commit;

295

296

测试

297

298

1

299

300

301

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

303

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

305

306

1

307

2

308

3

309

4

310

5

311

6

312

7

313

8

314

9

315

10

316

317

318

319	CREATE TABLE mytable(id number PRIMARY KEY, docs clob);

320	INSERT INTO mytable VALUES(111555,'this text will be indexed');

321	INSERT INTO mytable VALUES(111556,'this is a direct_datastore example');

322

Commit;

323

324	CREATE INDEX myindex ON mytable(docs)

325	indextype IS ctxsys.context

326	parameters ('datastore ctxsys.default_datastore');

327

328	SELECT * FROM mytable WHERE contains(docs, 'text') > 0;