日期:2014-05-16  浏览次数:20690 次

Oracle Text 全文索引 几种典型应用场景

一、普通查询

这是OracleText比较普遍且常用的应用场景。创建OracleText中Context类型的索引,生成大量的关键词,用于加快类似于普通的like ‘%xx%’操作速度,或者查询一些比较大的文档。可以使用contains函数进行数据检索。缺点:比较依赖于关键词和文档格式。有时可能不太准确。

以下为简单示例,这里不必多说,只是简单查询场景:

1.创建表

create table normal_context( id number , contentclob ) ;


2.初始化数据

insert into normal_context values(1 , ' Use thisindex to build a
                                  textretrieval application
                                  when your text consists
                                  of largecoherent
                                  documents.You can
                                  indexdocuments of
                                  differentformats such as
                                  MS Word, HTMLor
                                  plain text.
                                  You cancustomize the
                                  index in avariety of
                                  ways.
                                  This index typerequires
                                 CTX_DDL.SYNC_INDEX
                                  after DML onbase table.') ;
insert into normal_context values(2 ,'Use thisindex type for
                                better mixed query
                                performance.Typically,
                                with this indextype,
                                you index small
                                documents ortext
                               fragments.Other
                                columns in thebase
                                table, such asitem
                                names, prices,and
                                descriptionscan be
                                included in the indexto
                                improve mixedquery
                                performance.
                                This index typeis
                                transactional,
                                automaticallyupdating
                                itself afterDML to base
                                table. No CTX_
                               DDL.SYNC_INDEXis
                                necessary.') ;
 
 
insert into normal_context values(3 , ' UseCTXRULEindex to
                                  build adocument
                                 classification or routing
                                  application.This index
                                  is created on a table of
                                  queries,where the
                                  queriesdefine the
                                 classification or routing
                                  criteria.') ;
insert into normal_context values(4 , ' Createthis index when
                                  you need tospeed up
                                 existsNode()queries
                                  on anXMLTypecolumn.') ;
 


3.初始化索引

CREATE INDEX idx_normal_content ONnormal_context(content) INDEXTYPE IS CTXSYS.CONTEXT ;

3.1. context类型的全文索引在基表数据更改的时候需要使用下面的命令同步全文索引

exec CTX_DDL.SYNC_INDEX('idx_normal_content') ;


4.查询

_dexter@FAKE> SELECT SCORE(1), id, contentFROM normal_context WHERE CONTAINS(content, 'classification', 1) > 0;
 
 SCORE(1)         ID CONTENT
---------- ------------------------------------------------------
       10          3  Use CTXRULEindex to