Oracle全文检索

1.在9i中手工安装全文检索功能

在Oracle9i R2中，Oracle的全文检索技术被称为Oracle Text，在Oracle8/8i中它被称作Oracle interMedia Text，在Oracle8以前它的名称是Oracle ConText Cartridge。
Oracle Text组件可以在安装数据库的时候选择，缺省是安装的，如果没有安装，那么可以按照以下方式手动安装Oracle Text：

[oracle9@single ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 17 21:50:24 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL> select username from dba_users;(检查是否有ctxsys用户，没有表示全文检索功能没有安装)

USERNAME
------------------------------
SYS
SYSTEM
DBSNMP
QUEST
SPLEX
SEAGULL
TEST
OUTLN
WMSYS

9 rows selected.

SQL> select file_name from dba_data_files;

FILE_NAME
---------------------------------------

/oracle9/oradata/ora9i/system01.dbf
/oracle9/oradata/ora9i/undotbs01.dbf
/oracle9/oradata/ora9i/indx01.dbf
/oracle9/oradata/ora9i/tools01.dbf
/oracle9/oradata/ora9i/users01.dbf
/oracle9/oradata/ora9i/tbs_test_01.dbf

6 rows selected.

SQL> create tablespace oratext datafile '/oracle9/oradata/ora9i/oratext01.dbf' size 100M ;

Tablespace created.

SQL> spool /tmp/oracletext.log SQL> start ?/ctx/admin/dr0csys ctxsys oratext temp (最后三个参数代表ctxsys用户的密码，默认表空间和临时表空间)
...creating user CTXSYS
...creating role CTXAPP
SQL> conn ctxsys/ctxsys;Connected.
SQL> start ?/ctx/admin/dr0inst ?/ctx/lib/libctxx9.so
============== ConText Database Objects Installation ==============

This script must be run as CTXSYS. This script will exit
below if run as any other user.

User is CTXSYS
... creating tables and Oracle object types
... creating table dr$parameter
... creating table dr$class
... creating table dr$object
... creating table dr$object_attribute
... creating table dr$object_attribute_lov
... creating table dr$preference
... creating table dr$preference_value
... creating table dr$index
... creating table dr$index_partition
... creating table dr$index_value
... creating table dr$policy_tab
... creating table dr$sqe
... creating table dr$ths
... creating table dr$ths_phrase
... creating table dr$ths_fphrase
... creating table dr$ths_bt
... creating table dr$section_group
... creating table dr$section
... creating table dr$stoplist
... creating table dr$stopword
... creating table dr$sub_lexer
... creating table dr$index_set
... creating table dr$index_set_index
... creating table dr$server
... creating table dr$pending
... creating table dr$waiting
... creating table dr$online_pending
... creating table dr$delete
... creating table dr$unindexed
... creating table dr$index_error
... creating table dr$parallel
... creating table dr$stats
... creating table dr$part_stats
... creating named data type ctx_feedback_item_type
... creating named data type ctx_feedback_type
... creating safe callout library
... creating CONTEXT interface
drop public synonym contains
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

drop public synonym score
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

... creating CTXCAT interface
drop public synonym catsearch
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

... creating CTXRULE interface
drop public synonym matches
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

... creating CTXXPATH interface
... loading package headers
================== Package Installation ==========================
Install Global Symbols
... loading driobj.pkh
No errors.
... loading dr0def.pkh
No errors.
...loading drig.pkh
No errors.
Install DR Internal package specs
... loading driutl.pkh
No errors.
... loading driacc.pkh
No errors.
... loading driadm.pkh
No errors.
... loading dricon.pkh
No errors.
... loading dridisp.pkh
No errors.
... loading dridml.pkh
No errors.
... loading dridoc.pkh
No errors.
... loading drierr.pkh
No errors.
... loading driddl.pkh
No errors.
... loading driddlp.pkh
No errors.
... loading driddlc.pkh
No errors.
... loading driddlr.pkh
No errors.
... loading driddlx.pkh
No errors.
... loading drilist.pkh
No errors.
... loading driload.pkh
No errors.
... loading driopt.pkh
No errors.
... loading dripipe.pkh
No errors.
... loading dripref.pkh
No errors.
... loading drirec.pkh
No errors.
... loading drirep.pkh
No errors.
... loading drirepm.pkh
No errors.
... loading drireps.pkh
No errors.
... loading drirept.pkh
No errors.
... loading drirepz.pkh
No errors.
... loading driths.pkh
No errors.
... loading drithsc.pkh
No errors.
... loading drithsd.pkh
No errors.
... loading drithsl.pkh
No errors.
... loading drithsx.pkh
No errors.
... loading drival.pkh
No errors.
... loading driexp.pkh
No errors.
... loading driimp.pkh
No errors.
... loading driparse.pkh
No errors.
... loading drixtab.pkh
No errors.
... loading drixtabc.pkh
No errors.
... loading drixtabr.pkh
No errors.
... loading drixtabx.pkh
No errors.
Install ConText public API specs
... loading dr0adm.pkh
No errors.
... loading dr0ddl.pkh
No errors.
... loading dr0doc.pkh
No errors.
... loading dr0out.pkh
No errors.
... loading dr0query.pkh
No errors.
... loading dr0thes.pkh
No errors.
... loading dr0repor.pkh
No errors.
... loading dr0ulex.pkh
No errors.
... loading dr0cls.pkh
No errors.
... loading package bodies
================== Package Installation ==========================
Install DR Internal package bodies
... loading driacc.plb
No errors.
... loading driadm.plb
No errors.
... loading dricon.plb
No errors.
... loading dridisp.plb
No errors.
... loading dridml.plb
No errors.
... loading dridoc.plb
No errors.
... loading drierr.plb
No errors.
... loading driddl.plb
No errors.
... loading driddlp.plb
No errors.
... loading driddlc.plb
No errors.
... loading driddlr.plb
No errors.
... loading driddlx.plb
No errors.
... loading drilist.plb
No errors.
... loading driload.plb
No errors.
... loading dripipe.plb
No errors.
... loading driopt.plb
No errors.
... loading dripref.plb
No errors.
... loading drirec.plb
No errors.
... loading drirep.plb
No errors.
... loading drirepm.plb
No errors.
... loading drireps.plb
No errors.
... loading drirept.plb
No errors.
... loading drirepz.plb
No errors.
... loading driths.plb
No errors.
... loading drithsc.plb
No errors.
... loading drithsd.plb
No errors.
... loading drithsl.plb
No errors.
... loading drithsx.plb
No errors.
... loading driutl.plb
No errors.
... loading drival.plb
No errors.
... loading driexp.plb
No errors.
... loading driimp.plb
No errors.
... loading driparse.plb
No errors.
... loading drixtab.plb
No errors.
... loading drixtabc.plb
No errors.
... loading drixtabr.plb
No errors.
... loading drixtabx.plb
No errors.
... loading driproc.plb
No errors.
Install ConText public API bodies
... loading dr0adm.plb
No errors.
... loading dr0ddl.plb
No errors.
... loading dr0doc.plb
No errors.
... loading dr0out.plb
No errors.
... loading dr0query.plb
No errors.
... loading dr0thes.plb
No errors.
... loading dr0repor.plb
No errors.
... loading dr0cls.plb
No errors.
========================================================
... creating CONTEXT interface body
No errors.
No errors.
... creating CTXCAT interface body
No errors.
... creating CTXRULE interface body
No errors.
... creating CTXXPATH interface body
No errors.
... creating CONTEXT index type
drop public synonym context
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

... creating CTXCAT index type
drop public synonym ctxcat
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

... creating CTXRULE index type
drop public synonym ctxrule
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

... creating CTXXPATH index type
drop public synonym ctxxpath
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

... creating objects
Removing old object definitions...
Creating new object definitions...
...creating default preferences
Create default preferences
System Parameters
========================================================
SQL> start ?/ctx/admin/defaults/drdefus.sql;
Creating lexer preference...
Creating wordlist preference...
Creating stoplist...
Creating default policy...

2.在9i中使用全文检索功能

作了个简单试验，演示了最基本的全文检索功能，过程如下：

[root@single ~]# su - oracle9
[oracle9@single ~]$ sqlplus seagull/aaaa

SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 17 22:04:02 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

SQL>

SQL> @/oracle9/product/9i/sqlplus/demo/demobld.sql(利用oracle提供的demo脚本创建测试表)

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
DUMMY TABLE
EMP TABLE
SALGRADE TABLE

--插入测试数据

SQL> insert into dept (deptno,dname,loc) values ('50','公安软件事业部','四楼');

1 row created.

SQL> insert into dept (deptno,dname,loc) values ('80','系统集成部','一楼');

1 row created.

SQL>insert into dept (deptno,dname,loc) values ('60','社保软件事业部','三楼');
1 row created.

SQL>insert into dept (deptno,dname,loc) values ('70','人事部','二楼');
1 row created.

SQL> commit;

Commit complete.

--用传统的方法查询数据

SQL> select * from dept where INSTR (dname, '集成')>0;

DEPTNO DNAME LOC
---------- -------------- -------------
80 系统集成部 一楼

SQL> select * from dept where dname like '%软件%';

DEPTNO DNAME LOC
---------- -------------- -------------
50 公安软件事业部 四楼
60 社保软件事业部 三楼

--以下用全文检索的方法查询数据

--先用ctxsys用户创建词法分析器

SQL> conn ctxsys/ctxsys
Connected.
SQL> begin
2 ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
3 ctx_ddl.create_preference ('mystore', 'BASIC_STORAGE');
4 ctx_ddl.set_attribute ('mystore', 'I_TABLE_CLAUSE', 'tablespace indx ');
5 ctx_ddl.set_attribute ('mystore', 'I_INDEX_CLAUSE', 'tablespace indx compress 2 ');
6 end;
7 /

PL/SQL procedure successfully completed.

--进入seagull用户，创建全文检索索引

SQL> conn seagull/aaaa
Connected.
SQL> CREATE INDEX ind_dept_dname ON dept( dname ) INDEXTYPE is CTXSYS.CONTEXT parameters('lexer ctxsys.my_lexer storage ctxsys.mystore');

Index created.

--利用全文检索功能来查询数据

SQL> select * from dept where contains(dname,'集成') >0;

DEPTNO DNAME LOC
---------- -------------- -------------
80 系统集成部 一楼

SQL> select * from dept where contains(dname,'集成 or 软件') >0;

DEPTNO DNAME LOC
---------- -------------- -------------
80 系统集成部 一楼
60 社保软件事业部 三楼
50 公安软件事业部 四楼

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8861952/viewspace-1034676/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8861952/viewspace-1034676/