配置全文檢索

查詢中經常遇到類似這個的情況：

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:oracleora92ctxadmindefaultsdrdefus> @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.

全文檢索的缺點是不能實時保證查詢的正確性，所以要合理地定期同步索引。