SELECT * FROM my text WHERE INSTR(the text,' Oracle ')& gt;0;
SELECT * FROM mytext,其中文本類似於“% Oracle %”;
在許多情況下,使用instr和like是理想的,特別是當搜索只跨越壹個小表時。但是這些文本定位方式會導致全表掃描,耗費資源,搜索功能非常有限。所以在搜索海量文本數據時,建議使用oralce提供的全文搜索功能,建立全文搜索。步驟1:檢查並設置數據庫角色。首先,檢查數據庫中是否有CTXSYS用戶和CTXAPP基石。如果沒有這個用戶和角色,這意味著在創建數據庫時沒有安裝intermedia函數。您必須修改數據庫才能安裝此功能。默認情況下,ctxsys的用戶是鎖定的,因此應該首先啟用ctxsys的用戶。第二步:將ctxsys用戶下ctx_ddl的執行權限授予想要使用全文索引的用戶,例如:
將ctx_ddl上的execute授予pomoho
步驟3:設置詞法分析器(lexer)
Oracle實現全文檢索,其機制其實很簡單。即通過甲骨文專利的詞法分析器(lexer)找出文章中所有的表意單位(甲骨文稱之為$ term),並記錄在壹組以dr$開頭的表格中,同時記錄$ term的位置、頻率、哈希值等信息。在搜索時,Oracle從這組表中查找相應的$ term,計算其出現頻率,並根據壹種算法計算每個文檔的得分,這就是所謂的‘匹配率’。Lexer是這個機制的核心,決定了全文檢索的效率。Oracle為不同的語言提供了不同的lexer,我們通常可以使用其中的三種:
N basic_lexer:針對英語。它可以根據空格和標點符號將英語單詞從句子中分離出來,還可以自動將壹些出現過於頻繁、已經失去檢索意義的單詞視為‘垃圾’,如if、is等。,處理效率高。然而,這個詞用在漢語中存在許多問題。因為它只識別空格和標點符號,而中文句子中通常沒有空格,所以它會把整個句子當作壹個$ term,事實上它會失去檢索能力。以“中國人民站起來了”這句話為例。basic_lexer分析的結果只有壹個$ term,就是“中國人民站起來了”。如果此時搜索“中國”,將無法檢索到相關內容。
N chinese_vgram_lexer:支持所有中文字符集的專用中文分析器(ZHS 16 GB 231280 zhs 16 gbk zht 32 euchzht 16 big 5 zht 32 triszht 16 ms win 950 zht 16 hkk)。分析器以詞為單位分析漢語句子。“中國人民站起來了”這句話,將被它分析為:“中國”、“中國人”、“中國人”、“人民”、“人民站起來”、“起來”、“來了”、“來了”。可以看出,這種分析方法,實現算法非常簡單,可以實現‘壹網打盡’,但效率並不盡如人意。
N chinese_lexer:這是壹個新的中文分析器,只支持utf8字符集。正如我們上面看到的,分析器中文vgram lexer是非常機械的,因為它不知道常用的中文單詞。比如上面的‘人站’和‘站起來’在中文裏永遠不會單獨出現,所以這種$ term是沒有意義的,會影響效率。chinese_lexer最大的改進是分析器可以理解大多數常用的中文單詞,因此可以更高效地分析句子。以上兩個傻逼單位不會再出現,大大提高了效率。但它只支持utf8。如果妳的數據庫是zhs16gbk字符集,那就只能用傻逼的中文vgram lexer了。
如果不做任何設置,Oracle默認使用解析器basic_lexer。要指定使用哪個詞法分析器,可以這樣做:
首先,在當前用戶下創建壹個首選項(例如,在pomoho用戶下執行下面的語句)。
exec CTX _ DDL . create _ preference(' my _ lexer ',' Chinese _ vgram _ lexer ');
其次,在建立全文索引時,指定所使用的詞法分析器:
在mytable(mycolumn)上創建索引myindex索引類型為ctxsys.context
參數(' lexer my _ lexer ');
這樣建立的全文索引將使用chinese_vgram_lexer作為解析器。
第四步:建立索引。
使用以下語法建立全文索引
創建索引[架構。]架構上的索引。]表(列)索引類型為ctxsys.context [ONLINE]
LOCAL[(PARTITION[PARTITION][PARAMETERS(' paramstring ')]
[,PARTITION[PARTITION][PARAMETERS(' paramstring ')]]]]
[PARAMETERS(paramstring)][PARALLEL n][UNUSABLE];
示例:
在pubmenu上創建索引CTX _ idx _ menuname(menuname)
indextype是ctxsys.context參數(' lexer my_lexer ')
使用索引
使用全文索引很簡單,可以通過以下方式實現:
select * from pub menu where contains(menu,'上傳圖片')> 0
全文索引的類型
已建立的Oracle Text索引稱為域索引,它包括四種索引類型:
l上下文
2 CTXCAT
3 CTXRULE
4 CTXXPATH
根據您的應用程序和文本數據類型,您可以選擇任何壹種。
為多個字段建立全文索引
很多時候,需要從多個文本字段中查詢符合要求的記錄。這時候就需要為多個字段建立全文索引。例如,如果需要從pmhsubjects的subjectname和briefintro中進行全文檢索,則需要執行以下步驟:
建議首選多字段索引
以ctxsys身份登錄並執行:
EXEC CTX _ DDL . create _ preference(' CTX _ idx _ subject _ pref ',
多列數據存儲');
建立與首選項相對應的字段值(以ctxsys身份登錄)
EXEC CTX _ DDL . set _ attribute(' CTX _ idx _ subject _ pref ',' columns ',' subjectname,brief intro ');
建立全文索引
在pmhsubjects(subjectname)上創建索引ctx_idx_subject
INDEXTYPE ISctxsys。上下文參數('數據存儲CTX sys . CTX _ idx _ subject _ pref lexer my _ lexer ')
使用索引
select * from pmh subjects where contains(主題名,“李宇春”)> 0
全文索引的維護
為了CTXSYS。上下文索引,在應用程序對基表執行DML操作後,需要維護基表的索引。索引維護包括索引同步和索引優化。
索引建立後,我們可以發現Oracle在這個用戶下自動生成了下面的表:(假設索引名為myindex):
$myindex$I博士,$myindex$K博士,$myindex$R博士,$myindex$N博士,其中表I最重要。您可以查看該表,了解它包含的內容:
SELECT token_text,token _ count FROM dr $ I _ rsk 1 $ I其中ROWNUM & lt= 20;
這裏就不列舉查詢了。我拿了。如您所見,保存在這個表中的實際上是Oracle在分析您的文檔後生成的$ term記錄,包括$ term的位置、頻率和哈希值。當文檔內容發生變化時,可想而知,這個I表的內容也應該發生相應的變化,以保證Oracle在做全文檢索時能夠正確地檢索到內容(因為所謂的全文檢索其實就是查詢這個表的核心)。這使用了sync(同步)和optimize(優化)。
Sync:將新的$ term保存到I表中;
優化:清除I表的垃圾,主要是刪除I表中已刪除的$ term。
當基表中的索引文檔被插入、更新或刪除時,基表的更改不會立即影響索引,直到索引被同步。您可以查詢視圖CTX _用戶_待定來查看相應的更改。例如:
SELECT pnd_index_name,pnd_rowid,
TO _ CHAR(PND _時間戳,' dd-mon-yyyy hh24:mi:ss ')時間戳
來自ctx _ user _ pending
該語句的輸出類似於以下內容:
PND索引名稱PND ROWID時間戳
- - -
MYINDEX aaadxnaabaaas 3 saac 06-oct-1999 15:56:50
同步和優化方法:可以使用Oracle提供的ctx_ddl包來同步和優化索引。
1.對於CTXCAT類型的索引,當對基表執行DML操作時,Oracle會自動維護索引。對文檔的更改會立即反映在索引中。CTXCAT是交易形式的索引。
索引的同步
在插入、修改和刪除基表後同步索引。建議使用sync來同步索引。語法:
ctx _ ddl.sync _索引(
VARCHAR2中的idx_name默認為空
VARCHAR2中的內存默認為空,
VARCHAR2中的part_name默認為空
parallel_degree IN NUMBER默認1);
Idx_name索引名稱
Memory指定同步索引所需的內存。默認值是系統參數DEFAULT_INDEX_MEMORY。
指定大內存可以加快索引效率和查詢速度,索引碎片少。
Part_name同步哪個分區索引。
並行度並行同步索引。設置平行度。
例如:
同步索引myindex:exectx _ DDL . sync _ index(' myindex ');
實現建議:建議通過oracle的job同步索引。
索引優化
頻繁的索引同步會導致上下文索引碎片化。索引碎片嚴重影響查詢的響應速度。可以定期優化索引,減少碎片,減小索引大小,提高查詢效率。
從表中刪除文本後,Oracle Text會標記刪除的文檔,但不會立即修改索引。因此,現有的文檔信息占用了不必要的空間,導致了額外的查詢成本。您必須在完全模式下優化索引,並從索引中刪除無效的舊信息。這個過程叫做垃圾處理。當妳經常更新和刪除表格文本數據時,垃圾處理是必要的。
exec CTX _ DDL . optimize _ index(' myidx ',' full ');
建議:每天在系統空閑時優化全文索引,提高檢索效率。
P.s .時序優化指數
3.定時優化同步域索引。
創建計劃任務,定期優化和同步域索引。
SQL & gt創建或替換過程hsp_sync_index為
2開始
3 CTX _ DDL . sync _ index(' id _ cont _ msg ');
4結束;
5 /
程序已創建。
耗時:00:00:00.08
SQL & gt可變工單編號;
SQL & gt開始
2 DBMS_JOB。提交(:作業號,' HSP _ sync _ index();',
3 SYSDATE,' SYSDATE+(1/24/4)');
4提交;
5結束;
6 /
PL/SQL過程已成功完成。
耗時:00:00:00.27
SQL & gt將過程hsp_optimize_index創建或替換為
2開始
3 CTX _ DDL . optimize _ index(' id _ cont _ msg ',' FULL ');
4結束;
5 /
SQL & gt可變工單編號;
SQL & gt開始
2 DBMS_JOB。SUBMIT(:jobno,' HSP _ optimize _ index();',
3 SYSDATE,' SYSDATE+1 ');
4提交;
5結束;
6 /
程序已創建。
耗時:00:00:00.03
PL/SQL過程已成功完成。
耗時:00:00:00.02
SQL & gt