有很多時候，使用 instr 和 like 是很理想的，特別是搜尋僅跨越很小的表的時候。然而透過這些文字定位的方法將導致全表掃描，對資源來說消耗比較昂貴，而且實現的搜尋功能也非常有限，因此對海量的文字資料進行搜尋時，建議使用 oracle 提供的全文檢索功能。

# 原理

Oracle 的全文檢索實現邏輯，透過 lexer 詞法分析器，講把值的 term 找出來。記錄在一組的 DR$ 開頭的表裡面（主要是 DR$xxxI 表），主要記錄 term 的位置，frequency 頻率，hash 等資訊，oracle 根據謂詞，找到對應的 term。

# 分析器種類

- basic_lexer（預設的分析器）：英語和大多數西歐語言使用空格分隔詞。
- chinese_vgram_lexer：專用漢語分析器，支援所有漢字字符集（ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8）因為採用了分詞的方法，可以查到所有的分詞，效率差
- chinese_lexer：用於從中文文字中提取符號的詞法分析器。與chinese_vgram_lexer 相比，這個 lexer 提供了以下好處：
  - 生成更小的索引
  - 更好的查詢響應時間
  - 生成真實世界的令牌，從而獲得更好的查詢精度
  - 支援停止的詞

> 追求效率那麼使用 CHINESE_LEXER，如果追求準確度那麼使用 CHINESE_VGRAM_LEXER（推薦使用chinese_vgram_lexer）

# 操作

### 建立

```plsql
-- 授權執行許可權：
GRANT EXECUTE ON "CTXSYS"."CTX_DDL" TO "BEIJING";

-- 建立文字解析器
exec ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

-- 驗證
SELECT * FROM CTX_PREFERENCES WHERE PRE_NAME='LEXER';

-- 建立全文索引
CREATE INDEX ind_lot ON lot(lot_id) indextype is ctxsys.context parameters('lexer my_lexer');
```

### 使用

```sql
select * from lot where contains(lot_id,'北京')>0
```

### 過濾詞

```plsql
exec ctx_ddl.create_stoplist('北京');
```

### 刪除

```sql
刪除全文索引
Drop index index_name force;
```

# 維護全文索引

對於 CTXSYS.CONTEXT 索引，當應用程式對基表進行 DML 操作後，對基表的索引維護是必須的。索引維護包括索引同步和索引最佳化。

在索引建好後，我們可以在該使用者下查到 Oracle 自動產生了以下幾個表：(假設索引名為 myindex)：

- DR$myindex$I：該表中儲存的其實就是 Oracle 分析你的文件後，生成的 term 記錄在這裡，包括 term 出現的位置、次數、hash 值等。
- DR$myindex$K
- DR$myindex$R
- DR$myindex$N

當基表中的被索引文件發生 insert、update、delete 操作的時候，基表的改變並不能馬上影響到索引上直到同步索引。

### 檢視改動

```sql
SELECT PND_INDEX,
       PND_ROWID,
       TO_CHAR(PND_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') PND_TIMESTAMP
  FROM CTX_USER_PENDING;
```

### 同步

將新的 term 儲存到 I 表；

```plsql
ctx_ddl.sync_index(
  idx_name IN VARCHAR2 DEFAULT NULL
  memory IN VARCHAR2 DEFAULT NULL,
  part_name IN VARCHAR2 DEFAULT NULL
  parallel_degree IN NUMBER DEFAULT 1)
```

- idx_name：索引名稱
- memory：指定同步索引需要的記憶體。預設是系統引數 DEFAULT_INDEX_MRMORY。指定一個大的記憶體的時候可以加快索引效率和查詢速度，且索引有較少的碎片。
- part_name：同步那個分割槽索引
- parallel_degree：並行同步索引。設定並行度

##### 示例

```plsql
exec ctx_ddl.sync_index('ind_lot','500M','p1');
```

##### 自動

```plsql
DECLARE
  VARIABLE jobno number;
BEGIN
  DBMS_JOB.SUBMIT(:jobno,
                  'ctx_ddl.sync_index(''ind_lot'');',
                  SYSDATE,
                  'SYSDATE + (1/24/6)');
  commit;
END;           
/
```

### 最佳化

清除 I 表的垃圾，主要是將已經被刪除的 term 從 I 表刪除。

##### 示例

```plsql
exec ctx_ddl.optimize_index('ind_lot,'FULL);
```

##### 自動

```plsql
DECLARE
  VARIABLE jobno number;
BEGIN
  DBMS_JOB.SUBMIT(:jobno,
                  'ctx_ddl.optimize_index(''ind_lot'',''FULL'');',
                  SYSDATE,
                  'SYSDATE + 1');
  commit;
END;           
/
```

Oracle：全文索引

相關文章