oracle全文检索的创建及使用

oracle数据库选择的是Oracle 9i,10g版本.
数据类型可以是CLOB, BLOB, varchar类型的文本数据。
字符集是数据库建立时的默认的字符集.

1.创建Oracle数据库
     Programs > Oracle - home_name > Configuration and MigrationTools > Database Configuration Assistant
通过oracle配置助理来创建数据库,数据库名为test.只要一切都按oracle的默认设置进行安装,OracleText是随 Oracle数据库安装的. oracle text是oracle9i的一个组件.oracletext能进行关于文档的语言分析，使用多种方法查找文档，包括关键字、上下文查询、逻辑操作、模式匹配、混合主题查询、html/xml段落查找等方法,实现机制如下:
    通过oracle的词法分析器(lexer),将文章中所有的表意单元(oracle称之为term)找出来,纪录在一组以dr$开头的表中,同时记下该term出现的位置,次数,hash值等信息.检索时,oracle从这组表中查找相应term,并计算其出现频率,根据某个
算法来计算每个文档的匹配率(score)2.oracle全文索引的几个关键表oracle全文索引的原理是把对索引的查找转换成对几个表的查找。

  下面对几个主要的表进行简单介绍：
DR$INDEX_NAME$I:
该表的owner是基表所属的schema。
这个表包含了所有的关键字和其对应的位置信息。
Name       Type        
----------- ------------ -----------------------------------------------
TOKEN_TEXT  VARCHAR2(64)--标识字符，根据索引对应的语法分析器生成的。
TOKEN_TYPE NUMBER(3)   
TOKEN_FIRST NUMBER(10)  --当前关键字对应的最小docid
TOKEN_LAST NUMBER(10)  --当前关键字对应的最大docid
TOKEN_COUNT NUMBER(10)  --关键字在当前docid范围内满足条件的docid
TOKEN_INFO BLOB       --记录关键字对应的位置信息
------------------------------------------------------------------------
DR$INDEX_NAME$K:
这个表主要映射docid和rowid的关系。docid是全文检索的概念，它相当于表的一个条目。被全文检索索引的每一行
都对应一个docid，即一对一的关系。
Name    Type
------- ----------
DOCID   NUMBER(38)
TEXTKEY  ROWID
-------------------------------------------------------------------------
DR$INDEX_NAME$R:
这个表主要用来完成根据docid查询rowid的操作。这个表一般只有22行。这个表的DATA字段存的都是rowid，它是一个rowid的集合。由于rowid的长度都是固定的（18位），而docid是一个逻辑概念，它是根据序列顺序增长的，所以可以根据这个读取docid对应的rowid。如doc=1时，取blob的前18位就是doc=1时对应的rowid；当docid=2时，取blob的19-36位就是docid=2对应的rowid。docid=N时，其对应的rowid=Substr(DATA_ROWID,(N*18)+1,18)，其中DATA_ROWID是根据DR$INDEX_NAME$R表的DATA字段经过一定规则转换的数据。为了避免DR$INDEX_NAME$R表中的单行长度太大，oracle在初始时把DR$INDEX_NAME$R分成了22行。这个数量是会变化的，如果数据量增长非常大，DR$INDEX_NAME$R的行数也会增多。
Name  Type     
------ ---------
ROW_NO NUMBER(3) --行号
DATA  BLOB     --存储rowid集合
--------------------------------------

DR$INDEX_NAME$N:。
这个表用来保存被删除的docid的信息。在优化域索引时会使用并删除这些信息。
Name     Type      
--------- ----------
NLT_DOCIDNUMBER(38)   --docid                      
NLT_MARK CHAR(1)      --N 表示新产生的无效docid；M表示正在被优化的docid
-------------------------------------------------------------------------

DR$PENDING:
保存insert和update操作产生的新数据。在索引同步时会被用到这些数据，同步完成后这些数据会被删除。在该表(PND_CID,PND_PID, PND_ROWID)组成一个主键。
Name           Type   
--------------- -------
PND_CID        NUMBER 
PND_PID        NUMBER 
PND_ROWID      ROWID  
PND_TIMESTAMP  DATE   
PND_LOCK_FAILED CHAR(1)
----------------------------------------------------------

DR$WAITING:
表DR$DEPENDING的(PND_CID, PND_PID, PND_ROWID)必须唯一，而(PND_CID,PND_PID,PND_ROWID)唯一标识一个被插入或者更新的记录。当插入一条记录后，会在DR$DEPENDING产生一条记录。在没有同步索引的情况下更新该记录，也会产生一条记录来记录更新的信息，其(PND_CID,PND_PID,PND_ROWID)是一样的。为了不违反唯一性约束，后来的更新对应的信息会记录在DR$WAITING中。如果对一个没有被同步索引的记录执行多次更新，会在DR$WAITING记录多条信息。
这个表主要用途是：
如果在索引同步的过程中修改了在DR$PENGDING中已经存在的记录，由于同步还没有完成DR$PENGDING中的记录没有被删除，所以，新的修改记录的信息不能插入到DR$PENGDING中，只能临时放在DR$WAITING中。DR$WAITDING在这种情况下才有实际意义。但实际上，在更新数据的时候，oracle并不会判断是否有同步索引的任务在运行，它只是简单地判断被更新地记录地信息在DR$PENGDING是否存在，如果存在，则把更新信息放到DR$WAITING中，否则就放到DR$PENGDING中。
Name     Type  
--------- ------
WTG_CID   NUMBER
WTG_ROWID ROWID 
WTG_PID   NUMBER
------------------

DR$DELETE:
当用户在一个会话删除一条记录时，在提交或者回滚前，会在DR$DELETE表插入一条记录，标识某条记录已经被删除，这样，在当前会话查询被删除的记录将不会有结果返回；当会话提交事务，oracle会把DR$DELETE的记录删除。
Name      Type  
---------- ------
DEL_IDX_IDNUMBER    --index_id                     
DEL_IXP_IDNUMBER                          
DEL_DOCID NUMBER    --doc id
-------------------------------

DR$INDEX
该表主要记录域索引的相关信息。
Name               Type         
------------------- -------------
IDX_ID             NUMBER(38)   --索引ID
IDX_OWNER#         NUMBER       
IDX_NAME           VARCHAR2(30) 
IDX_TABLE_OWNER#   NUMBER       
IDX_TABLE#         NUMBER       
IDX_KEY_NAME       VARCHAR2(256)
IDX_KEY_TYPE       NUMBER       
IDX_TEXT_NAME      VARCHAR2(256)
IDX_TEXT_TYPE      NUMBER       
IDX_TEXT_LENGTH    NUMBER       
IDX_DOCID_COUNT    NUMBER     --docid的数量 
IDX_STATUS         VARCHAR2(12) 
IDX_VERSION        NUMBER       
IDX_NEXTID         NUMBER       
IDX_OPT_TOKEN      VARCHAR2(64)  --当次优化的起始关键字
IDX_OPT_TYPE       NUMBER       
IDX_OPT_COUNT      NUMBER       --当次优化的doc数量
IDX_LANGUAGE_COLUMN VARCHAR2(256)
IDX_FORMAT_COLUMN  VARCHAR2(256)
IDX_CHARSET_COLUMN  VARCHAR2(256)
IDX_TYPE           NUMBER       
IDX_OPTION         VARCHAR2(40) 
以下是建立最简单支持英文的全文检索

3.以sys用户连接数据库,创建用户
   以sys登录,创建一个用户admin,并赋予该用户resource,dba,connection,ctxapp角色:

create user admin
  identified by text
  default tablespace SYSTEM
  temporary tablespace TEMP;
grant resource to admin;
grant connect to admin;
--将CTXAPP角色赋予TEXT用
grant ctxapp to admin;
grant dba to admin;

4.以admin用户连接数据库,创建表和索引
--建表
  CREATE TABLE test (id NUMBER primary key notnull, content blob not null,createtime date);
  insert test values(1,'你好',null);
  insert test values(2,'检索',null);
  insert test values(3,'www',null);
  insert test values(4,'HELLO',null);
--建立文本索引参考项:(设置搜索器类型) 
BEGIN
ctx_ddl.create_preference ('main_lexer', 'chinese_lexer');
ctx_ddl.create_preference('mywordlist', 'BASIC_WORDLIST');
--PREFIX_INDEX，提高右通配符查询的效率
ctx_ddl.set_attribute('mywordlist','PREFIX_INDEX','TRUE');
ctx_ddl.set_attribute('mywordlist','PREFIX_MIN_LENGTH',1);
ctx_ddl.set_attribute('mywordlist','PREFIX_MAX_LENGTH', 5);
--SUBSTRING_INDEX，提高左通配符或双通配符查询的效率的
ctx_ddl.set_attribute('mywordlist','SUBSTRING_INDEX', 'YES');
--STEMMER，同词根查询的语言
ctx_ddl.set_attribute('mywordlist', 'STEMMER', 'ENGLISH');
--FUZZY_MATCH，设置模糊查询的语言
ctx_ddl.set_attribute('mywordlist', 'FUZZY_MATCH','ENGLISH');
END;

--浏览自己创造的preference
SELECT * FROM ctx_user_preferences ;

--创建索引，如果不显示的指定索引参数，系统会自动探测文本语言,数据类型和文档格式
CREATE INDEX myindex ON EMPLOREE(first_name) INDEXTYPE ISCTXSYS.Context;

--定期同步索引（同步索引为增量同步）
--context这种类型的索引需要手工同步
begin
 ctx_ddl.sync_index('myindex');
end;

 

--定期优化索引
 begin
 ctx_ddl.optimize_index('myindex','FULL');
 end;

--查询数据
--实验检索英文,半角、全角、大小写都可以正确识别出来
select * from test where contains(SKU,'你好')>0;
ID             CONTENT       CREATEDATE
------------------------------------------
1                你好          null

select * from test where contains(SKU,'www')>0;
ID             CONTENT       CREATEDATE
------------------------------------------
3                www          null

select * from test where contains(SKU,'HELLO')>0;
ID             CONTENT       CREATEDATE
------------------------------------------
4                HELLO          null

 

--查看解析的情况
21:54:04 SQL> select token_text,token_count fromdr$indx_texttest$i;
TOKEN_TEXT                                    TOKEN_COUNT
----------------------------------------------------------
www                                               1
hello                                             1
哈哈                                              1
你好                                              1