OracleTextを使ってみた

# 概要

OracleTextはOracleが提供している全文検索の機能です。

ひょんなことからOracleTextを利用している機能を担当することになり、勉強したのでその内容について備忘の意味も込めてまとめておこうと思います。

# OracleTextを利用するための準備

sysdba権限で下記のSQLを実行します。

```sql
SQL> @?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK;
```

# テーブルとインデックスの作成

OracleTextではテーブルに検索用の特殊なINDEXを作成し、それを利用して検索を実施します。

従業員の情報を管理するEMPLOYEEテーブルを作成します。

|カラム名|データ型|説明|
|---|---|---|
|EMPLOYEE_CODE|VARCHAR2|従業員を識別するコード|
|NAME|VARCHAR2|従業員の名前|
|NAME_HIRAGANA|VARCHAR2|従業員の名前（ひらがな）|
|NAME_ALPHABET|VARCHAR2|従業員の名前（アルファベット）|
|DUMMY_TEXT|VARCHAR2|全文検索用のカラム|

このテーブルにおいて、下記のカラムから全文検索できるようにします。

- EMPLOYEE_CODE
- NAME
- NAME_HIRAGANA
- NAME_ALPHABET

テーブルとINDEXの作成は下記のスクリプトを利用しました。

```sql
-- テーブルの作成
CREATE TABLE EMPLOYEE(
    "EMPLOYEE_CODE" VARCHAR2(10),
    "NAME" VARCHAR2(100),
    "NAME_HIRAGANA" VARCHAR2(100),
    "NAME_ALPHABET" VARCHAR2(100),
    "DUMMY_TEXT" VARCHAR2(20),
    "PRC_DATE" DATE Default SYSDATE NOT NULL
    );

-- 全文検索に必要なプリファレンスの作成
BEGIN
ctx_ddl.create_preference('employee_datastore',  'multi_column_datastore');
ctx_ddl.set_attribute('employee_datastore',  'columns',  'EMPLOYEE_CODE, NAME, NAME_HIRAGANA, NAME_ALPHABET');
ctx_ddl.create_preference('employee_wordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('employee_wordlist','SUBSTRING_INDEX', 'TRUE');
END;
/

-- 検索用のインデックスの作成
CREATE INDEX EMPLOYEE_IDX01 ON EMPLOYEE(DUMMY_TEXT) indextype IS ctxsys.context parameters
('datastore employee_datastore wordlist employee_wordlist lexer CTXSYS.BASIC_LEXER stoplist CTXSYS.EMPTY_STOPLIST');
```

OracleText独自のものについて下記で簡単にまとめます。

## DATASTORE

SQLの下記の部分です。

```sql
ctx_ddl.create_preference('employee_datastore',  'multi_column_datastore');
ctx_ddl.set_attribute('employee_datastore',  'columns',  'EMPLOYEE_CODE, NAME, NAME_HIRAGANA, NAME_ALPHABET');
```

DATASTOREにはいくつか種類がありますが、ここでは複数のカラムを検索対象とする`MULTI_COLUMN_DATASTORE`を利用します。

`employee_datastore`という名前のプリファレンスを作成し、columns属性で検索対象としたいカラムを指定します。

## WORDLIST

SQLの下記の部分です。

```sql
ctx_ddl.create_preference('employee_wordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('employee_wordlist','SUBSTRING_INDEX', 'TRUE');
```

WORDLISTは検索を高速にするために指定するもののようです。型は`BASIC_WORDLIST`の1種類のみのようなのでこれを指定しています。

`employee_wordlist`という名前のプリファレンスを作成し、SUBSTRING_INDEX属性を有効にしています。

SUBSTRING_INDEX属性を有効にするとワイルドカードを利用した部分一致の検索を高速に実行できるようになるようです。

## INDEXの作成

SQLの下記の部分です。

```sql
CREATE INDEX EMPLOYEE_IDX01 ON EMPLOYEE(DUMMY_TEXT) indextype IS ctxsys.context parameters
('datastore employee_datastore wordlist employee_wordlist lexer CTXSYS.BASIC_LEXER stoplist CTXSYS.EMPTY_STOPLIST');
```

`EMPLOYEE_IDX01`という名前のインデックスをDUMMY_TEXTカラムに対して作成します。

MULTI_COLUMN_DATASTOREではインデックスに指定された列ではなく、DATASTOREのcolumns属性に指定されたカラムが検索対象になります。

このスクリプトで事前に作成しておいた`employee_datastore`と`employee_wordlist`をインデックスに関連付けます。

その他の記述については以下にまとめます。

### LEXER

LEXERは検索対象のテキスト文字列からトークン（索引情報の最小単位）を生成する方法を指定するためのものです。

いろいろ種類はありますが、ここでは`BASIC_LEXER`を利用します。

### STOPLIST

STOPLISTは特定の文字列を索引情報から除外する場合に利用します。例えば英語の冠詞の「a」や「the」など検索対象を特定するのに役に立たないものを除外するなどです。

今回は特に除外したい文字列がないのでこの機能を無効にするために`CTXSYS.EMPTY_STOPLIST`を指定します。

# データの登録

EMPLOYEEテーブルには下記のデータを入れました。

|EMPLOYEE_CODE|NAME|NAME_HIRAGANA|NAME_ALPHABET|
|---|---|---|---|
|YAMA001|山田太郎|やまだたろう|YAMADA TARO|
|YAMA002|小山田太郎|おやまだたろう|OYAMADA TARO|
|YAMA003|小山太郎|こやまたろう|KOYAMA TARO|
|YAMA004|小田太郎|おだたろう|ODA TARO|

# インデックスの同期

このままではまだ全文検索は利用できません。インデックスの同期処理を行う必要があります。

```sql
-- インデックスの同期
CALL CTX_DDL.SYNC_INDEX('EMPLOYEE_IDX01', '2M');

-- 統計情報の収集
ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS;
```

# 検索してみる

準備が完了したので検索を実行してみます。OracleTextの検索では`CONTAINS`関数を利用します。
この関数は返り値が1以上だと全文検索の条件に一致し、数値が大きいほど一致する箇所が多いです。
返り値の値は`SCORE`関数で取得できます。

```sql
SELECT
    EMPLOYEE_CODE,
    NAME,
    NAME_HIRAGANA,
    NAME_ALPHABET,
    SCORE(1) AS SCORE
FROM
    EMPLOYEE
WHERE
    contains(DUMMY_TEXT,
        '%KO% or %小%',
        1) > 0
ORDER BY SCORE DESC;
```

ワイルドカードと演算子を利用しました。演算子については[Oracle Text まとめ 全文検索　CONTAINS関数の仕様](https://qiita.com/wagase/items/f4ea3dcdcdbfbe6d0f31)を参考にさせていただきました。

結果はこちらです。

![sample01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/960294/c5732aad-fdb4-4272-747f-88b17a701dec.png)

一行目のデータは他よりも検索条件に一致する箇所が多いと判断され、SCOREが大きくなりました。

このように、複数のカラムから全文検索ができました。

# まとめ

複数のカラムを横断しての検索が簡単に実現でき便利な機能だと感じました。今回紹介したのはほんの１部で、他にもさまざまな検索オプションがあったり、検索結果に重み付けをしたりといろいろなことができるようです。他のオプションについても少しずつ勉強していきたいと思いました。

# 参考文献

- [Oracle Text 詳細解説](https://www.oracle.com/jp/a/tech/docs/technical-resources/oracletext-ver12.pdf)
- [Oracle Text まとめ 全文検索　CONTAINS関数の仕様](https://qiita.com/wagase/items/f4ea3dcdcdbfbe6d0f31)