oraclesql全文檢索_oracle資料庫實現全文檢索

『壹』 oracle資料庫實現全文檢索

Oracle全文檢索配置方法：

1.檢查資料庫是否具有全文檢索功能（這是針對已經建成使用的資料庫）

查看用戶中是否存在ctxsys用戶，查詢角色里是否存在ctxapp角色。以上兩個中的1個不滿足（不存在），則說明沒有裝過全文檢索功能。

使用contains函數的時候，若沒有全文檢索則會報錯的。

2.若沒有，則需要手動建立，先建立全文檢索要使用的空間

sqlplus / as sysdba --進入控制台

createtablespaceIdx_ctxsysdatafile'/oradata/sg186fx/ctxsys01.;--創建全文檢索使用的表空間

3.創建全文檢索使用的用戶和角色及相應的包，則需要執行oracle自帶的一個腳本：cd $ORACLE_HOME/ctx/admin/catctx.sql

還是在sqlplus中執行：

@?/ctx/admin/catctx.sql ctxsys Idx_ctxsys temp nolock

在執行這個腳本的時候，輸入了幾個參數，第一個參數ctxsys為ctxsys用戶的密碼

第二個參數Idx_ctxsys為ctxsys用戶要使用的表空間

第三個參數temp為ctxsys用戶使用的臨時表空間

第四個參數nolock為ctxsys用戶處於解鎖狀態。

4.創建完成後，要登錄ctxsys用戶

connect ctxsys/ctxsys

執行以下腳本：@?/ctx/admin/defaults/drdefus.sql(這是個很重要的腳本，後面創建索引會使用該腳本創建的信息)

5.創建全文索引語法分析器

先要明確使用全文索引的用戶，我要使用全文索引的是sgpm用戶

因此

grantexecuteonctxsys.ctx_ddltosgpmwithgrantoption;

connect sgpm/sgpm

設置語法分析器：

execctx_ddl.drop_preference('chinalexer');
execctx_ddl.create_preference('chinalexer','chinese_lexer');

設置詞法屬性：

execctx_ddl.drop_preference('idx_c_store');

begin
ctx_ddl.create_preference('idx_c_store','BASIC_STORAGE');
ctx_ddl.set_attribut('idx_c_store','I_TABLE_CLAUSE','tablespacesIdx_ctxsy');
ctx_ddl.set_attribute('idx_c_store','I_INDEX_CLAUSE','tablespaceIdx_ctxsycompress2');
end;
/

6.創建索引

createindexsgpm.idx_c_cons_nameonsgpm.c_cons(cons_name)indextypeisctxsys.contextparameters('lexerchinalexerstorageidx_c_store');

7.同步索引

variablejobnonumber;
begin
dbms_job.submit(:jobno,'pkg_sp_tools.p_cont_sys_index();',sysdate,'trunc(sysdate)+19/24+1');--執行的是個性化方法。
end;
/

普通的就是用：

execctx_ddl.sync_index('idx_c_cons_name');

到此，全文檢索創建成功，contains函數就可以正常使用了。

注意：創建的過程中會出現ORA-29879:cannot create multiple domain index on a column listusing same indextype ,這說明在其他用戶下已經建立了該索引。

『貳』 oracle 全文索引，chinese_vgram_lexer 分析器，不能檢索類似電話號碼，身份證號碼，這樣的純數字。

Oracle全文索引的BASIC屬性主要是針對西方英語語系，英語語系的單詞是通過空格、標點和回車來分隔的。而中文則需要索引來自動切詞。[@more@]2看下面這個例子：
SQL> CREATE TABLE T (ID NUMBER, DOCS VARCHAR2(1000));
表已創建。
SQL> INSERT INTO T VALUES (1, '一個中文例子，測試BASIC_LEXER語法屬性是否可以正常識別中文。');
已創建 1 行。
SQL> COMMIT;
提交完成。
SQL> CREATE INDEX IND_T_DOCS ON T (DOCS) INDEXTYPE IS CTXSYS.CONTEXT;
索引已創建。
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '中文') > 0;
未選定行
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '一個中文例子') > 0;
ID DOCS
1 一個中文例子，測試BASIC_LEXER語法屬性是否可以正常識別中文。
通過BASIC_LEXER來索引中文，Oracle只識別被空格、標點和回車符分隔出來的部分。需要對中文內容進行索引的話，就必須使用中文的LEXER。
Oracle提供了兩種預定義的中文LEXER屬性：CHINESE_VGRAM_LEXER和CHINESE_LEXER。Oracle在文檔上是這樣描述的：
CHINESE_LEXER相對應CHINESE_VGRAM_LEXER屬性有如下的優點：
產生的索引更小；
更好的查詢響應時間；
產生更接近真實的索引切詞，使得查詢精度更高；
支持停用詞。
雖然Oracle說明了使用CHINESE_LEXER的大量好處，但是CHINESE_LEXER的實際效果卻存在一定的問題：
SQL> DROP INDEX IND_T_DOCS;
索引已丟棄。
SQL> TRUNCATE TABLE T;
表已截掉。
SQL> INSERT INTO T VALUES (1, '北京大學未名湖');
已創建 1 行。
SQL> INSERT INTO T VALUES (2, '北京郵電大學');
已創建 1 行。
SQL> INSERT INTO T VALUES (3, '北京市第十四中學');
已創建 1 行。
SQL> COMMIT;
提交完成。
SQL> CONN CTXSYS/CTXSYS@YANGTK
已連接。
SQL> BEGIN
2 CTX_DDL.CREATE_PREFERENCE('TEST_CHINESE_VGRAM_LEXER', 'CHINESE_VGRAM_LEXER');
3 CTX_DDL.CREATE_PREFERENCE('TEST_CHINESE_LEXER', 'CHINESE_LEXER');
4 END;
5 /
PL/SQL 過程已成功完成。
SQL> CONN YANGTK/YANGTK@YANGTK
已連接。
SQL> CREATE INDEX IND_T_DOCS ON T(DOCS) INDEXTYPE IS CTXSYS.CONTEXT
2 PARAMETERS ('LEXER CTXSYS.TEST_CHINESE_VGRAM_LEXER');
索引已創建。
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '北京') > 0;
ID DOCS
3 北京市第十四中學
2 北京郵電大學
1 北京大學未名湖
SQL> DROP INDEX IND_T_DOCS;
索引已丟棄。
SQL> CREATE INDEX IND_T_DOCS ON T(DOCS) INDEXTYPE IS CTXSYS.CONTEXT
2 PARAMETERS ('LEXER CTXSYS.TEST_CHINESE_LEXER');
索引已創建。
SQL> SELECT * FROM T WHERE CONTAINS(DOCS, '北京') > 0;
ID DOCS
2 北京郵電大學
通過對比結果可以發現：使用CHINESE_LEXER 沒有將第一條記錄和第三條記錄中的北京檢索出來。也許Oracle的CHINESE_LEXER認為北京大學和北京市是一個完整的詞彙，而沒有對其進行進一步切分。也許這就是Oracle提到的這種切分方式更接近於真實的情況。我不知道讀者會怎樣看待這個問題，不過我更傾向於使用 CHINESE_VGRAM_LEXER，個人感覺返回記錄多一些總比漏掉一些要好。

『叄』 oracle資料庫的全文檢索，最大能檢索多大的文本。

檢索多大的文本？
oracle表的列最多有1000列，
對行的數量，邏輯上應該沒有限制，應該受限於你的物理空間，如硬碟等。當然，與建庫時的表空間、用戶配額的大小有關。

『肆』如何實現涉及多個表的多個欄位的全文檢索功能（oracle）

oracle可以支持多個欄位上的全文索引，但如果還涉及到多張表的話，就只能在每張表上建一個全文索引。但這樣的話，查詢時很不方便。而且如果涉及到的表或者欄位發生變化，需要重新建全文索引，可擴展性不佳。
另一個思路是專門建立一張中間表，其中包括以下欄位：表名，欄位名，欄位值，對象ID。在欄位值這個欄位上建立全文索引。然後定時的將原來四張表中須檢索的欄位同步到此中間表（可考慮用物化視圖）。查詢時，直接對此中間表進行查詢即可。
舉個例子：假設原表是文章表，其中有如下一條數據：
ID
標題
內容
作者
1
德川家康為什麼能統一日本？
請教一下，為什麼最後是德川家康統一日本呢？
德川家康粉
現在需要檢索在標題，內容或作者欄位中存在「德川家康」的數據。
首先，需要將原數據同步到中間表，變成以下三條數據：
表名
欄位名
欄位值
對象ID
文章表
標題
德川家康為什麼能統一日本？
1
文章表
內容
請教一下，為什麼最後是德川家康統一日本呢？
1
文章表
作者
德川家康粉
1
通過在欄位值這個欄位上全文檢索「德川家康」，可以篩選出以上三條數據。這三條數據都指向文章表中的ID＝1的文章。從文章表裡取出這條數據，檢索就成功結束了。
此方法的優點：一是檢索的速度比較快；二是可以兼容指定表或指定欄位的檢索；三是需要檢索的表或欄位可以動態刪減，可擴展性好。
主要的缺點一是需要一張中間表，並定時同步，消耗額外的伺服器資源，二是同步的過程會造成延時，即新修改但尚未同步的數據會暫時檢索不到。

『伍』如何實現Oracle資料庫Text全文檢索

Oracle全文檢索配置方法：
1.檢查資料庫是否具有全文檢索功能（這是針對已經建成使用的資料庫）
查看用戶中是否存在ctxsys用戶，查詢角色里是否存在ctxapp角色。以上兩個中的1個不滿足（不存在），則說明沒有裝過全文檢索功能。
使用contains函數的時候，若沒有全文檢索則會報錯的。
2.若沒有，則需要手動建立，先建立全文檢索要使用的空間
sqlplus / as sysdba --進入控制台

1

create tablespace Idx_ctxsys datafile '/oradata/sg186fx/ctxsys01.dbf size 10240M autoextend on next 32M maxsize 20480M;--創建全文檢索使用的表空間

3.創建全文檢索使用的用戶和角色及相應的包，則需要執行oracle自帶的一個腳本：cd $ORACLE_HOME/ctx/admin/catctx.sql
還是在sqlplus中執行：
@?/ctx/admin/catctx.sql ctxsys Idx_ctxsys temp nolock
在執行這個腳本的時候，輸入了幾個參數，第一個參數ctxsys為ctxsys用戶的密碼
第二個參數Idx_ctxsys為ctxsys用戶要使用的表空間
第三個參數temp為ctxsys用戶使用的臨時表空間
第四個參數nolock為ctxsys用戶處於解鎖狀態。
4.創建完成後，要登錄ctxsys用戶
connect ctxsys/ctxsys
執行以下腳本：@?/ctx/admin/defaults/drdefus.sql(這是個很重要的腳本，後面創建索引會使用該腳本創建的信息)
5.創建全文索引語法分析器
先要明確使用全文索引的用戶，我要使用全文索引的是sgpm用戶
因此

1

grant execute on ctxsys.ctx_ddl to sgpm with grant option;

connect sgpm/sgpm
設置語法分析器：

1
2

exec ctx_ddl.drop_preference('chinalexer');
exec ctx_ddl.create_preference('chinalexer','chinese_lexer');

設置詞法屬性：

1

exec ctx_ddl.drop_preference('idx_c_store');

1
2
3
4
5
6

begin
ctx_ddl.create_preference('idx_c_store','BASIC_STORAGE');
ctx_ddl.set_attribut('idx_c_store','I_TABLE_CLAUSE','tablespaces Idx_ctxsy');
ctx_ddl.set_attribute('idx_c_store','I_INDEX_CLAUSE','tablespace Idx_ctxsy compress 2');
end;
/

6.創建索引

1

create index sgpm.idx_c_cons_name on sgpm.c_cons(cons_name) indextype is ctxsys.context parameters('lexer chinalexer storage idx_c_store');

7.同步索引

1
2
3
4
5

variable jobno number;
begin
dbms_job.submit(:jobno,'pkg_sp_tools.p_cont_sys_index();',sysdate,'trunc(sysdate)+19/24+1'); --執行的是個性化方法。
end;
/

普通的就是用：

1

exec ctx_ddl.sync_index('idx_c_cons_name');

到此，全文檢索創建成功，contains函數就可以正常使用了。
注意：創建的過程中會出現ORA-29879:cannot create multiple domain index on a column listusing same indextype ,這說明在其他用戶下已經建立了該索引。

『陸』如何手工安裝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:專門的漢語分析器，支持所有漢字字元集（SUTF8）。該分析器按字為單元來分析漢語句子。『中國人民站起來了』這句話，會被它分析成如下幾個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 procere 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; 查看數據文件信息

『柒』 oracle資料庫的全文檢索。

Toad上你用system用戶登錄，你是你所謂的"system用戶界面"了

『捌』 Oracle全文檢索是什麼意思

全文檢索是一種將文件中所有文本與檢索項匹配的文字資料檢索方法。全文檢索系統是按照全文檢索理論建立起來的用於提供全文檢索服務的軟體系統。

判斷檢索效果的兩個指標：

查全率=被檢出相關信息量/相關信息總量（%）

查准率=被檢出相關信息量/被檢出信息總量（%）