/*-----表名 “tablename”---字段 “name”---数据库用户名:dbname ---索引名称:i_table_name-----*/
1-- 检查和设置数据库角色,system用户登录后查询ctxsys状态是否open ※※※※※※※
select username,account_status from dba_users where username like 'CTX%';
修改ctxsys用户状态为open ※※※※※※※
alter user ctxsys identified by oracle account unlock;
再次查询ctxsys用户状态确认open状态
select username,account_status from dba_users where username like 'CTX%';
在DBA用户权限下,将 CTX_DDL 使用权限付给 dbname 用户 ※※※※※※※
GRANT EXECUTE ON ctx_ddl TO dbname;
2-- 以下操作均使用 dbname 用户进行操作
查询是否存在索引
i_table_name SELECT * FROM user_indexes t WHERE t.index_name='i_table_name';
去除已经存在的索引
DROP INDEX i_table_name;
3-- 设置全文索引参数 dbname 指的是用户名 解决单个中文字查询问题 ※※※※※※※
BEGIN
ctx_ddl.drop_preference ('dbname.CHINESE_LEXER');
ctx_ddl.create_preference ('dbname.CHINESE_LEXER', 'CHINESE_VGRAM_LEXER');
ctx_ddl.drop_preference ('dbname.ChineseWordList');
ctx_ddl.create_preference('dbname.ChineseWordList', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('dbname.ChineseWordList','PREFIX_INDEX','TRUE'); ctx_ddl.set_attribute('dbname.ChineseWordList','PREFIX_MIN_LENGTH',1); ctx_ddl.set_attribute('dbname.ChineseWordList','PREFIX_MAX_LENGTH', 5); ctx_ddl.set_attribute('dbname.ChineseWordList','SUBSTRING_INDEX', 'YES');
END;
4-- 查看全文索引参数
SELECT * FROM ctx_preference_values ORDER BY 1;
SELECT * FROM ctx_preferences ORDER BY 1;
5-- 建立 context 类型的全文索引 ※※※※※※※
Create INDEX i_table_name on tablename(name) indextype is ctxsys.CONTEXT parameters(
'lexer dbname.chinese_lexer wordlist dbname.chinesewordlist');
6-- 全文索引查询语句contains
SELECT COUNT(*) FROM tablename t WHERE contains(t.name,'农业银行 & 公司')>0;
SELECT * FROM tablename t WHERE contains(t.name,'农业 & 安徽 & 中 & 部')>0;
SELECT * FROM tablename t WHERE contains(t.name,'( 建设 | 农业 ) & 安徽 & 中国')>0;
7-- 同步、优化 全文索引i_table_name ,10W数据,执行时间小于1分钟
可以将此执行语句放到 Oracle 定时任务中每天定时执行 ※※※※※※※
BEGIN
ctx_ddl.sync_index('i_table_name');
ctx_ddl.optimize_index('i_table_name','full');
END;
8-- 或建立同步存储过程,以便定时调用
CREATE OR REPLACE PROCEDURE SYNC_CNCOMMBANKINFO_TEXTINDEX AS
BEGIN
CTX_DDL.SYNC_INDEX('i_table_name');
CTX_DDL.OPTIMIZE_INDEX('i_table_name', 'full');
END SYNC_CNCOMMBANKINFO_TEXTINDEX;
9-- 查询当前用户的定时任务列表
SELECT * FROM USER_JOBS;
每天凌晨 5 点执行 sync_cncommbankinfo_textindex 数据同步存储过程
DECLARE V_JOB_NUM NUMBER;
BEGIN
DBMS_JOB.SUBMIT(V_JOB_NUM,'sync_cncommbankinfo_textindex;',SYSDATE,'TRUNC(SYSDATE) + 1 + 5 / 24');
END;
10--删除指定编号的 JOB
begin
dbms_job.remove(24);
end;