oracle 全文索引探索

最新推荐文章于 2025-03-18 13:37:49 发布

timkey-json

最新推荐文章于 2025-03-18 13:37:49 发布

阅读量538

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/qq_22994765/article/details/106906697

版权

oracle 专栏收录该内容

1 篇文章

订阅专栏

ORACLE TEXT

Oracle Text(oracle全文索引)为查询应用程序和文档分类应用程序中的文本提供索引，单词和主题搜索以及查看功能。常见的索引类型如下：

索引类型	描述	支持的首选项和参数	查询运算符	备注
CONTEXT	当文本由大型，连贯的文档（例如，MS Word，HTML或纯文本）组成时，请使用此索引来构建文本检索应用程序。此索引类型要求`CTX_DDL.SYNC_INDEX`在对基表进行插入，更新和删除操作之后	CREATEINDEX`支持所有首选项和参数，除了`INDEXSET.支持的参数：索引分区子句格式，字符集和语言列	CONTAINS该`CONTEXT`语法支持一组丰富的操作。将`CTXCAT`语法与查询模板一起使用。	支持所有文档服务和查询服务。支持分区文本表的索引。支持`FILTER` `BY`和的`ORDER` `BY`子句为`CREATE` `INDEX`结构化的列值建立索引，以更有效地处理混合查询。
CTXCAT	使用此索引可以更好地混合小型文档和文本片段的查询性能。为了提高混合查询的性能，请在基表中包括其他列，例如项目名称，价格和描述。此索引类型是事务性的。在插入，更新或删除基表之后，它会自动更新自己。`CTX_DDL.SYNC_INDEX`没有必要。	INDEXSET LEXER	STOPLIST	STORAGE
CTXRULE	使用此索引来构建文档分类或路由应用程序。在查询表上创建此索引，查询在其中定义分类或路由条件	请参考https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/classifying-documents-in-oracle-text.html#GUID-247843B1-57A0-429E-8656-E7DCE4C2B299	MATCHES	使用`MATCHES`运算符对单个文档进行分类（纯文本，HTML或XML）。`MATCHES`将文档转换为一组查询，并在索引中找到匹配的行。

用户权限

oracle text的使用需要有特定的角色（CTXAPP）及权限(包括执行包执行)

1.创建用户

CREATE USER timkey IDENTIFIED BY 123456;

2.向用户授予角色

GRANT RESOURCE, CONNECT, CTXAPP TO timkey;

3.在CTX PL/SQL程序包上授予EXECUTE特权

#DDL 是需要的，其他按需添加
GRANT EXECUTE ON CTXSYS.CTX_CLS TO timkey;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO timkey;
GRANT EXECUTE ON CTXSYS.CTX_DOC TO timkey;
GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO timkey;
GRANT EXECUTE ON CTXSYS.CTX_QUERY TO timkey;
GRANT EXECUTE ON CTXSYS.CTX_REPORT TO timkey;
GRANT EXECUTE ON CTXSYS.CTX_THES TO timkey;
GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO timkey;

索引操作

以下是你可能必须知道的基础点；

查看索引错误

有时，索引操作可能会失败或可能无法成功完成。当系统在行索引编制过程中遇到错误时，会将错误记录在Oracle Text视图中。您可以使用以下命令查看索引错误 CTX_USER_INDEX_ERRORS.在所有指数为查看错误CTXSYS与CTX_INDEX_ERRORS.

SELECT err_timestamp, err_text FROM ctx_user_index_errors ORDER BY err_timestamp DESC;

删除索引

#正常删除
DROP INDEX yourindex; 
#非正常删除 无法确定索引状态（例如，由于索引故障）
DROP INDEX yourindex FORCE;

恢复失败的索引

#在yourindex  10 MB的内存上恢复索引操作
ALTER INDEX yourindex REBUILD PARAMETERS('resume memory 10M');

重建索引

您可以使用“ ALTER INDEX重建索引”来重建有效索引，该索引不允许更改大多数索引设置。要使用新的首选项建立索引时，可以重建索引。通常，重建索引比删除索引并使用CREATE INDEX语句重新创建没有优势。

#重建索引并将lexer首选项替换为 my_lexer:
ALTER INDEX newsindex REBUILD PARAMETERS('replace lexer my_lexer');

首选项操作

#创建
ctx_ddl.create_preference('my_lexer');

#删除
ctx_ddl.drop_preference('my_lexer');

查看待处理的DML操作

在基表中插入或更新文档时，它们的行ID保留在DML队列中，直到您同步索引。您可以在视图中查看此队列CTX_USER_PENDING。

SELECT pnd_index_name, pnd_rowid, to_char(
        pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss'
        ) timestamp FROM ctx_user_pending;

索引同步

ctx_ddl.sync_index('yourindex', '2M');

索引查询

常用索引类型使用example

CONTAINS（CONTEXT）

SELECT SCORE(1), title from news WHERE CONTAINS(text, 'oracle', 1) > 0;
#排序
SELECT SCORE(1), title from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0
           ORDER BY SCORE(1) DESC;
#结构化复杂查询
SELECT SCORE(1), title, issue_date from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0
           AND issue_date >= ('01-OCT-97') 
           ORDER BY SCORE(1) DESC;

CATSEARCH(CTXCAT)

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'order by bid_close desc')> 0;

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'category_id=99 order by bid_close desc')> 0;

MATCHES(CTXRULE)

SELECT classification FROM querytable WHERE MATCHES(query_string,:doc_text) > 0;

索引表说明

创建全文索引时会基于基础表产生其他关于索引的表，常见的有以下：

表格式	表名称	表描述	备注
DR$indexname$I	索引令牌表	包含令牌列表（`TOKEN_TEXT`列）以及有关令牌出现的行和单词位置的信息（`TOKEN_INFO`列）
DR$indexname$K	索引组织表	为每个docid / rowid对包含一行	功能查询
DR$indexname$R	rowid列表	该表在BLOB列中包含完整的rowid列表	索引查找

CONTEXT索引的栗子

#1创建表
create table mytable(id number primary key,column1 VARCHAR2(200) ,column2 VARCHAR2(200),column3 VARCHAR2(400));
#插入测试数据
insert into mytable values(1,'张三','北京','张三是一个软件工程师');
insert into mytable values(2,'李四','北京','李四是一个产品经理');
insert into mytable values(3,'王五','上海','王五是一个测试工程师');
insert into mytable values(4,'唐六','深圳','唐六是一个项目经理');
#设置数据存储区 
exec ctx_ddl.create_preference('my_store', 'MULTI_COLUMN_DATASTORE');
exec ctx_ddl.set_attribute('my_store', 'columns', 'column1, column2, column3');
#设置过滤对象
exec ctx_ddl.create_preference('my_filter', 'NULL_FILTER');
#设置词分析器
exec ctx_ddl.create_preference('my_lexer', 'CHINESE_VGRAM_LEXER');
#创建索引
create index myindex on mytable(column3) indextype is ctxsys.context parameters('DATASTORE my_store filter my_filter lexer my_lexer');
#新增加一条记录
insert into mytable values(5,'唐六','深圳北','唐六是一个董事长');
#同步索引
 exec ctx_ddl.sync_index('myindex', '2M');
 ======context的索引不会自动更新，所以需要同步索引可用使用job 同步和优化================
 #同步索引
 1. create or replace procedure hsp_sync_index as
   begin
    ctx_ddl.sync_index('id_cont_msg');
   end;
  /
  2.VARIABLE jobno number; 
  3. BEGIN
     DBMS_JOB.SUBMIT(:jobno,'hsp_sync_index();',  
     SYSDATE, 'SYSDATE + (1/24/4)');  
     commit;  
     END;  
   /
   #优化索引任务
   1.create or replace procedure hsp_optimize_index as
      begin
       ctx_ddl.optimize_index('id_cont_msg','FULL');
       end;
     /
   2.VARIABLE jobno number;
   3. BEGIN
      DBMS_JOB.SUBMIT(:jobno,'hsp_optimize_index();',  
      SYSDATE, 'SYSDATE + 1');  
      commit; 
      END;
     /

spring+mybatis+oracle text （context）示例

########controller############
@RestController
@RequestMapping("/test")
public class TestController {
    @Autowired
    private TestService  service;

    @GetMapping("/query")
    public Object test(@RequestParam("key")String key){
        return service.search(key);
    }
}
############Service############
@Service
public class TestService {

    @Autowired
    private TestMapper testMapper;

    public List<Mytable> search(String key){
        return testMapper.queryIndex(key);
    }
}
############mapper############
public interface TestMapper extends Mapper<Mytable> {
    List<Mytable> queryIndex(String key);
}
############mapper.xml############
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="org.timkey.oracle.text.mapper.TestMapper">
    <select id="queryIndex" resultType="org.timkey.oracle.text.entity.Mytable">
        select * from mytable where contains(column3,#{key})>0
    </select>
</mapper>

CTXCAT索引的栗子

#1创建表
create table test(
     id number,
     column1 varchar2(100),
     column2 VARCHAR2(200),
     column3 VARCHAR2(400)
     orderNo number,
     testDate date);
#插入测试数据
insert into test values(1,'张三','开发部','软件工程师','1000000001','24-OCT-2019');
insert into test values(2,'李四','产品部','产品经理','1000000002','25-OCT-2019');
insert into test values(3,'王五','测试部','测试工程师','1000000003','26-OCT-2019');
insert into test values(4,'唐六','项目部','项目经理','1000000004','27-OCT-2019');
#设置词分析器
exec ctx_ddl.create_preference('my_lexer', 'CHINESE_VGRAM_LEXER');
#设置索引集
exec ctx_ddl.create_index_set('ctx_iset');
exec ctx_ddl.add_index('ctx_iset','column1,column2');
exec ctx_ddl.add_index('ctx_iset','number,testDate');
#创建索引
create index testindex on test(column3) indextype is ctxsys.ctxcat parameters('index set ctx_iset  lexer my_lexer');
#查询示例
select * from test where catsearch(column3,'经理','order by testDate desc')>0;