Oracle Text 全文檢索實戰指南：從詞法分析器選型到索引維護自動化

為什麼需要 Oracle Text？

傳統 LIKE '%keyword%' 或 INSTR 會導致全表掃描，資料量超過萬筆效能急劇下降，且不支援語意分析、相關性評分。Oracle Text (InterMedia) 提供倒排索引、多語言斷詞、評分排序，是海量文字檢索的標準解法。

建立全文索引五步驟

步驟 1：確認環境與角色

-- 檢查 CTXSYS 是否存在且解鎖
SELECT username, account_status FROM dba_users WHERE username = 'CTXSYS';

-- 若被鎖定
ALTER USER CTXSYS IDENTIFIED BY new_password ACCOUNT UNLOCK;

-- 授予目標使用者 CTXAPP 角色與 CTX_DDL 執行權
GRANT CTXAPP TO app_user;
GRANT EXECUTE ON CTX_DDL TO app_user;

步驟 2：選擇並建立 Lexer (詞法分析器) —— 核心決定效能

-- 建立 Preference (以 CHINESE_VGRAM_LEXER 為例)
BEGIN
  CTX_DDL.CREATE_PREFERENCE('my_lexer', 'CHINESE_VGRAM_LEXER');
  -- 可選微調：CTX_DDL.SET_ATTRIBUTE('my_lexer', 'MIXED_CASE', 'YES');
END;
/

步驟 3：建立 CONTEXT 索引

CREATE INDEX idx_doc_content ON documents(content)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('LEXER my_lexer SYNC (ON COMMIT)'); -- 可加 SYNC 自動同步

參數說明：LEXER 指定斷詞器；SYNC (ON COMMIT) 交易提交即同步 (適合低頻寫入)；高頻寫入建議 SYNC (EVERY "NEXT_DAY(TRUNC(SYSDATE)+1/24)") 定時同步。

步驟 4：查詢語法與評分

-- 基本查詢
SELECT title, SCORE(1) AS relevance
FROM documents
WHERE CONTAINS(content, 'Oracle 資料庫', 1) > 0
ORDER BY relevance DESC;

-- 進階：邏輯運算、權重、模糊
-- 'Oracle & Database'      AND
-- 'Oracle | Database'      OR
-- 'Oracle ~ Database'      NOT
-- 'Oracle > Database'      權重 Oracle 較重
-- '?Oracle'                模糊匹配 (拼音/同音)

步驟 5：多欄位索引 (MULTI_COLUMN_DATASTORE)

BEGIN
  CTX_DDL.CREATE_PREFERENCE('multi_ds', 'MULTI_COLUMN_DATASTORE');
  CTX_DDL.SET_ATTRIBUTE('multi_ds', 'COLUMNS', 'subject, brief_intro');
END;
/

CREATE INDEX idx_subject_multi ON subjects(subject)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('DATASTORE multi_ds LEXER my_lexer');

查詢時仍對單一欄位發問：CONTAINS(subject, '關鍵字')，實際搜尋包含多欄位內容。

索引類型選擇

索引維護自動化：同步與最佳化

同步

-- 手動同步
EXEC CTX_DDL.SYNC_INDEX('idx_doc_content', '256M'); -- 指定記憶體加速

-- 排程自動同步 (建議每小時/每日視寫入量)
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'SYNC_TEXT_IDX',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN CTX_DDL.SYNC_INDEX(''idx_doc_content''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    enabled         => TRUE);
END;
/

最佳化

同步會產生碎片 (舊 Token 標記刪除但未物理移除)，定期 OPTIMIZE_INDEX 清理：

-- FULL 模式：完整重建，清除所有垃圾，耗時較長
EXEC CTX_DDL.OPTIMIZE_INDEX('idx_doc_content', 'FULL', '256M');

-- FAST 模式：僅合併碎片，較快
EXEC CTX_DDL.OPTIMIZE_INDEX('idx_doc_content', 'FAST');

-- TOKEN 模式：指定 Token 清理
EXEC CTX_DDL.OPTIMIZE_INDEX('idx_doc_content', 'TOKEN', NULL, 'Oracle');

建議策略：每日離峰時段 FAST，每週末 FULL；監控 CTX_USER_INDEX_FRAGMENTS 碎片比例 > 50% 即觸發 FULL。

監控與疑難排查

• CTX_USER_PENDING：查看待同步的 ROWID、時間戳。
• DR$index_name$I (Token 表)：SELECT token_text, token_count FROM dr$idx_doc_content$I WHERE ROWNUM < 20; 檢查斷詞結果。
• CTX_USER_INDEX_ERRORS：同步/最佳化錯誤日誌。
• 查詢慢：檢查 FIRST_ROWS Hint、確認 SCORE 排序未導致全表掃描、評估 OPTIMIZE_INDEX 頻率。

總結

1. 字元集 UTF8 → CHINESE_LEXER；非 UTF8 → CHINESE_VGRAM_LEXER。
2. 索引建立指定 LEXER、SYNC 策略、DATASTORE (多欄位)。
3. 自動化 SYNC_INDEX + 定期 OPTIMIZE_INDEX 是維穩關鍵。
4. 善用 SCORE、WEIGHT、FUZZY 提升檢索體驗。