Oracle Text 全文搜尋應用完整建置指南

Oracle Text 簡介

Oracle Text 內建於所有 Oracle Database 版本（含免費 XE），提供企業級全文搜尋：支援布林運算、模糊比對、Soundex、詞幹還原、多語言斷詞、數百種文件格式（PDF、Office）、混合結構化/非結構化查詢、文件級授權、結果排序等。所有功能透過標準 SQL 與 PL/SQL API 操作，無需外部搜尋引擎。

核心架構：索引管道

索引建立過程分五階段：

1. 資料存取：從資料庫、檔案系統、Web 抓取原始資料。
2. 過濾：將各種格式轉為純文字。
3. 分段：保留文件結構中繼資料。
4. 詞法分析：依語言切分詞彙。
5. 建立索引：將 Token 寫入倒排索引。

各階段可透過 CTX_DDL 偏好設定自訂。

安裝與授權

-- 建立使用者並授權 CTXAPP 角色
CREATE USER ot_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE, CTXAPP TO ot_user;

檔案全文索引範例

-- 1. 基礎表：儲存檔案路徑與格式
CREATE TABLE files (
  id         NUMBER PRIMARY KEY,
  issue_id   NUMBER,
  path       VARCHAR2(255) UNIQUE,
  ot_format  VARCHAR2(6)  -- NULL=自動偵測, 'IGNORE'=略過
);

-- 2. 建立 FILE_DATASTORE 偏好
BEGIN
  CTX_DDL.CREATE_PREFERENCE('file_ds', 'FILE_DATASTORE');
  CTX_DDL.SET_ATTRIBUTE('file_ds', 'PATH', '/data/docs');
END;
/

-- 3. 建立 CONTEXT 索引
CREATE INDEX file_idx ON files(path) 
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE file_ds FORMAT COLUMN ot_format');

結構化資料多欄位索引

-- 1. 定義 USER_DATASTORE 程序：彙整多欄位文字
CREATE PACKAGE ot_search AS
  PROCEDURE issue_filter(rid IN ROWID, tlob IN OUT NOCOPY CLOB);
END;
/

CREATE PACKAGE BODY ot_search AS
  PROCEDURE issue_filter(rid IN ROWID, tlob IN OUT NOCOPY CLOB) IS
  BEGIN
    FOR c1 IN (SELECT author, summary, description FROM issues WHERE ROWID = rid) LOOP
      DBMS_LOB.WRITEAPPEND(tlob, LENGTH(c1.summary)+1, c1.summary || ' ');
      DBMS_LOB.WRITEAPPEND(tlob, LENGTH(c1.author)+1, c1.author || ' ');
      DBMS_LOB.WRITEAPPEND(tlob, LENGTH(c1.description), c1.description);
    END LOOP;
  END;
END;
/

-- 2. 建立偏好
BEGIN
  CTX_DDL.CREATE_PREFERENCE('issue_ds', 'USER_DATASTORE');
  CTX_DDL.SET_ATTRIBUTE('issue_ds', 'PROCEDURE', 'OT_SEARCH.ISSUE_FILTER');
  CTX_DDL.SET_ATTRIBUTE('issue_ds', 'OUTPUT_TYPE', 'CLOB');
END;
/

-- 3. 建立索引
CREATE INDEX issue_idx ON issues(ot_version) 
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('DATASTORE issue_ds');

查詢語法

-- 基本布林查詢
SELECT id FROM issues WHERE CONTAINS(ot_version, 'color AND pink', 1) > 0;
SELECT id FROM issues WHERE CONTAINS(ot_version, 'jane OR john', 1) > 0;

-- 進階功能
SELECT * FROM docs WHERE CONTAINS(text, 'fuzzy(goverment)', 1) > 0;      -- 模糊比對
SELECT * FROM docs WHERE CONTAINS(text, '$manage', 1) > 0;               -- 詞幹還原
SELECT * FROM docs WHERE CONTAINS(text, 'neardogcat', 1) > 0;            -- 近接搜尋
SELECT * FROM docs WHERE CONTAINS(text, 'test%', 1) > 0;                 -- 萬用字元

索引維護

-- 同步增量變更
EXEC CTX_DDL.SYNC_INDEX('issue_idx', '2M');

-- 排程自動同步（每小時）
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'SYNC_ISSUE_IDX',
    job_type => 'PLSQL_BLOCK',
    job_action => 'CTX_DDL.SYNC_INDEX(''ISSUE_IDX'', ''2M'');',
    repeat_interval => 'FREQ=HOURLY',
    enabled => TRUE
  );
END;
/

-- 優化索引（合併碎片、移除刪除標記）
EXEC CTX_DDL.OPTIMIZE_INDEX('issue_idx', 'FULL', 2);

錯誤追蹤

SELECT * FROM CTX_USER_INDEX_ERRORS WHERE INDEX_NAME = 'ISSUE_IDX';

強制重建特定文件索引

UPDATE files SET path = path WHERE id = 4; -- 觸發索引欄位變更
EXEC CTX_DDL.SYNC_INDEX('file_idx');