Oracle全文索引技術-使用說明文檔

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

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

3、程?！綩racle Text 全文檢索】Oracle一直致力于全文檢索技術的研究，當Oracle9i Rlease2發(fā)布之時，Oracle數據庫的全文檢索技術已經非常完美，Oracle Text使Oracle9i具備了強大的文本檢索能力和智能化的文本管理能力。Oracle Text是Oracle9i采用的新名稱，在Oracle8/8i中它被稱作Oracle interMedia Text，在Oracle8以前它的名稱是Oracle ConText Cartridge。使用Oracle9i和Oracle Text，可以方便而有效地利用標準的SQL工具來構建基于文本的新的開發(fā)工具或對現(xiàn)有應用程序進

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

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

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

7、lexer ：專門針對漢語，對漢字搜索準確性較高如：中國人民站起來了這句話，會被它分析成如下幾個term: 中，中國，國人，人民，民站，站起，起來，來了，了Ø chinese_lexer ：新的漢語分析器，只支持 UTF8 字符集，可以將語句分詞成常見的詞組，能跟有效率的分析語句，對漢字解析搜索效率較高【指定語法分析器：】1. 當前用戶下下建立一個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ù)文本數據進行檢索。支持word、html、xml、text等很多數據格式。支持中文字符集，支持分區(qū)索引，唯一支持并行創(chuàng)建索引（Parallel indexing）的索引類型。對表進行DML操作后，并不會自動同步索引。需要手工同步索引。查詢操作符：CONTAINSn CTXCAT 當使用混合查詢語句的時候可以帶來很好的效率。適合于查詢較小的具有一定結構的文本段。具有事務性，當更新主表的時候自動同步索引。作符：CATSEARCHn CTXRULE 主要

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

11、ASTORE');- 建立 preference 對應的字段值（以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ù)文本數據進行檢索。支持word、html、xml、text等很多數據格式。支持中文字符集，支持分區(qū)索引，唯一支持并行創(chuàng)建索引（Parallel indexing）的索引類型。對表進行DML操作后，并不會自動同步索引。需要手工同步索引。查詢操作符：CONTAINS6.1 函數 CONTAINS 語法使用contains時，主要查

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

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

15、查到Oracle自動產生了以下幾個表：(假設索引名為myindex)： DR$myindex$I、DR$myindex$K、DR$myindex$R、DR$myindex$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)的位置、次數、hash值等。當文檔的內容改變后，可以想見這個I表的內容也應該相應改變，才能保證Oracle在做全文檢索時正確檢索到內容(因為所謂全文檢索，其實核心就是查詢這個表)。這就用到sync(同步) 和 optimize(優(yōu)化)了。 n 同步(sync): 將新的term 保存到I表； n 優(yōu)化(optimize): 清除I表的垃圾，主要是將已經被刪除的term從I表刪除。 當基表中的被索引文檔發(fā)生insert、update、delete操作的時候，基表的改變并不能馬上影響到索引上直到同步索引?？梢圆樵円晥DCTX_U

17、SER_PENDING查看相應的改動。例如：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 同步數據同步和優(yōu)化方法: 可以使用Oracle提供的ctx_ddl包同步和優(yōu)化索引 對于CTXCAT類型的索引來說， 當對基表進行DML操作的時候，Oracle自動維護索引。對文檔的改變馬上反映到索引中。CTXCAT

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

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

20、。當你經常的對表文本數據進行更新，刪除操作的時候，垃圾處理是很必要的。 EXEC ctx_ddl.optimize_index('myidx', 'full');實施建議：每天在系統(tǒng)空閑的時候對全文索引進行相應的優(yōu)化，以提高檢索的效率- 同步、優(yōu)化 全文索引IDX_CNDATABANKINFO_PKNM ，10W數據，執(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, '分行 & 銀行 & 云南')【原因】默認的中文分詞器，只將中文分割成如：“中國人民銀行” -> “中國 國人 人民 民銀 銀行”，沒有前綴索引，需要手工修改配置，修改如下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');【總結：】n OracleText要支持中文必須得用CHINESE_VGRAM_LEXER。 n 要支持一個中文字以上的搜索，必須配置一個WordList，且把它

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

27、O_PKNMSELECT * FROM user_indexes t WHERE t.index_name='IDX_CNDATABANKINFO_PKNM'- 去除已經存在的索引DROP INDEX IDX_CNDATABANKINFO_PKNM;- 設置全文索引參數 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;/- 查看全文索引參數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,'農業(yè)銀行 & 公司')>0;SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'農業(yè) & 安徽 & 中 & 部')>0;SELECT * FROM cn_common_data_bank_info t WHERE contains(t.ptcpt_nm,'( 建設 | 農業(yè) ) & 安徽 & 中國