oracle 全文检索

使用 like '%   %'，oracle会进行全表扫描，相当耗费系统资源，这可以使用oracle的全文检索来提高查询速度。

SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> create table t (text varchar(4000));

表已创建。

SQL> insert into t values('你那么爱她，为什么不把她留下');

已创建 1 行。

SQL> begin ctx_ddl.create_preference('club_lexer','chinese_vgram_lexer'); end
  2  /

PL/SQL 过程已成功完成。

SQL> create index idx_t_text on t(text) indextype is
  2  ctxsys.context parameters('lexer club_lexer');

索引已创建。

SQL> select count(*) from t where contains(text,'什么')>0;
select count(*) from t where contains(text,'什么')>0
*
第 1 行出现错误:
ORA-29902: 执行 ODCIIndexStart() 例行程序中出错
ORA-20000: Oracle Text error:
DRG-10817: CONTAINS  搜索词包含禁用词或禁用词的词组: 什么


SQL> select count(*) from t where contains(text,'为什么')>0;

  COUNT(*)
----------
         1

SQL> select count(*) from t where contains(text,'她')>0;

  COUNT(*)
----------
         1

SQL> select count(*) from t where contains(text,'那么')>0;

  COUNT(*)
----------
         1

定期同步优化索引

SQL> create or replace procedure hsp_sync_index as
  2   begin
  3      ctx_ddl.sync_index('idx_t_text');
  4   end;
  5   /

过程已创建。

SQL> --定期同步索引
SQL> VARIABLE jobno number;
SQL>  BEGIN
  2      DBMS_JOB.SUBMIT(:jobno,'hsp_sync_index();',
  3      SYSDATE, 'SYSDATE + (1/24/4)');
  4      commit;
  5   END;
  6   /

PL/SQL 过程已成功完成。

SQL> create or replace procedure hsp_optimize_index as
  2      begin
  3      ctx_ddl.optimize_index('idx_t_text','FULL');
  4  end;
  5  /

过程已创建。

SQL> --定期优化索引
SQL> VARIABLE jobno number;
SQL> BEGIN
  2      DBMS_JOB.SUBMIT(:jobno,'hsp_optimize_index();',
  3      SYSDATE, 'SYSDATE + 1');
  4      commit;
  5  END;
  6  /

PL/SQL 过程已成功完成。

在创建同步与优化存储过程前，要使用sys用户为evan用户赋权：grant execute on ctx_ddl to evan;
不然会报：
SQL> create or replace procedure hsp_sync_index as
  2   begin
  3      ctx_ddl.sync_index('idx_t_text');
  4   end;
  5   /
警告: 创建的过程带有编译错误。
SQL> show errors
PROCEDURE HSP_SYNC_INDEX 出现错误:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5      PL/SQL: Statement ignored
3/5      PLS-00201: 必须声明标识符 'CTX_DDL'
此外还要格外注意一下禁用词。