配置全文检索

查询中经常遇到类似这个的情况：

select * from t where t.name like '%a%';

其中a是变量。像这种情况是不会走索引的，如果表很大，这将是非常耗费资源的。

优化这个查询有两种方法：

1、拆分name

2、全文检索

其中：拆分name的方法需要修改表结构，或者需要新建表，且不同的业务下拆分是很困难的，甚至有些业务逻辑下无法进行拆分处理。幸好，oracle给我们提供了全文检索这个特性。

一、----------------- 安装全文检索组件---------------------

1、建立ctxsys用户需要的表空间（如果需要用已存在的表空间，这一步可以忽略）
以sys用户执行
SQL> create tablespace ts_text datafile 'E:ORACLEORADATAORACLE9Its_text01.dbf' size 100m;

Tablespace created

2、建立sysctx用户
以sys用户执行
SQL>@?ctxadmindr0csys
需要输入输入三个参数，分别设置ctxsys用户的密码、表空间名称、临时表空间名称
SQL> @E:oracleora92ctxadmindr0csys
...creating user CTXSYS
...creating role CTXAPP

3、安装全文检索组件
以ctxsys用户执行
SQL>@E:oracleora92ctxadmindr0inst
需要一个输入参数：E:oracleora92ctxliblibctxx9.so
.......
... creating objects
Removing old object definitions...
Creating new object definitions...
...creating default preferences
Create default preferences
System Parameters
========================================================

4、设置一些必要的默认值
以ctxsys用户执行
SQL>@E:oracleora92ctxadmindefaultsdrdefusctxsys@oracle9i> @E:oracleora92ctxadmindefaultsdrdefus
Creating lexer preference...
Creating wordlist preference...
Creating stoplist...
Creating default policy...

二、--------------使用全文检索-----------------------------
1、授权
以sys登录
grant ctxapp to suk;
以ctxsys登录
grant execute on ctx_ddl to suk;

2、建立索引
以suk连接
exec ctx_ddl.create_preference('suk_lexer','CHINESE_LEXER');
--其中：第一个参数可以随便命名，第二个参数必须是oracle规定的几个值之一。
--第二个参数可以是CHINESE_LEXER、chinese_vgram_lexer；CHINESE_LEXER建索引时间长、但查询速度、查询准确度都比chinese_vgram_lexer高

create index idx_item_sku on item ( sku ) indextype is ctxsys.context parameters('lexer suk_lexer');
--parameters括号中都第一个值lexer是固定的，第二个值是上一步建立的preference名称

--查询数据
select SKU from ITEM
where ITEM_TYPE='p'
and contains(SKU,'理光')>0
and contains(SKU,'Caplio')>0
and contains(SKU,'R1v')>0;

3、定期同步索引（同步索引为增量同步）
--context这种类型的索引需要手工同步
SQL> create or replace procedure souchang_sync_index as
2 begin
3 ctx_ddl.sync_index('idx_item_sku');
4 end;
5 /

Procedure created.

Elapsed: 00:00:00.08
SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'souchang_sync_index();',
3 SYSDATE, 'SYSDATE + (1/24/4)');
4 commit;
5 END;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27

4、定期优化索引
SQL> create or replace procedure souchang_optimize_index as
2 begin
3 ctx_ddl.optimize_index('idx_item_sku','FULL');
4 end;
5 /

SQL> VARIABLE jobno number;
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:jobno,'souchang_optimize_index();',
3 SYSDATE, 'SYSDATE + 1');
4 commit;
5 END;
6 /
Procedure created.

全文检索的缺点是不能实时保证查询的正确性，所以要合理地定期同步索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63711/，如需转载，请注明出处，否则将追究法律责任。