oracle 全文索引探索_oracle ctxsys.context

ORACLE TEXT

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

更多相关信息请查看：https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/index.html

用户权限

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;

索引表说明

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

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;