【轉載】oracle全文索引的建立和使用

一、設定詞法分析器

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

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

     chinese_vgram_lexer:專門的漢語分析器，支援所有漢字字符集（ZHS16CGB231280ZHS16GBKZHT32EUCZHT16BIG5ZHT32TRISZHT16MSWIN950ZHT16HKSCSUTF8）。該分析器按字為單元來分析漢語句子。‘中國人民站起來了’這句話，會被它分析成如下幾個term: ‘中’，‘中國’，‘國人’，‘人民’，‘民站’，‘站起’，起來’，‘來了’，‘了’。可以看出，這種分析方法，實現演算法很簡單，並且能實現‘一網打盡’，但效率則是差強人意。

     chinese_lexer:這是一個新的漢語分析器，只支援utf8字符集。上面已經看到，chinese vgram lexer這個分析器由於不認識常用的漢語詞彙，因此分析的單元非常機械，像上面的‘民站’，‘站起’在漢語中根本不會單獨出現，因此這種term是沒有意義的，反而影響效率。chinese_lexer的最大改進就是該分析器能認識大部分常用漢語詞彙，因此能更有效率地分析句子，像以上兩個愚蠢的單元將不會再出現，極大提高了效率。但是它只支援utf8,如果你的資料庫是zhs16gbk字符集，則只能使用笨笨的那個Chinese vgram lexer.

如果不做任何設定，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
說明：在建立索引的時候斷開、重啟等導致索引中斷沒有執行成功，之後再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;         //最佳化

建完後手動執行下：

exec dbms_job.run(jobno);

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

exec ctx_ddl.sync_index('index_name');

如果單獨執行沒有問題，則檢查job是否寫錯或者當前操作的oracle資料庫使用者有無執行儲存過程的許可權

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

以上報錯就是使用者沒有執行任何儲存過程造成的，此時需要對使用者加上這個許可權：

SQL> grant execute any procedure to oracle_username;

再看一下job的情況

select * from user_jobs;

 

四、測試

關聯查詢： select * from table_name where contains (column_name,'keyword') >0;

SQL> select * from searchkeytbl where type='城市' and contains (key,'楊浦') >0;

USERNAME             TYPE                                     KEY
-------------------- ---------------------------------------- --------------------------------------------------------------------------------
mujian80             城市                                     上海市楊浦區

 

五、問題

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

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.

看一下 可能是用於建立索引的表空間不夠了

reports——>DBA——>total free space   pl/sql developer工具，檢視錶空間的剩餘空間

select * from v$datafile;              檢視資料檔案資訊