记一次在oracle数据库中添加全文索引的过程

前景：

在创建全文索引之前，有如下几个内容需要确认下：

1.全文索引不支持nvarchar2类型，所以需要创建全文索引的字段的字段类型，如果是这个类型，可以修改为varchar2()；

2.如果需要创建全文索引的字段包含 (1、中文、英文结合 ， 或者纯英文；2、中文、数字结合，或者纯数字的)这几种情况的，需要对字段单独处理，就比如zjhm字段，我们可以添加一个新的字段zjhm1来存储处理过的zjhm，然后对zjhm1创建全文索引；处理的过程在附件函数中；

一、创建全文索引

1.开启全文索引查询权限

ALTER USER 用户名 ACCOUNT UNLOCK;

2.使用sys用户以dba权限登录，授予执行刷新全文检索的权限  （注意：此步骤不操作，后面的刷新的存储过程编译报错）

GRANT EXECUTE ON ctx_ddl TO 用户名;

3.创建文本解析器bdcdj_lexer，默认使用chinese_vgram_lexer参数。(bdcdj_lexer为自定义)

BEGIN
ctx_ddl.create_preference ('bdcdj_lexer', 'chinese_vgram_lexer');
END;

或者

call ctx_ddl.create_preference ('bdcdj_lexer', 'chinese_vgram_lexer');

4.创建对应的表字段全文索引。

CREATE INDEX ier_ztt_gy_qlr_qlrmc ON ztt_gy_qlr(qlrmc) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER jgxt_lexer SYNC (ON COMMIT)');

CREATE INDEX ier_ztt_gy_qlr_zjh1 ON ztt_gy_qlr(zjh1) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER jgxt_lexer SYNC (ON COMMIT)');

5.验证表中的全文索引是否创建成功

select * from bdc_regn_zb where contains(zl,'云岩区金')>0 

---------
删除索引：
drop index ier_bdc_fwsyq_bdcqzh force;

二、添加触发器来完成新增的字段的插入操作

比如：

create or replace trigger "tri_ztt_gy_qlr"
before insert or update on ztt_gy_qlr
for each row
begin
  
  if inserting then
       :new.zjh1 :=pro_add_split(:new.zjh);
    end if;

  if updating  then
         :new.zjh1 :=pro_add_split(:new.zjh);
  end if;
end;

三、添加刷新全文索引存储过程 

1、使用查询语句获得库中所有的全文索引，并拼接刷新语句，用于存储过程。
select 'ctx_ddl.optimize_index('||''''||index_name||''''||','||''''||'full'||''''||');' as sql from user_indexes where index_type='DOMAIN' order by table_name

2、创建存储过程

create or replace procedure pro_optimize_index authid current_user is
begin
    begin
/*示例：
ctx_ddl.optimize_index('IER_BDC_QLRLB_ZJHM2','full')。
具体执行内容从上面的查询语句结果中摘取。
*/
end;
end;

3、创建定时任务，调用存储过程，每天夜间03：00定时刷新操作。

declare
IER_INDEX_job number;
begin
    IER_INDEX_job:=1083;
    dbms_job.isubmit(IER_INDEX_job,'begin pro_optimize_index();end;',trunc(sysdate+1)+3/24,'sysdate+1');
commit;
end;

4、查询定时任务创建成果与否。
select job,schema_user,last_date,next_date,interval,what from user_jobs where what like '%pro_opt%'

select * from dba_jobs where what like '%pro_optimize_index%'