oracle11g 建立全文索引

最新推荐文章于 2021-04-11 14:50:04 发布

堕落陨石

最新推荐文章于 2021-04-11 14:50:04 发布

阅读量1.4w

点赞数 1

分类专栏：网络技术

网络技术专栏收录该内容

25 篇文章 0 订阅

订阅专栏

在手动创建了数据库后，只是具备了基本的功能，有的还需要使用全文检索功能，因此还需要配置全文检索。

按照以下步骤来做：

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

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

即使满足也要查看下用户下有没有相应存储过程，如果没有，也得执行下面操作，可以不见表空间，但必须执行

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

@?/ctx/admin/defaults/drdefus.sql

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

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

sqlplus / as sysdba --进入控制台

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

因此，grant execute on ctxsys.ctx_ddl to sgpm with grant option;

connect sgpm/sgpm

设置语法分析器：exec ctx_ddl.drop_preference('chinalexer');

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

设置词法属性：exec ctx_ddl.drop_preference('idx_c_store');

begin

ctx_ddl.create_preference('idx_c_store','BASIC_STORAGE');

ctx_ddl.set_attribute('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.创建索引

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.同步索引

variable jobno number;

begin

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

end;

普通的就是用： exec ctx_ddl.sync_index('idx_c_cons_name');

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

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

------------------------------------------------------------------------------

数据库在创建全文索引的时候会检查几项工作：
（1）先检查oracle是否已安装“全文检索工具”，oracle11g 默认安装的，oracle10g默认没有安装

（2）再进行“语法分析”就是我们上面提到的“拆字”过程，例如创建词法分析器及相关表等操作
当你使用的是oracle10g数据库的时候发现创建不了全文索引说明，可能你的“全文检索工具”没有安装好
ORACLE10g 创建全文索引过程：
1,首先查看ORACLE是否已安装“全文检索工具”
通过查看是否存在 CTXSYS 用户，CTXAPP角色即可判断。
USERNAME
------------------------------
LS
CTXSYS 默认是没有的，需要安装2个脚本catctx.sql，drdefus.sql
2,如果ORACLE没有安装“全文检索工具”，则使用以下步骤手工安装。
a)进入ORACLE安装目录
cd $ORACLE_HOME
b)使用 DBA 角色登陆数据库
sqlplus sys/sys as sysdba
c)查看表空间文件存放路径
select name from v$datafile;
d)为 CTXSYS 用户创建表空间
CREATE TABLESPACE ctxsys
LOGGING
DATAFILE '/u01/app/oracle/oradata/LEO/file1/ctxsys01.dbf'
SIZE 32m
AUTOEXTEND ON
NEXT 32m MAXSIZE 2048m
EXTENT MANAGEMENT LOCAL ;
e)创建 CTXSYS 用户，创建 CTXAPP 角色
@?/ctx/admin/catctx.sql ctxsys ctxsys temp1 nolock
--(密码、表空间、临时表空间、用户状态)
--如果当前sql脚本无执行权限，请手工添加。
f)为 CTXSYS 执行初始化工作，如果没有此操作，后续操作会失败。
connect ctxsys/ctxsys;
@?/ctx/admin/defaults/drdefus.sql
3,创建全文索引
a)创建词法分析器及相关表
--词法分析器
execute ctx_ddl.create_preference('offerProdAddrLexer','CHINESE_LEXER');
--词法
execute ctx_ddl.create_preference('offerProdAddrList', 'BASIC_WORDLIST');
b)创建全文索引
Connected.
c)同步全文索引
exec ctx_ddl.sync_index('IDX_OFFER_PROD_2_ADDR_AA_DESC', '20M');
--全文索引的同步需要使用JOB来进行

--------------------------------

一、设置词法分析器
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是分析器名。

二、建立全文索引
在建立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 procedure 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; 查看数据文件信息

------------------------------------------------------------------------------------------------------------------------------------------------------------

1．创建数据存储定义（Datastore），使用多列数据存储在多列上创建全文索引
BEGIN
CTX_DDL.CREATE_PREFERENCE('INFOGRID_COM_DATASTORE','MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('INFOGRID_COM_DATASTORE','columns','NAME,ADDRESS,BUSINESS_ZONE,FAREN,FUND,INTRODUCTION');
END;

2．创建词法分析器（Lexer）
BEGIN
    CTX_DDL.CREATE_PREFERENCE('INFOGRID_LEXER', 'CHINESE_LEXER');
END;

3．创建全文索引（索引在DML提交后自动同步更新）
CREATE INDEX INFOGRID_COM_FULL_IDX ON g2b_com(NAME)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS (
   'LEXER INFOGRID_LEXER
    DATASTORE INFOGRID_COM_DATASTORE
    SYNC (ON COMMIT)' --使用实时同步（DML提交时同步全文索引）
)
--手工同步索引（如果使用自动同步，这步可省略）
begin
ctx_ddl.sync_index('INFOGRID_COM_FULL_IDX');
end;

4.使用全文索引查询（按照出现频率排序）
select score(0),t.* from g2b_com t where contains(NAME,'条件一，条件二',0)>0 order by score(0) desc

5．删除全文索引（删除词法分析器，删除数据存储定义，删除索引）
begin
ctx_ddl.drop_preference('INFOGRID_COM_DATASTORE');--删除数据储存定义DataStore
ctx_ddl.drop_preference('INFOGRID_LEXER');--删除词法分析器
end;
drop index INFOGRID_COM_FULL_IDX;--删除索引

------------------------------------------------------------------------------

自己亲自测试记录

第 5 行出现错误:
ORA-06550: 第 5 行, 第 43 列:
PLS-00302: 必须声明 'SET_ATTRIBUT' 组件
ORA-06550: 第 5 行, 第 35 列:
PL/SQL: Statement ignored

SQL> begin
2 ctx_ddl.create_preference('idx_c_store','BASIC_STORAGE');
3 ctx_ddl.set_attribut('idx_c_store','I_TABLE_CLAUSE','tablespaces Idx_ctxsy
');
4 ctx_ddl.set_attribute('idx_c_store','I_INDEX_CLAUSE','tablespace Idx_ctxsy
compress 2');
5 end;
6 /
ctx_ddl.set_attribut('idx_c_store','I_TABLE_CLAUSE','tablespaces Idx_ctxsy');
*
第 3 行出现错误:
ORA-06550: 第 3 行, 第 9 列:
PLS-00302: 必须声明 'SET_ATTRIBUT' 组件
ORA-06550: 第 3 行, 第 1 列:
PL/SQL: Statement ignored

SQL> begin
2 ctx_ddl.create_preference('idx_c_store','BASIC_STORAGE');
3 ctx_ddl.set_attribute('idx_c_store','I_TABLE_CLAUSE','tablespaces Idx_ctxs
y');
4 ctx_ddl.set_attribute('idx_c_store','I_INDEX_CLAUSE','tablespace Idx_ctxsy
compress 2');
5 end;
6 /

PL/SQL 过程已成功完成。

*
第 1 行出现错误:
ORA-29879: 使用同一个索引类型无法在列表上创建多个域索引

SQL> create index gsjssearch.idx_ALLSEARCHINFO_fullindex on gsjssearch.ALLSEARCH
INFO(CONTENT) indextype is ctxsys.context parameters('lexer chinalexer storage i
dx_c_store');
create index gsjssearch.idx_ALLSEARCHINFO_fullindex on gsjssearch.ALLSEARCHINFO(
CONTENT) indextype is ctxsys.context parameters('lexer chinalexer storage idx_c_
store')
*
第 1 行出现错误:
ORA-29855: 执行 ODCIINDEXCREATE 例行程序时出错
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-11439: Message 11439 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.TEXTINDEXMETHODS", line 366

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope =spfile;

系统已更改。

SQL> shutdown immediate;
ORA-01031: 权限不足
SQL> connect sys/oracle as sysdba;
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup;
ORACLE 例程已经启动。

Total System Global Area 778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             452986280 bytes
Database Buffers          318767104 bytes
Redo Buffers                5259264 bytes
数据库装载完毕。
数据库已经打开。
SQL> connect gsjssearch/gsjssearch;
已连接。
SQL> create index gsjssearch.idx_ALLSEARCHINFO_fullindex on gsjssearch.ALLSEARCH
INFO(CONTENT) indextype is ctxsys.context parameters('lexer chinalexer storage i
dx_c_store');
create index gsjssearch.idx_ALLSEARCHINFO_fullindex on gsjssearch.ALLSEARCHINFO(
CONTENT) indextype is ctxsys.context parameters('lexer chinalexer storage idx_c_
store')
                        *
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用

SQL> CREATE INDEX myindex ON ALLSEARCHINFO(CONTENT) indextype is ctxsys.context
parameters('lexer chinalexer storage idx_c_store');
CREATE INDEX myindex ON ALLSEARCHINFO(CONTENT) indextype is ctxsys.context para
meters('lexer chinalexer storage idx_c_store')
*
第 1 行出现错误:
ORA-29855: 执行 ODCIINDEXCREATE 例行程序时出错
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-50857: Message 50857 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-00922: 选项缺失或无效
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.TEXTINDEXMETHODS", line 366

SQL> CREATE INDEX myindex ON ALLSEARCHINFO(CONTENT) indextype is ctxsys.context
parameters('lexer chinalexer');
CREATE INDEX myindex ON ALLSEARCHINFO(CONTENT) indextype is ctxsys.context para
meters('lexer chinalexer')
*
第 1 行出现错误:
ORA-29855: 执行 ODCIINDEXCREATE 例行程序时出错
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-10502: Message 10502 not found; No message file for product=ctx,
facility=DRG; arguments: [MYINDEX]
DRG-11432: Message 11432 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.TEXTINDEXMETHODS", line 366

SQL> CREATE INDEX INFOGRID_COM_FULL_IDX ON ALLSEARCHINFO(CONTENT)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS (
4     'LEXER INFOGRID_LEXER
5      DATASTORE INFOGRID_COM_DATASTORE
6      SYNC (ON COMMIT)'
7 );
CREATE INDEX INFOGRID_COM_FULL_IDX ON ALLSEARCHINFO(CONTENT)
*
第 1 行出现错误:
ORA-29855: 执行 ODCIINDEXCREATE 例行程序时出错
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-10700: Message 10700 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.TEXTINDEXMETHODS", line 366

SQL> conn CTXSYS/ctxsys
已连接。
SQL> $ORACLE_HOME/ctx/admin/defaults/drdefus.sql;
'ORACLE_HOME' 不是内部或外部命令，也不是可运行的程序
或批处理文件。

SQL> @?/ctx/admin/defaults/drdefus.sql;
Creating lexer preference...
begin
*
第 1 行出现错误:
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-10701: Message 10701 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.CTX_DDL", line 26
ORA-06512: 在 line 2

Creating wordlist preference...
begin
*
第 1 行出现错误:
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-10701: Message 10701 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.CTX_DDL", line 26
ORA-06512: 在 line 2

Creating stoplist...
begin
*
第 1 行出现错误:
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-12500: Message 12500 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.CTX_DDL", line 533
ORA-06512: 在 line 2

begin
*
第 1 行出现错误:
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-12500: Message 12500 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.CTX_DDL", line 533
ORA-06512: 在 line 2

Creating default policy...
begin
*
第 1 行出现错误:
ORA-20000: Message 11104 not found; No message file for product=ctx,
facility=DRG; arguments:
DRG-10507: Message 10507 not found; No message file for product=ctx,
facility=DRG; arguments:
ORA-06512: 在 "CTXSYS.DRUE", line 160
ORA-06512: 在 "CTXSYS.CTX_DDL", line 997
ORA-06512: 在 line 2

SQL> create index ls.leo_text_t2_text_index on ALLSEARCHINFO(CONTENT) indextype
is ctxsys.context;
create index ls.leo_text_t2_text_index on ALLSEARCHINFO(CONTENT) indextype is ct
xsys.context
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> connect gsjssearch/gsjssearch;
已连接。
SQL> create index ls.leo_text_t2_text_index on ALLSEARCHINFO(CONTENT) indextype
is ctxsys.context;
create index ls.leo_text_t2_text_index on ALLSEARCHINFO(CONTENT) indextype is ct
xsys.context
*
第 1 行出现错误:
ORA-28100: 策略函数方案 LS 无效

SQL> create index leo_text_t2_text_index on ALLSEARCHINFO(CONTENT) indextype is
ctxsys.context;

索引已创建。

SQL>

create index leo_text_t2_text_index on ALLSEARCHINFO(CONTENT) indextype is ctxsys.context;

堕落陨石

关注

1
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
oracle11g 建立全文索引

在手动创建了数据库后，只是具备了基本的功能，有的还需要使用全文检索功能，因此还需要配置全文检索。按照以下步骤来做： 1.检查数据库是否具有全文检索功能（这是针对已经建成使用的数据库）查看用户中是否存在ctxsys用户，查询角色里是否存在ctxapp角色。以上两个中的1个不满足（不存在），则说明没有装过全文检索功能。即使满足也要查看下用户下
复制链接

扫一扫