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

1、Oracle全文索引技術(shù)使用說明文檔金聯(lián)萬家(北京)電子支付科技發(fā)展有限公司文檔編號(hào)保 密 等 級(jí)機(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 對(duì)多字段建立索引66 Oracle全文索引之 - CONTEXT76.1 函數(shù) CONTAI

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

3、程。【Oracle Text 全文檢索】Oracle一直致力于全文檢索技術(shù)的研究，當(dāng)Oracle9i Rlease2發(fā)布之時(shí)，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)用程序中可搜索的注釋字段，也可是實(shí)現(xiàn)涉及多種文檔格式和復(fù)雜搜索標(biāo)準(zhǔn)的大型文檔管理系統(tǒng)。Oracle Text支持Oracle數(shù)據(jù)庫所支持的大多數(shù)語言的基本全文搜索功能。擴(kuò)展閱讀： 3 建立全文索引的操作步驟3.1 查看用戶與角色檢查數(shù)據(jù)庫中是否有 CTXSYS 用戶 和 CTXAPP 角色如果沒有這個(gè)用戶和角色，意味著你的數(shù)據(jù)庫創(chuàng)建時(shí)未安裝intermedia功能。你必須修改數(shù)據(jù)庫以安裝這項(xiàng)功能。默認(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實(shí)現(xiàn)全文

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

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

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

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

10、用于文檔分類。 查詢操作符：MATCHESn CTXXPATH 快速查找XML文檔NODE節(jié)點(diǎn)XPATH路徑5 對(duì)多字段建立索引很多時(shí)候需要從多個(gè)文本字段中查詢滿足條件的記錄，這時(shí)就需要建立針對(duì)多個(gè)字段的全文索引，例如需要從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 對(duì)應(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：用于對(duì)含有大量連續(xù)文本數(shù)據(jù)進(jìn)行檢索。支持word、html、xml、text等很多數(shù)據(jù)格式。支持中文字符集，支持分區(qū)索引，唯一支持并行創(chuàng)建索引（Parallel indexing）的索引類型。對(duì)表進(jìn)行DML操作后，并不會(huì)自動(dòng)同步索引。需要手工同步索引。查詢操作符：CONTAINS6.1 函數(shù) CONTAINS 語法使用contains時(shí)，主要查

13、詢語法有：contains(列名稱,查詢關(guān)鍵字)Logical Operators：組合搜索條件，通過使用AND，OR等邏輯符號(hào)。n AND（&），同時(shí)含有所有關(guān)鍵詞，如：'cats AND dogs'，'cats & dogs'n OR（|），含有所有關(guān)鍵詞中的任意一個(gè)，如：'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ù)對(duì)于CTXSYS.CONTEXT索引，當(dāng)應(yīng)用程序?qū)磉M(jìn)行DML操作后，對(duì)基表的索引維護(hù)是必須的。索引維護(hù)包括索引同步和索引優(yōu)化。 在索引建好后，我們可以在該用戶下

15、查到Oracle自動(dòng)產(chǎn)生了以下幾個(gè)表：(假設(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;這里就不列出查詢接過了?？梢钥吹?，該表中保存的其實(shí)就是

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

17、SER_PENDING查看相應(yīng)的改動(dò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)化索引 對(duì)于CTXCAT類型的索引來說， 當(dāng)對(duì)基表進(jìn)行DML操作的時(shí)候，Oracle自動(dòng)維護(hù)索引。對(duì)文檔的改變馬上反映到索引中。CTXCAT

18、是事務(wù)形的索引。 【索引的同步 】在對(duì)基表插入，修改，刪除之后同步索引。推薦使用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 -指定一個(gè)大的內(nèi)存時(shí)候可以加快索引效率和查詢速度，且索引有較少的碎片 PART_NAME IN VARCHAR2 DEFAULT NULL,-同步哪個(gè)分區(qū)索引 PARALLEL_DEGREE IN NUMBER DEFAULT

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

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

21、9;,'full');END;/6.3 單個(gè)中文字查詢問題查詢單個(gè)中文字時(shí)，出現(xiàn)錯(cuò)誤：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() 例行程序中出錯(cuò)； ORA-20000：Oracle Text Error； DRG-10847：CONTAINS 要求這種類型的查詢有前綴索引；改成

22、查詢：兩個(gè)字的中文則正常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 要支持一個(gè)中文字以上的搜索，必須配置一個(gè)WordList，且把它

26、的屬性Prefix_Index配置為True。7 建立全文索引的完整腳本/* -以下腳本為代收付系統(tǒng)“行名行號(hào)表 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è) ) & 安徽 & 中國