Oracle 如何創(chuàng)建和使用全文索引

不使用Oracle text功能,也有很多方法可以在Oracle數(shù)據(jù)庫中搜索文本.可以使用標準的INSTR函數(shù)和LIKE操作符實現(xiàn)。

SELECT *FROM mytext WHERE INSTR (thetext, 'Oracle') > 0;

SELECT * FROM mytext WHERE thetext LIKE '%Oracle%';

　　有很多時候，使用instr和like是很理想的, 特別是搜索僅跨越很小的表的時候.然而通過這些文本定位的方法將導致全表掃描,對資源來說消耗比較昂貴,而且實現(xiàn)的搜索功能也非常有限，因此對海量的文本數(shù)據(jù)進行搜索時，建議使用oralce提供的全文檢索功能 建立全文檢索的步驟步驟一 檢查和設(shè)置數(shù)據(jù)庫角色首先檢查數(shù)據(jù)庫中是否有CTXSYS用戶和CTXAPP腳色。如果沒有這個用戶和角色，意味著你的數(shù)據(jù)庫創(chuàng)建時未安裝intermedia功能。你必須修改數(shù)據(jù)庫以安裝這項功能。 默認安裝情況下，ctxsys用戶是被鎖定的，因此要先啟用ctxsys的用戶。 步驟二 賦權(quán) 在ctxsys用戶下把ctx_ddl的執(zhí)行權(quán)限賦于要使用全文索引的用戶，例：

grant execute on ctx_ddl to pomoho;

一、設(shè)置詞法分析器

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

　　basic_lexer:針對英語。它能根據(jù)空格和標點來將英語單詞從句子中分離，還能自動將一些出現(xiàn)頻率過高已經(jīng)失去檢索意義的單詞作為‘垃圾'處理，如if , is等，具有較高的處理效率。但該lexer應用于漢語則有很多問題，由于它只認空格和標點，而漢語的一句話中通常不會有空格，因此，它會把整句話作為一個term,事實上失去檢索能力。以‘中國人民站起來了'這句話為例，basic_lexer分析的結(jié)果只有一個term ,就是‘中國人民站起來了'。此時若檢索‘中國'，將檢索不到內(nèi)容。

　　chinese_vgram_lexer:專門的漢語分析器，支持所有漢字字符集（ZHS16CGB231280ZHS16GBKZHT32EUCZHT16BIG5ZHT32TRISZHT16MSWIN950ZHT16HKSCSUTF8）。該分析器按字為單元來分析漢語句子?！袊嗣裾酒饋砹?這句話，會被它分析成如下幾個term: ‘中'，‘中國'，‘國人'，‘人民'，‘民站'，‘站起'，起來'，‘來了'，‘了'?？梢钥闯?，這種分析方法，實現(xiàn)算法很簡單，并且能實現(xiàn)‘一網(wǎng)打盡'，但效率則是差強人意。

　　chinese_lexer:這是一個新的漢語分析器，只支持utf8字符集。上面已經(jīng)看到，chinese vgram lexer這個分析器由于不認識常用的漢語詞匯，因此分析的單元非常機械，像上面的‘民站'，‘站起'在漢語中根本不會單獨出現(xiàn)，因此這種term是沒有意義的，反而影響效率。chinese_lexer的最大改進就是該分析器能認識大部分常用漢語詞匯，因此能更有效率地分析句子，像以上兩個愚蠢的單元將不會再出現(xiàn)，極大提高了效率。但是它只支持utf8,如果你的數(shù)據(jù)庫是zhs16gbk字符集，則只能使用笨笨的那個Chinese vgram lexer.
如果不做任何設(shè)置，Oracle缺省使用basic_lexer這個分析器。要指定使用哪一個lexer,可以這樣操作：

BEGIN
 ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');
END;
/

其中my_lexer是分析器名。

二、建立全文索引

在建立intermedia索引時，指明所用的lexer:

CREATE INDEX  myindex ON mytable(mycolumn) indextype is ctxsys.context parameters('lexer my_lexer');

※個人體會：全文索引建立后，用pl/sql developer工具view table，在index這一欄是看不到索引信息的。

而本人在刪除全文索引時遇到過一下報錯：

SQL> drop index searchkeytbl_key;
drop index searchkeytbl_key
ORA-29868: cannot issue DDL on a domain index marked as LOADING

解決方法：

ORA-29868: cannot issue DDL on a domain index marked as LOADING
說明：在創(chuàng)建索引的時候斷開、重啟等導致索引中斷沒有執(zhí)行成功，之后再drop或者rebuild等操作的時候都會報此錯誤
解決：只能drop index ind_name force強行刪除，然后再重建

三、索引同步維護

用以下的兩個job來完成(該job要建在和表同一個用戶下) :

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.sync_index(''index_name'');',
SYSDATE, 'SYSDATE + (1/24/4)');
commit;
END;      //同步
 
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,'ctx_ddl.optimize_index(''myindex'',''FULL'');',
SYSDATE, 'SYSDATE + 1');
commit;     //優(yōu)化

建完后手動運行下：

exec dbms_job.run(jobno);

※個人體會：運行job可能會有問題，此時可以單獨運行索引，嘗試一下

exec ctx_ddl.sync_index('index_name');

如果單獨運行沒有問題，則檢查job是否寫錯或者當前操作的oracle數(shù)據(jù)庫用戶有無運行存儲過程的權(quán)限

SQL> exec dbms_job.run(190);
begin dbms_job.run(190); end;

ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1

以上報錯就是用戶沒有運行任何存儲過程造成的，此時需要對用戶加上這個權(quán)限：

SQL> grant execute any procedure to oracle_username;

再看一下job的情況

select * from user_jobs;

四、測試

關(guān)聯(lián)查詢： select * from table_name where contains (column_name,'keyword') >0;
SQL> select * from searchkeytbl where type='城市' and contains (key,'楊浦') >0;

USERNAME             TYPE                                     KEY
-------------------- ---------------------------------------- --------------------------------------------------------------------------------
mujian80             城市                                     上海市楊浦區(qū)

五、問題

加全文索引遇到的問題(不斷更新)

SQL> create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer');
create index gh_ghname_idx on gh(ghname) indextype is ctxsys.context parameters('lexer gh_ghname_lexer')

ORA-24795: Illegal COMMIT attempt made
ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvddl.IndexCreate
ORA-20000: Oracle Text error:
DRG-50857: oracle error in drvdml.MaintainKTab
ORA-24795: Illegal COMMIT attempt made
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364
To avoid the error, please use one of the following solutions

1. Don't use a 32k-blocksized tablespace to store the internal index objects
- or -
2. Download Patch 5596325 from Metalink and apply it as described in the README file.

看一下 可能是用于創(chuàng)建索引的表空間不夠了

reports——>DBA——>total free space   pl/sql developer工具，查看表空間的剩余空間
select * from v$datafile;              查看數(shù)據(jù)文件信息