C# 實作 Oracle Text 全文檢索完整範例：從建索引到同步優化

前言

Oracle Text 提供強大的全文檢索能力，支援多語言、模糊比對、評分排序等進階功能。本文提供完整的 C# 範例，涵蓋授權、建表、建索引、查詢、同步與優化全流程。

前置授權（必須由 DBA 執行）

-- 1. 授予 CTXAPP 角色
GRANT CTXAPP TO scott;

-- 2. 授予 CTX_DDL 套件執行權限
GRANT EXECUTE ON CTX_DDL TO scott;

執行全文檢索的使用者必須具備上述權限，否則會遇到 ORA-20000: Oracle Text error: DRG-10599。

建表、載入資料、建立索引

DROP TABLE docs;
CREATE TABLE docs (
  id   NUMBER PRIMARY KEY,
  text VARCHAR2(80)
);

INSERT INTO docs VALUES (1, 'the first doc');
INSERT INTO docs VALUES (2, 'the second doc');
COMMIT;

-- 建立 CONTEXT 索引（預設詞彙分析器）
CREATE INDEX doc_index ON docs(text) INDEXTYPE IS CTXSYS.CONTEXT;

索引建立後，Oracle 自動產生四張內部表：DR$DOC_INDEX$I（倒排索引）、$K、$N、$R。

C# 查詢範例

using System;
using System.Data;
using Oracle.DataAccess.Client; // ODP.NET

namespace OracleTextDemo
{
    class Program
    {
        static void Main()
        {
            string connStr = "Data Source=ora9;User Id=scott;Password=tiger;Unicode=True";
            string sqlStr  = "SELECT id FROM docs WHERE CONTAINS(text, '%FIRST%') > 0";

            using (var conn = new OracleConnection(connStr))
            using (var cmd  = new OracleCommand(sqlStr, conn))
            {
                conn.Open();
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        Console.WriteLine("找到文件 ID: " + reader.GetInt32(0));
                }
            }
        }
    }
}

關鍵點： CONTAINS 回傳分數，必須用 > 0 判斷是否命中（Oracle 不支援布林回傳）。

索引同步與優化（維護排程）

資料異動（INSERT/UPDATE/DELETE）後，索引不會即時更新，需手動或排程同步。

同步

CREATE OR REPLACE PROCEDURE sync_idx IS
BEGIN
  -- 同步：將新詞彙寫入 $I 表
  CTX_DDL.SYNC_INDEX('doc_index', '2M'); -- 2MB 記憶體上限
END;
/

優化

CREATE OR REPLACE PROCEDURE optimize_idx IS
BEGIN
  -- 完整優化：合併片段、移除已刪除詞彙
  CTX_DDL.OPTIMIZE_INDEX('doc_index', 'FULL', NULL, TRUE);
END;
/

DBMS_JOB 排程每 2 分鐘執行一次同步+優化

DECLARE
  v_job NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job       => v_job,
    what      => 'BEGIN sync_idx; optimize_idx; END;',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/720'  -- 1天/720 = 2分鐘
  );
  COMMIT;
END;
/

版本差異注意事項（9i/10g vs 11g+）

升級 11g 時務必重測所有全文查詢邏輯。

結語

Oracle Text 為企業級全文檢索提供零成本、高整合度方案。掌握 CTX_DDL.SYNC_INDEX / OPTIMIZE_INDEX 維護機制，並注意版本差異，即可穩定支援百萬級文件檢索需求。