oraclesql全文检索_oracle数据库实现全文检索

‘壹’ oracle数据库实现全文检索

Oracle全文检索配置方法：

1.检查数据库是否具有全文检索功能（这是针对已经建成使用的数据库）

查看用户中是否存在ctxsys用户，查询角色里是否存在ctxapp角色。以上两个中的1个不满足（不存在），则说明没有装过全文检索功能。

使用contains函数的时候，若没有全文检索则会报错的。

2.若没有，则需要手动建立，先建立全文检索要使用的空间

sqlplus / as sysdba --进入控制台

createtablespaceIdx_ctxsysdatafile'/oradata/sg186fx/ctxsys01.;--创建全文检索使用的表空间

3.创建全文检索使用的用户和角色及相应的包，则需要执行oracle自带的一个脚本：cd $ORACLE_HOME/ctx/admin/catctx.sql

还是在sqlplus中执行：

@?/ctx/admin/catctx.sql ctxsys Idx_ctxsys temp nolock

在执行这个脚本的时候，输入了几个参数，第一个参数ctxsys为ctxsys用户的密码

第二个参数Idx_ctxsys为ctxsys用户要使用的表空间

第三个参数temp为ctxsys用户使用的临时表空间

第四个参数nolock为ctxsys用户处于解锁状态。

4.创建完成后，要登录ctxsys用户

connect ctxsys/ctxsys

执行以下脚本：@?/ctx/admin/defaults/drdefus.sql(这是个很重要的脚本，后面创建索引会使用该脚本创建的信息)

5.创建全文索引语法分析器

先要明确使用全文索引的用户，我要使用全文索引的是sgpm用户

因此

grantexecuteonctxsys.ctx_ddltosgpmwithgrantoption;

connect sgpm/sgpm

设置语法分析器：

execctx_ddl.drop_preference('chinalexer');
execctx_ddl.create_preference('chinalexer','chinese_lexer');

设置词法属性：

execctx_ddl.drop_preference('idx_c_store');

begin
ctx_ddl.create_preference('idx_c_store','BASIC_STORAGE');
ctx_ddl.set_attribut('idx_c_store','I_TABLE_CLAUSE','tablespacesIdx_ctxsy');
ctx_ddl.set_attribute('idx_c_store','I_INDEX_CLAUSE','tablespaceIdx_ctxsycompress2');
end;
/

6.创建索引

createindexsgpm.idx_c_cons_nameonsgpm.c_cons(cons_name)indextypeisctxsys.contextparameters('lexerchinalexerstorageidx_c_store');

7.同步索引

variablejobnonumber;
begin
dbms_job.submit(:jobno,'pkg_sp_tools.p_cont_sys_index();',sysdate,'trunc(sysdate)+19/24+1');--执行的是个性化方法。
end;
/

普通的就是用：

execctx_ddl.sync_index('idx_c_cons_name');

到此，全文检索创建成功，contains函数就可以正常使用了。

注意：创建的过程中会出现ORA-29879:cannot create multiple domain index on a column listusing same indextype ,这说明在其他用户下已经建立了该索引。

‘贰’ oracle 全文索引，chinese_vgram_lexer 分析器，不能检索类似电话号码，身份证号码，这样的纯数字。

Oracle全文索引的BASIC属性主要是针对西方英语语系，英语语系的单词是通过空格、标点和回车来分隔的。而中文则需要索引来自动切词。[@more@]2看下面这个例子：
SQL> CREATE TABLE T (ID NUMBER, DOCS VARCHAR2(1000));
表已创建。
SQL> INSERT INTO T VALUES (1, '一个中文例子，测试BASIC_LEXER语法属性是否可以正常识别中文。');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> CREATE INDEX IND_T_DOCS ON T (DOCS) INDEXTYPE IS CTXSYS.CONTEXT;
索引已创建。
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '中文') > 0;
未选定行
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '一个中文例子') > 0;
ID DOCS
1 一个中文例子，测试BASIC_LEXER语法属性是否可以正常识别中文。
通过BASIC_LEXER来索引中文，Oracle只识别被空格、标点和回车符分隔出来的部分。需要对中文内容进行索引的话，就必须使用中文的LEXER。
Oracle提供了两种预定义的中文LEXER属性：CHINESE_VGRAM_LEXER和CHINESE_LEXER。Oracle在文档上是这样描述的：
CHINESE_LEXER相对应CHINESE_VGRAM_LEXER属性有如下的优点：
产生的索引更小；
更好的查询响应时间；
产生更接近真实的索引切词，使得查询精度更高；
支持停用词。
虽然Oracle说明了使用CHINESE_LEXER的大量好处，但是CHINESE_LEXER的实际效果却存在一定的问题：
SQL> DROP INDEX IND_T_DOCS;
索引已丢弃。
SQL> TRUNCATE TABLE T;
表已截掉。
SQL> INSERT INTO T VALUES (1, '北京大学未名湖');
已创建 1 行。
SQL> INSERT INTO T VALUES (2, '北京邮电大学');
已创建 1 行。
SQL> INSERT INTO T VALUES (3, '北京市第十四中学');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> CONN CTXSYS/CTXSYS@YANGTK
已连接。
SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('TEST_CHINESE_VGRAM_LEXER', 'CHINESE_VGRAM_LEXER');
3 CTX_DDL.CREATE_PREFERENCE('TEST_CHINESE_LEXER', 'CHINESE_LEXER');
4 END;
5 /
PL/SQL 过程已成功完成。
SQL> CONN YANGTK/YANGTK@YANGTK
已连接。
SQL> CREATE INDEX IND_T_DOCS ON T(DOCS) INDEXTYPE IS CTXSYS.CONTEXT
2 PARAMETERS ('LEXER CTXSYS.TEST_CHINESE_VGRAM_LEXER');
索引已创建。
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '北京') > 0;
ID DOCS
3 北京市第十四中学
2 北京邮电大学
1 北京大学未名湖
SQL> DROP INDEX IND_T_DOCS;
索引已丢弃。
SQL> CREATE INDEX IND_T_DOCS ON T(DOCS) INDEXTYPE IS CTXSYS.CONTEXT
2 PARAMETERS ('LEXER CTXSYS.TEST_CHINESE_LEXER');
索引已创建。
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '北京') > 0;
ID DOCS
2 北京邮电大学
通过对比结果可以发现：使用CHINESE_LEXER 没有将第一条记录和第三条记录中的北京检索出来。也许Oracle的CHINESE_LEXER认为北京大学和北京市是一个完整的词汇，而没有对其进行进一步切分。也许这就是Oracle提到的这种切分方式更接近于真实的情况。我不知道读者会怎样看待这个问题，不过我更倾向于使用 CHINESE_VGRAM_LEXER，个人感觉返回记录多一些总比漏掉一些要好。

‘叁’ oracle数据库的全文检索，最大能检索多大的文本。

检索多大的文本？
oracle表的列最多有1000列，
对行的数量，逻辑上应该没有限制，应该受限于你的物理空间，如硬盘等。当然，与建库时的表空间、用户配额的大小有关。

‘肆’ 如何实现涉及多个表的多个字段的全文检索功能（oracle）

oracle可以支持多个字段上的全文索引，但如果还涉及到多张表的话，就只能在每张表上建一个全文索引。但这样的话，查询时很不方便。而且如果涉及到的表或者字段发生变化，需要重新建全文索引，可扩展性不佳。
另一个思路是专门建立一张中间表，其中包括以下字段：表名，字段名，字段值，对象ID。在字段值这个字段上建立全文索引。然后定时的将原来四张表中须检索的字段同步到此中间表（可考虑用物化视图）。查询时，直接对此中间表进行查询即可。
举个例子：假设原表是文章表，其中有如下一条数据：
ID
标题
内容
作者
1
德川家康为什么能统一日本？
请教一下，为什么最后是德川家康统一日本呢？
德川家康粉
现在需要检索在标题，内容或作者字段中存在“德川家康”的数据。
首先，需要将原数据同步到中间表，变成以下三条数据：
表名
字段名
字段值
对象ID
文章表
标题
德川家康为什么能统一日本？
1
文章表
内容
请教一下，为什么最后是德川家康统一日本呢？
1
文章表
作者
德川家康粉
1
通过在字段值这个字段上全文检索“德川家康”，可以筛选出以上三条数据。这三条数据都指向文章表中的ID＝1的文章。从文章表里取出这条数据，检索就成功结束了。
此方法的优点：一是检索的速度比较快；二是可以兼容指定表或指定字段的检索；三是需要检索的表或字段可以动态删减，可扩展性好。
主要的缺点一是需要一张中间表，并定时同步，消耗额外的服务器资源，二是同步的过程会造成延时，即新修改但尚未同步的数据会暂时检索不到。

‘伍’ 如何实现Oracle数据库Text全文检索

Oracle全文检索配置方法：
1.检查数据库是否具有全文检索功能（这是针对已经建成使用的数据库）
查看用户中是否存在ctxsys用户，查询角色里是否存在ctxapp角色。以上两个中的1个不满足（不存在），则说明没有装过全文检索功能。
使用contains函数的时候，若没有全文检索则会报错的。
2.若没有，则需要手动建立，先建立全文检索要使用的空间
sqlplus / as sysdba --进入控制台

1

create tablespace Idx_ctxsys datafile '/oradata/sg186fx/ctxsys01.dbf size 10240M autoextend on next 32M maxsize 20480M;--创建全文检索使用的表空间

3.创建全文检索使用的用户和角色及相应的包，则需要执行oracle自带的一个脚本：cd $ORACLE_HOME/ctx/admin/catctx.sql
还是在sqlplus中执行：
@?/ctx/admin/catctx.sql ctxsys Idx_ctxsys temp nolock
在执行这个脚本的时候，输入了几个参数，第一个参数ctxsys为ctxsys用户的密码
第二个参数Idx_ctxsys为ctxsys用户要使用的表空间
第三个参数temp为ctxsys用户使用的临时表空间
第四个参数nolock为ctxsys用户处于解锁状态。
4.创建完成后，要登录ctxsys用户
connect ctxsys/ctxsys
执行以下脚本：@?/ctx/admin/defaults/drdefus.sql(这是个很重要的脚本，后面创建索引会使用该脚本创建的信息)
5.创建全文索引语法分析器
先要明确使用全文索引的用户，我要使用全文索引的是sgpm用户
因此

1

grant execute on ctxsys.ctx_ddl to sgpm with grant option;

connect sgpm/sgpm
设置语法分析器：

1
2

exec ctx_ddl.drop_preference('chinalexer');
exec ctx_ddl.create_preference('chinalexer','chinese_lexer');

设置词法属性：

1

exec ctx_ddl.drop_preference('idx_c_store');

1
2
3
4
5
6

begin
ctx_ddl.create_preference('idx_c_store','BASIC_STORAGE');
ctx_ddl.set_attribut('idx_c_store','I_TABLE_CLAUSE','tablespaces Idx_ctxsy');
ctx_ddl.set_attribute('idx_c_store','I_INDEX_CLAUSE','tablespace Idx_ctxsy compress 2');
end;
/

6.创建索引

1

create index sgpm.idx_c_cons_name on sgpm.c_cons(cons_name) indextype is ctxsys.context parameters('lexer chinalexer storage idx_c_store');

7.同步索引

1
2
3
4
5

variable jobno number;
begin
dbms_job.submit(:jobno,'pkg_sp_tools.p_cont_sys_index();',sysdate,'trunc(sysdate)+19/24+1'); --执行的是个性化方法。
end;
/

普通的就是用：

1

exec ctx_ddl.sync_index('idx_c_cons_name');

到此，全文检索创建成功，contains函数就可以正常使用了。
注意：创建的过程中会出现ORA-29879:cannot create multiple domain index on a column listusing same indextype ,这说明在其他用户下已经建立了该索引。

‘陆’ 如何手工安装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:专门的汉语分析器，支持所有汉字字符集（SUTF8）。该分析器按字为单元来分析汉语句子。‘中国人民站起来了’这句话，会被它分析成如下几个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是分析器名。
二、建立全文索引
在建立intermedia索引时，指明所用的lexer:
CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context parameters('lexer my_lexer');
※个人体会：全文索引建立后，用pl/sql developer工具view table，在index这一栏是看不到索引信息的。
而本人在删除全文索引时遇到过一下报错：
SQL> drop index searchkeytbl_key;
drop index searchkeytbl_key
ORA-29868: cannot issue DDL on a domain index marked as LOADING
解决方法：
ORA-29868: cannot issue DDL on a domain index marked as LOADING
说明：在创建索引的时候断开、重启等导致索引中断没有执行成功，之后再drop或者rebuild等操作的时候都会报此错误
解决：只能drop index ind_name force强行删除，然后再重建
三、索引同步维护
用以下的两个job来完成(该job要建在和表同一个用户下) :
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''index_name'');',
SYSDATE, 'SYSDATE + (1/24/4)');
commit;
END; //同步
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''myindex'',''FULL'');',
SYSDATE, 'SYSDATE + 1');
commit; //优化
建完后手动运行下：
exec dbms_job.run(jobno);
※个人体会：运行job可能会有问题，此时可以单独运行索引，尝试一下
exec ctx_ddl.sync_index('index_name');
如果单独运行没有问题，则检查job是否写错或者当前操作的oracle数据库用户有无运行存储过程的权限
SQL> exec dbms_job.run(190);
begin dbms_job.run(190); end;
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1
以上报错就是用户没有运行任何存储过程造成的，此时需要对用户加上这个权限：
SQL> grant execute any procere to oracle_username;
再看一下job的情况
select * from user_jobs;
四、测试
关联查询： select * from table_name where contains (column_name,'keyword') >0;
SQL> select * from searchkeytbl where type='城市' and contains (key,'杨浦') >0;
USERNAME TYPE KEY
-------------------- ---------------------------------------- --------------------------------------------------------------------------------
mujian80 城市上海市杨浦区
五、问题
加全文索引遇到的问题(不断更新)
SQL> create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer');
create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer')
ORA-24795: Illegal COMMIT attempt made
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexCreate
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvdml.MaintainKTab
ORA-24795: Illegal COMMIT attempt made
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
To avoid the error, please use one of the following solutions
1. Don't use a 32k-blocksized tablespace to store the internal index objects
- or -
2. Download Patch 5596325 from Metalink and apply it as described in the README file.
看一下可能是用于创建索引的表空间不够了
reports——>DBA——>total free space pl/sql developer工具，查看表空间的剩余空间
select * from v$datafile; 查看数据文件信息

‘柒’ oracle数据库的全文检索。

Toad上你用system用户登录，你是你所谓的"system用户界面"了

‘捌’ Oracle全文检索是什么意思

全文检索是一种将文件中所有文本与检索项匹配的文字资料检索方法。全文检索系统是按照全文检索理论建立起来的用于提供全文检索服务的软件系统。

判断检索效果的两个指标：

查全率=被检出相关信息量/相关信息总量（%）

查准率=被检出相关信息量/被检出信息总量（%）