oracle全文检索功能

在对海量的文本数据进行搜索时，建议使用全文检索功能。
创建全文索引环境

1.在sysdba权限下验证是否有ctxsys用户和ctxapp角色：
sql>select username from all_users;
如果没有该用户，则需要打开dbca工具中选择configrue database options，然后选择所有数据库组件安装即可。

2.用在sysdba权限下或ctxsys用户下授予prt用户执行ctx_ddl的权限
sql>grant execute on ctx_ddl to prt;

3.prt用户下创建分词
sql>conn prt/prt
sql>exec ctx_ddl.create_preference ('PRT_CHINESE_LEXER', 'chinese_vgram_lexer');

4.在某个字段上创建全文索引
create index idx_ctx_movie_desc on movie(desc) indextype is ctxsys.context parameters('LEXER PRT_CHINES_LEXER');

5.定期同步优化全文索引

/*索引建好后，由于对表所发生的任何DML语句，都不会自动修改索引，因此，必须定时同步(sync)和优化(optimize)索引，以正确反映数据的变化，所以要定期同步和优化全文索引。*/
create or replace procedure p_lexer_movie is
begin
 ctx_ddl.sync_index('IDX_CTX_MOVIE_DESC');
 ctx_ddl.optimize_index('IDX_CTX_MOVIE_DESC','FULL');
end;

var i number;
begin
dbms_job.submit(:j,'p_lexer_movie;',sysdate,'sysdate + 1/48');
commit;
end;
/

全文索引的作用包括：1.提高查询速度，2.切词，3.针对所有数据类型（包括blob等）进行全文检索，4.可以对超过150种文件类型（如doc，txt，pdf，xml）进行检索

1.提高查询速度
select * from movie where containts(desc,'探险')>0;
查看执行计划走的是domain index:IDX_CTX_MOVIE_DESC

在数据量很大的情况下比如下sql速度提高n倍
select * from movie where instr(desc,'探险')>0;

经过测试，在5600万条记录的表中，查询出符合条件的117条记录，用全文索引只需要2.391s，而用第二种方法，则需要99.515s才能出结果。

2.实现切词功能（yangtingkun）：
create or replace function f_split_chinese(as_input in varchar2)
  return varchar2 as
  pragma autonomous_transaction;
  v_return varchar2(32767);
begin
  begin
    execute immediate 'drop table t_temp_table purge';
  exception
    when others then
      null;
  end;

  execute immediate 'create table t_temp_table (str varchar2(4000))';
  execute immediate 'insert into t_temp_table values (:1)'
    using as_input;
  execute immediate 'create index ind_t_temp_table_str on t_temp_table(str) indextype is ctxsys.context
                            parameters(''lexer SMS_CHINESE_LEXER'')';
  execute immediate 'select max(ltrim(sys_connect_by_path(token_text, '',''), '',''))
                      from
                      (
                      select token_text, row_number() over(order by token_text) rn
                      from dr$ind_t_temp_table_str$i
                      )
                      start with rn = 1
                      connect by prior rn + 1 = rn'
    into v_return;
  execute immediate 'drop table t_temp_table purge';
  return v_return;
end;

SQL> select f_split_chinese('优化你的文本检索') from dual;
f_split_chinese('优化你的文本?
-----------------------------------------
本检,的文,化你,检索,你的,索,文本,优化

sql>exec ctx_ddl.drop_preference('PRT_CHINESE_LEXER');
sql>exec ctx_ddl.create_preference('PRT_CHINESE_LEXER', 'CHINESE_LEXER');

SQL> select f_split_chinese('优化你的文本检索') from dual;
F_SPLIT_CHINESE('优化你的文本?
---------------------------------
检索,你的,文本,优化