Oracle全文索引使用方法

接近千萬資料表中有個name欄位，存放中文名稱，現在需要根據name欄位模糊比對查詢資料，一般的做法是對name欄位建立b*tree索引，查詢用like'%name%'，但是經過測試，這種寫法沒有走索引，而是全表掃描，可想而知效能有問題。所以採用了Oracle提供的全文索引技術，下面詳細介紹下如何使用全文索引。

由Oracle索引來理解ArcSDE索引

Oracle索引技術之如何建立最佳索引

Oracle索引列NULL值引發執行計畫該表的測試樣本

Oracle索引 主鍵影響查詢速度

Oracle索引掃描

1.使用前提

1) 檢查資料庫中是否有CTXSYS使用者(從dba_users)和CTXAPP角色(dba_roles)。如果沒有這個使用者和角色，意味著你的資料庫建立時未安裝intermedia功能。你必須修改資料庫以安裝這項功能。

2) 把CTXAPP角色賦於目前使用者： GRANT CTXAPP TO 目前使用者;

3) 把CTX_DDL的執行許可權賦於目前使用者： GRANT EXECUTE ON CTX_DDL TO 目前使用者;

2.建立分析器

oracle text的分析器，將需要檢索的記錄，按照一定的方式進行片語拆分，然後存放在索引表中。檢索的時候根據索引表中存放的拆分片語，對傳入的關鍵字進行匹配，並返回匹配結果。

oracle text中的分析器有3種：

1) basic_lexer：針對英文，只能根據空格和標點來進行拆分。比如“中國深圳”，只能拆分為“中國深圳”一個詞，根據“中國”或者“深圳”就搜尋不到。

2) chinese_vgram_lexer：專門的漢語分析器，按字單元進行拆分，比如“中國深圳”，可以拆分為“中”、“中國“、”國深”、“深圳”、“圳”五個片語。這種方式的好處是能夠將所有有可能的片語全部儲存進索引表，使得資料不會遺漏，但是效率上來說就差強人意了。

3) chinese_lexer：一種新的漢語分析器，能夠認識大部分常用的漢語詞彙，較與chinese_vgram_lexer機械式的拆分，能夠按常用詞彙進行拆分儲存。比如“中國深圳”，只會被拆分為“中國”、“深圳”兩個片語。

具體文法為：

ctx_ddl.drop_preference('testlex');--刪除

ctx_ddl.create_preference('testlex','CHINESE_LEXER');--建立一個“CHINESE_LEXER”分析器，名稱為“testlex”

3.建立過濾片語

假設根據公司名稱進行檢索，一般情況下我們不希望，當輸入“公司”、“股份公司”、“有限公司”等能夠檢索出來結果，所以需要對這些關鍵字進行過濾。當然如果沒有這種特殊的需求，可以不用建立過濾片語的。

1) 建立一個過濾器，名稱為“teststoplist”

exec ctx_ddl.create_stoplist('teststoplist');

2) 添加自訂需要過濾的片語，以下內容的意思是“公司”、“股份公司”、“有限公司”這些片語不會建立索引，不會被檢索到。

ctx_ddl.add_stopword('teststoplist','有限公司');

ctx_ddl.add_stopword('teststoplist','公司');

ctx_ddl.add_stopword('teststoplist','股份公司');

4.建立索引

create index idx_test  on test(name) indextype is CTXSYS.CONTEXT parameters('lexertestlex stoplistteststoplist');

以上語句的意思是在test表name欄位上建立全文索引，索引類型為CTXSYS.CONTEXT，用到了上面建立的分析器“testlex”、過濾片語“teststoplist”。

建立完索引之後，可以看到產生了如下幾個表：dr$idx_test$i;dr$idx_test$k;dr$idx_test$n;dr$idx_test$r;其中dr$idx_test$i表存放的就是name欄位分詞後的資料。

5.使用索引

oracle全文索引使用時，sql語句必須使用contains關鍵字，具體如下：

select * from test where contains(name,'深圳') > 0;

如果需要根據匹配程度來排序，如下：

select score(1),t.* from test t where contains(name,'深圳',1)>0 order by score(1) desc;

6.索引維護

對錶的dml操作是不會更新全文索引的，這個也是全文索引的一個特點，索引我們必須手動去維護索引，具體寫法如下：

exec ctx_ddl.sync_index('IDX_TEST');--同步索引，將新的資料同步到索引

exec ctx_ddl.optimize_index('IDX_TEST','FULL');--最佳化索引，清楚已刪除的資料

總結：Oracle全文索引是一種用空間換取時間的一種技術，再加上dml操作不能自動同步到索引，需要手動定期維護，所以適用情境也不是很廣泛。因此，選擇使用時請謹慎。