您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle Text 全文檢索實戰：從 LIKE 遷移到 CONTAINS 的索引建立、同步與優化全流程

編輯：Oracle 資料庫基礎

遷移動機

系統資料增長導致 LIKE '%keyword%' 全表掃描效能急劇下降。引入 Oracle Text 全文索引，實現毫秒級模糊/語義搜尋。

前置條件

資料庫已安裝 Oracle Text 組件 (CTXSYS)
具備 CTXAPP 角色或 EXECUTE ANY PROCEDURE 權限

步驟 1：建立 Context 索引

-- 法律全文內容欄位
CREATE INDEX idx_flqw_nr ON flqw(nr) INDEXTYPE IS CTXSYS.CONTEXT;

-- 法律條款欄位
CREATE INDEX idx_fltk_nr ON fltk(nr) INDEXTYPE IS CTXSYS.CONTEXT;

CTXSYS.CONTEXT 為標準全文索引類型，支援詞幹、同義詞、近鄰、模糊、權重等豐富查詢語法。

步驟 2：查詢語法遷移

-- 原 LIKE 查詢
SELECT * FROM flqw WHERE nr LIKE '%勞動基準法%';

-- Oracle Text 查詢
SELECT * FROM flqw WHERE CONTAINS(nr, '勞動基準法') > 0;

-- 進階：邏輯組合
SELECT * FROM flqw WHERE CONTAINS(nr, '勞動 AND 基準法') > 0;
SELECT * FROM flqw WHERE CONTAINS(nr, '勞動 NEAR/5 基準法') > 0;  -- 距離 5 詞內
SELECT * FROM flqw WHERE CONTAINS(nr, 'fuzzy(勞動, 70, 1, WEIGHT)') > 0;  -- 模糊匹配

步驟 3：索引維護 — 同步與優化 (關鍵！)

Context 索引 不自動維護，DML 後必須同步才能搜尋到新資料。

手動同步

EXEC CTX_DDL.SYNC_INDEX('idx_flqw_nr');
EXEC CTX_DDL.SYNC_INDEX('idx_fltk_nr');

手動優化 (合併碎片、回收已刪除文件空間)

EXEC CTX_DDL.OPTIMIZE_INDEX('idx_flqw_nr', 'FULL');
EXEC CTX_DDL.OPTIMIZE_INDEX('idx_fltk_nr', 'FULL');

優化頻率遠低於同步 (如每日/每週)。優化可能導致死鎖，建議手工執行或低峰期排程。

步驟 4：自動化排程 (DBMS_JOB / DBMS_SCHEDULER)

建立同步/優化預存程序

GRANT EXECUTE ANY PROCEDURE TO flyy;

CREATE OR REPLACE PROCEDURE flyy.sync_index AS
BEGIN
  CTXSYS.CTX_DDL.SYNC_INDEX('idx_flqw_nr');
  CTXSYS.CTX_DDL.SYNC_INDEX('idx_fltk_nr');
END;
/

CREATE OR REPLACE PROCEDURE flyy.optimize_index AS
BEGIN
  CTX_DDL.OPTIMIZE_INDEX('idx_flqw_nr', 'FULL');
  CTX_DDL.OPTIMIZE_INDEX('idx_fltk_nr', 'FULL');
END;
/

建立 Job (每 15 分鐘同步，每日優化)

VARIABLE job1 NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:job1, 'flyy.sync_index;', SYSDATE, 'SYSDATE + 1/24/4');  -- 15 分鐘
  COMMIT;
END;
/

VARIABLE job2 NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:job2, 'flyy.optimize_index;', SYSDATE, 'SYSDATE + 1');  -- 每日
  COMMIT;
END;
/

啟動與監控

-- 查詢 Job 號
SELECT job, schema_user, interval, what FROM user_jobs;

-- 手動觸發測試
EXEC DBMS_JOB.RUN(41);  -- 替換實際 job 號

進階優化策略

策略	適用場景	語法
MEMORY 參數	大量索引建立	`CREATE INDEX ... PARAMETERS('MEMORY 200M')`
SYNC (ON COMMIT)	低 DML、需即時可搜	`CREATE INDEX ... PARAMETERS('SYNC (ON COMMIT)')`
SYNC (EVERY "...")	定時自動同步	`CREATE INDEX ... PARAMETERS('SYNC (EVERY "SYSDATE+1/24/4")')`
STORAGE 子句	指定索引表空間	`CREATE INDEX ... TABLESPACE ctx_ts PARAMETERS('STORAGE (INITIAL 10M)')`
LEXER 設定	中文分詞優化	`CTX_DDL.CREATE_PREFERENCE('my_lexer','CHINESE_VGRAM_LEXER')`
MULTI_COLUMN_DATASTORE	多欄位合一索引	`CTX_DDL.CREATE_PREFERENCE(...)`

中文分詞優化關鍵

-- 建立中文詞彙器偏好
EXEC CTX_DDL.CREATE_PREFERENCE('chinese_lexer', 'CHINESE_VGRAM_LEXER');

-- 建立索引時套用
CREATE INDEX idx_flqw_nr ON flqw(nr) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER chinese_lexer SYNC (ON COMMIT)');

CHINESE_VGRAM_LEXER 採用 N-gram 機制，無需詞典即可處理中文分詞，適合通用中文全文檢索。