Oracle全文索引技術(shù)-使用說明文檔

1、Oracle全文索引技術(shù)使用說明文檔金聯(lián)萬家(北京)電子支付科技發(fā)展有限公司文檔編號保 密 等 級機(jī)密作 者宋成最后修改日期審 核 人最后審批日期批 準(zhǔn) 人最后批準(zhǔn)日期修改記錄日期版本修改人備注2013-03-20V1.0宋成初稿目錄1 前言31.1 編寫目的31.2 名詞解釋31.3 參考資料32 oracle全文索引技術(shù)43 建立全文索引的操作步驟43.1 查看用戶與角色43.2 開啟目標(biāo)用戶全文索引權(quán)限43.3 設(shè)置語法分析器Lexer53.4 建立索引53.5 使用索引64 全文索引的種類65 對多字段建立索引66 Oracle全文索引之 - CONTEXT76.1 函數(shù) CONTAI

2、NS 語法76.2 全文索引的維護(hù)76.2.1 同步數(shù)據(jù)86.3 單個中文字查詢問題97 建立全文索引的完整腳本108 附錄141 前言1.1 編寫目的本文檔主要說明Oracle全文索引技術(shù)的使用與維護(hù)，為數(shù)據(jù)庫操作使用人員提供參考維護(hù)手冊。1.2 名詞解釋名詞解釋表 1. 術(shù)語表1.3 參考資料資料名說明表 2. 參考資料列表2 oracle全文索引技術(shù)全文檢索：是指計算機(jī)索引程序通過掃描文章中的每一個詞，對每一個詞建立一個索引，指明該詞在文章中出現(xiàn)的次數(shù)和位置，當(dāng)用戶查詢時，檢索程序就根據(jù)事先建立的索引進(jìn)行查找，并將查找的結(jié)果反饋給用戶的檢索方式。這個過程類似于通過字典中的檢索字表查字的過

3、程。【Oracle Text 全文檢索】Oracle一直致力于全文檢索技術(shù)的研究，當(dāng)Oracle9i Rlease2發(fā)布之時，Oracle數(shù)據(jù)庫的全文檢索技術(shù)已經(jīng)非常完美，Oracle Text使Oracle9i具備了強(qiáng)大的文本檢索能力和智能化的文本管理能力。Oracle Text是Oracle9i采用的新名稱，在Oracle8/8i中它被稱作Oracle interMedia Text，在Oracle8以前它的名稱是Oracle ConText Cartridge。使用Oracle9i和Oracle Text，可以方便而有效地利用標(biāo)準(zhǔn)的SQL工具來構(gòu)建基于文本的新的開發(fā)工具或?qū)ΜF(xiàn)有應(yīng)用程序進(jìn)

4、行擴(kuò)展。應(yīng)用程序開發(fā)人員可以在任何使用文本的Oracle數(shù)據(jù)庫應(yīng)用程序中充分利用Oracle Text搜索，應(yīng)用范圍可以是現(xiàn)有應(yīng)用程序中可搜索的注釋字段，也可是實現(xiàn)涉及多種文檔格式和復(fù)雜搜索標(biāo)準(zhǔn)的大型文檔管理系統(tǒng)。Oracle Text支持Oracle數(shù)據(jù)庫所支持的大多數(shù)語言的基本全文搜索功能。擴(kuò)展閱讀： 3 建立全文索引的操作步驟3.1 查看用戶與角色檢查數(shù)據(jù)庫中是否有 CTXSYS 用戶 和 CTXAPP 角色如果沒有這個用戶和角色，意味著你的數(shù)據(jù)庫創(chuàng)建時未安裝intermedia功能。你必須修改數(shù)據(jù)庫以安裝這項功能。默認(rèn)安裝情況下，ctxsys用戶是被鎖定的，因此要先啟用ctxsys的用

5、戶。 - 鎖住用戶ALTER USER ctxsys ACCOUNT LOCK PASSWORD EXPIRE;- 解鎖用戶ALTER USER ctxsys ACCOUNT UNLOCK IDENTIFIED BY ctxsys;如果沒有該用戶，則需要打開dbca工具中選擇configrue database options，然后選擇所有數(shù)據(jù)庫組件安裝即可。3.2 開啟目標(biāo)用戶全文索引權(quán)限賦權(quán)在ctxsys用戶下把ctx_ddl的執(zhí)行權(quán)限賦于要使用全文索引的用戶，例：GRANT EXECUTE ON ctx_ddl TO username;3.3 設(shè)置語法分析器LexerOracle實現(xiàn)全文

6、檢索，其機(jī)制其實很簡單。即通過Oracle專利的詞法分析器(lexer),將文章中所有的表意單元(Oracle 稱為 term)找出來，記錄在一組 以dr$開頭的表中，同時記下該term出現(xiàn)的位置、次數(shù)、hash 值等信息。檢索時，Oracle 從這組表中查找相應(yīng)的term，并計算其出現(xiàn)頻率，根據(jù)某個算法來計算每個文檔的得分(score),即所謂的匹配率。而lexer則是該機(jī)制的核心，它決定了全文檢索的效率。Oracle 針對不同的語言提供了不同的 lexer, 而我們通常能用到其中的三個：Ø basic_lexer ：針對英語（默認(rèn)分析器）Ø chinese_vgram_

7、lexer ：專門針對漢語，對漢字搜索準(zhǔn)確性較高如：中國人民站起來了這句話，會被它分析成如下幾個term: 中，中國，國人，人民，民站，站起，起來，來了，了Ø chinese_lexer ：新的漢語分析器，只支持 UTF8 字符集，可以將語句分詞成常見的詞組，能跟有效率的分析語句，對漢字解析搜索效率較高【指定語法分析器：】1. 當(dāng)前用戶下下建立一個preference(例：在pomoho用戶下執(zhí)行以下語句) EXEC ctx_ddl.create_preference('my_lexer','chinese_vgram_lexer');2.

8、在建立全文索引索引時，指明所用的lexer: CREATE INDEX myindex ON mytable(mycolumn) INDEXTYPE IS ctxsys.context PARAMETERS('lexer my_lexer');這樣建立的全文檢索索引，就會使用chinese_vgram_lexer作為分析器。 3.4 建立索引 通過以下語法建立全文索引 3.5 使用索引 使用全文索引很簡單，可以通過： SELECT * FROM PUBMENU WHERE CONTAINS(MENUNAME, '上傳圖片') > 0;4

9、 全文索引的種類建立的Oracle Text索引被稱為域索引(domain index)，包括4種索引類型： n CONTEXT 用于對含有大量連續(xù)文本數(shù)據(jù)進(jìn)行檢索。支持word、html、xml、text等很多數(shù)據(jù)格式。支持中文字符集，支持分區(qū)索引，唯一支持并行創(chuàng)建索引（Parallel indexing）的索引類型。對表進(jìn)行DML操作后，并不會自動同步索引。需要手工同步索引。查詢操作符：CONTAINSn CTXCAT 當(dāng)使用混合查詢語句的時候可以帶來很好的效率。適合于查詢較小的具有一定結(jié)構(gòu)的文本段。具有事務(wù)性，當(dāng)更新主表的時候自動同步索引。作符：CATSEARCHn CTXRULE 主要

10、用于文檔分類。 查詢操作符：MATCHESn CTXXPATH 快速查找XML文檔NODE節(jié)點XPATH路徑5 對多字段建立索引很多時候需要從多個文本字段中查詢滿足條件的記錄，這時就需要建立針對多個字段的全文索引，例如需要從pmhsubjects(專題表)的subjectname(專題名稱)和briefintro(簡介)上進(jìn)行全文檢索，則需要按以下步驟進(jìn)行操作： -建議多字段索引的preference ，以ctxsys登錄，并執(zhí)行EXEC ctx_ddl.create_preference('ctx_idx_subject_pref','MULTI_COLUMN_DAT

11、ASTORE');- 建立 preference 對應(yīng)的字段值（以ctxsys登陸）EXEC ctx_ddl.set_attribute('ctx_idx_subject_pref','columns','subjectname,briefintro');-建立全文索引CREATE INDEX ctx_idx_subject ON pmhsubjects(subjectname) INDEXTYPE IS ctxsys.context PARAMETERS('DATASTORE ctxsys.ctx_idx_subject_pre

12、f lexer my_lexer');-使用索引SELECT * FROM pmhsubjects WHERE contains(subjectname,'李宇春');6 Oracle全文索引之 - CONTEXTCONTEXT：用于對含有大量連續(xù)文本數(shù)據(jù)進(jìn)行檢索。支持word、html、xml、text等很多數(shù)據(jù)格式。支持中文字符集，支持分區(qū)索引，唯一支持并行創(chuàng)建索引（Parallel indexing）的索引類型。對表進(jìn)行DML操作后，并不會自動同步索引。需要手工同步索引。查詢操作符：CONTAINS6.1 函數(shù) CONTAINS 語法使用contains時，主要查

13、詢語法有：contains(列名稱,查詢關(guān)鍵字)Logical Operators：組合搜索條件，通過使用AND，OR等邏輯符號。n AND（&），同時含有所有關(guān)鍵詞，如：'cats AND dogs'，'cats & dogs'n OR（|），含有所有關(guān)鍵詞中的任意一個，如：'cats | dogs'，'cats OR dogs'sn NOT（），不含該關(guān)鍵詞，如：'animals dogs'n ACCUM（，），與|類似，如：'dogs, cats, puppies'n EQUI

14、V（=），如：'German shepherds=alsatians are big dogs'n ABOUT等【示例：】包含：“中國、安徽”關(guān)鍵字的“建設(shè)”或“農(nóng)業(yè)”銀行SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'( 建設(shè) | 農(nóng)業(yè) ) & 安徽 & 中國')>0;6.2 全文索引的維護(hù)對于CTXSYS.CONTEXT索引，當(dāng)應(yīng)用程序?qū)磉M(jìn)行DML操作后，對基表的索引維護(hù)是必須的。索引維護(hù)包括索引同步和索引優(yōu)化。 在索引建好后，我們可以在該用戶下

15、查到Oracle自動產(chǎn)生了以下幾個表：(假設(shè)索引名為myindex)： DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$N其中以I表最重要，可以查詢一下該表，看看有什么內(nèi)容：-IDX_CNDATABANKINFO_PKNMSELECT * FROM cn_common_data_bank_info;SELECT * FROM user_indexes;SELECT t.token_text,t.token_count FROM dr$idx_cndatabankinfo_pknm$i t;這里就不列出查詢接過了?？梢钥吹?，該表中保存的其實就是

16、Oracle 分析你的文檔后，生成的term記錄在這里，包括term出現(xiàn)的位置、次數(shù)、hash值等。當(dāng)文檔的內(nèi)容改變后，可以想見這個I表的內(nèi)容也應(yīng)該相應(yīng)改變，才能保證Oracle在做全文檢索時正確檢索到內(nèi)容(因為所謂全文檢索，其實核心就是查詢這個表)。這就用到sync(同步) 和 optimize(優(yōu)化)了。 n 同步(sync): 將新的term 保存到I表； n 優(yōu)化(optimize): 清除I表的垃圾，主要是將已經(jīng)被刪除的term從I表刪除。 當(dāng)基表中的被索引文檔發(fā)生insert、update、delete操作的時候，基表的改變并不能馬上影響到索引上直到同步索引?？梢圆樵円晥DCTX_U

17、SER_PENDING查看相應(yīng)的改動。例如：SELECT * FROM ctx_user_pending t; SELECT T.PND_INDEX_NAME, T.PND_ROWID, TO_CHAR(T.PND_TIMESTAMP, 'dd-mm-yyyy hh24:mi:ss') TIMESTAMP FROM CTX_USER_PENDING T;6.2.1 同步數(shù)據(jù)同步和優(yōu)化方法: 可以使用Oracle提供的ctx_ddl包同步和優(yōu)化索引 對于CTXCAT類型的索引來說， 當(dāng)對基表進(jìn)行DML操作的時候，Oracle自動維護(hù)索引。對文檔的改變馬上反映到索引中。CTXCAT

18、是事務(wù)形的索引。 【索引的同步 】在對基表插入，修改，刪除之后同步索引。推薦使用sync同步索引。語法： CTX_DDL.SYNC_INDEX( IDX_NAME IN VARCHAR2 DEFAULT NULL,-索引名稱 MEMORY IN VARCHAR2 DEFAULT NULL,-指定同步索引需要的內(nèi)存。默認(rèn)是系統(tǒng)參數(shù)：DEFAULT_INDEX_MEMORY -指定一個大的內(nèi)存時候可以加快索引效率和查詢速度，且索引有較少的碎片 PART_NAME IN VARCHAR2 DEFAULT NULL,-同步哪個分區(qū)索引 PARALLEL_DEGREE IN NUMBER DEFAULT

19、 1 -并行同步索引。設(shè)置并行度。 ); 例如：同步索引myindex:EXEC ctx_ddl.sync_index('myindex');實施建議：建議通過oracle的job對索引進(jìn)行同步 【索引的優(yōu)化 】經(jīng)常的索引同步將會導(dǎo)致你的CONTEXT索引產(chǎn)生碎片。索引碎片嚴(yán)重的影響了查詢的反應(yīng)速度。你可以定期優(yōu)化索引來減少碎片，減少索引大小，提高查詢效率。 當(dāng)文本從表中刪除的時候，Oracle Text標(biāo)記刪除的文檔，但是并不馬上修改索引。因此，就的文檔信息占據(jù)了不必要的空間，導(dǎo)致了查詢額外的開銷。你必須以FULL模式優(yōu)化索引，從索引中刪除無效的舊的信息。這個過程叫做垃圾處理

20、。當(dāng)你經(jīng)常的對表文本數(shù)據(jù)進(jìn)行更新，刪除操作的時候，垃圾處理是很必要的。 EXEC ctx_ddl.optimize_index('myidx', 'full');實施建議：每天在系統(tǒng)空閑的時候?qū)θ乃饕M(jìn)行相應(yīng)的優(yōu)化，以提高檢索的效率- 同步、優(yōu)化 全文索引IDX_CNDATABANKINFO_PKNM ，10W數(shù)據(jù)，執(zhí)行時間小于1分鐘BEGIN ctx_ddl.sync_index('IDX_CNDATABANKINFO_PKNM'); ctx_ddl.optimize_index('IDX_CNDATABANKINFO_PKNM

21、9;,'full');END;/6.3 單個中文字查詢問題查詢單個中文字時，出現(xiàn)錯誤：SELECT T.ID, T.BK_CD, T.PTCPT_NM FROM CN_COMMON_DATA_BANK_INFO T WHERE T.STATUS = 1 AND CONTAINS(T.PTCPT_NM, '行 & 銀 & 云') > 0; ORA-29902：執(zhí)行ODCIIndexStart() 例行程序中出錯； ORA-20000：Oracle Text Error； DRG-10847：CONTAINS 要求這種類型的查詢有前綴索引；改成

22、查詢：兩個字的中文則正常CONTAINS(T.PTCPT_NM, '分行 & 銀行 & 云南')【原因】默認(rèn)的中文分詞器，只將中文分割成如：“中國人民銀行” -> “中國 國人 人民 民銀 銀行”，沒有前綴索引，需要手工修改配置，修改如下SELECT * FROM user_indexes ;DROP INDEX IDX_CNDATABANKINFO_PKNM;-BEGIN ctx_ddl.drop_preference('cacps2_ceshi.CHINESE_LEXER'); ctx_ddl.drop_preference('

23、cacps2_ceshi.ChineseWordList'); ctx_ddl.create_preference ('cacps2_ceshi.CHINESE_LEXER', 'CHINESE_VGRAM_LEXER'); ctx_ddl.create_preference('cacps2_ceshi.ChineseWordList', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_I

24、NDEX','TRUE'); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MIN_LENGTH',1); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MAX_LENGTH', 5); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','SUBSTRING_INDEX'

25、, 'YES');END;/-Create INDEX IDX_CNDATABANKINFO_PKNM on cn_common_data_bank_info(ptcpt_nm) indextype is ctxsys.CONTEXT parameters('lexer cacps2_ceshi.chinese_lexer wordlist cacps2_ceshi.chinesewordlist');【總結(jié)：】n OracleText要支持中文必須得用CHINESE_VGRAM_LEXER。 n 要支持一個中文字以上的搜索，必須配置一個WordList，且把它

26、的屬性Prefix_Index配置為True。7 建立全文索引的完整腳本/* -以下腳本為代收付系統(tǒng)“行名行號表 cn_common_data_bank_info” 銀行名稱字段“ptcpt_nm ”建立全文索引數(shù)據(jù)庫用戶名：cacps2_ceshi索引名稱： IDX_CNDATABANKINFO_PKNM- */- 在DBA用戶權(quán)限下，將 CTX_DDL 使用權(quán)限付給 cacps2_ceshi 用戶GRANT EXECUTE ON ctx_ddl TO cacps2_ceshi;- 以下操作均使用 cacps2_ceshi 用戶進(jìn)行操作- 查詢是否存在索引 IDX_CNDATABANKINF

27、O_PKNMSELECT * FROM user_indexes t WHERE t.index_name='IDX_CNDATABANKINFO_PKNM'- 去除已經(jīng)存在的索引DROP INDEX IDX_CNDATABANKINFO_PKNM;- 設(shè)置全文索引參數(shù) cacps2_ceshi 指的是用戶名BEGIN ctx_ddl.drop_preference ('cacps2_ceshi.CHINESE_LEXER'); ctx_ddl.create_preference ('cacps2_ceshi.CHINESE_LEXER', &#

28、39;CHINESE_VGRAM_LEXER'); ctx_ddl.drop_preference ('cacps2_ceshi.ChineseWordList'); ctx_ddl.create_preference('cacps2_ceshi.ChineseWordList', 'BASIC_WORDLIST'); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_INDEX','TRUE'); ctx_ddl.se

29、t_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MIN_LENGTH',1); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','PREFIX_MAX_LENGTH', 5); ctx_ddl.set_attribute('cacps2_ceshi.ChineseWordList','SUBSTRING_INDEX', 'YES');END;/- 查看全文索引參數(shù)SELEC

30、T * FROM ctx_preference_values ORDER BY 1;SELECT * FROM ctx_preferences ORDER BY 1;- 建立 context 類型的全文索引Create INDEX IDX_CNDATABANKINFO_PKNM on cn_common_data_bank_info(ptcpt_nm) indextype is ctxsys.CONTEXT parameters('lexer cacps2_ceshi.chinese_lexer wordlist cacps2_ceshi.chinesewordlist');-

31、 全文索引查詢語句 containsSELECT COUNT(*) FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'農(nóng)業(yè)銀行 & 公司')>0;SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'農(nóng)業(yè) & 安徽 & 中 & 部')>0;SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'( 建設(shè) | 農(nóng)業(yè) ) & 安徽 & 中國