OracleTextを使ってみた2

# 概要

先日OracleTextについて学んだことを下記の記事にまとめましたが、別の使い方についても学んだので本記事にまとめようと思います。

https://qiita.com/taka194/items/e9b3011070181be0df36

前回は`MULTI_COLUMN_DATASTORE`を利用して複数のカラムを横断して検索する方法についてまとめました。今回は`USER_DATASTORE`を利用した検索についてまとめます。

# USER_DATASTOREとは

USER_DATASTOREはPL/SQLプロシージャのIN OUT変数の返り値を検索対象とします。プロシージャの作り方を工夫すれば複数のテーブルを検索対象にしたり、タグ付けをして重み付けの検索をしたりすることができます。
他にもできることはありそうですが、本記事ではこの2つの検索についてまとめようと思います。

# テーブルの作成

従業員テーブル（EMPLOYEE_2）と部署テーブル（BU）を作成します。

### 従業員テーブル

|カラム名|データ型|説明|
|---|---|---|
|EMPLOYEE_CODE|VARCHAR2|従業員を識別するコード|
|EMPLOYEE_NAME|VARCHAR2|従業員の名前|
|BU_CODE|VARCHAR2|所属する部署のコード|
|DUMMY_TEXT|VARCHAR2|全文検索用のカラム|

```sql
CREATE TABLE EMPLOYEE_2(
    "EMPLOYEE_CODE" VARCHAR2(10),
    "EMPLOYEE_NAME" VARCHAR2(100),
    "BU_CODE" VARCHAR2(20),
    "DUMMY_TEXT" VARCHAR2(20)
    );
```

下記のデータを登録します。

|EMPLOYEE_CODE|EMPLOYEE_NAME|BU_CODE|DUMMY_TEXT|
|---|---|---|---|
|EMP_1|坂東　さやか|BU_1||
|EMP_2|伝法谷　智貴|BU_1||
|EMP_3|神田橋　すみれ|BU_2||
|EMP_4|溝渕　輝|BU_3||
|EMP_5|三本木　翔太郎|BU_3||

### 部署テーブル

|カラム名|データ型|説明|
|---|---|---|
|BU_CODE|VARCHAR2|部署を識別するコード|
|BU_NAME|VARCHAR2|部署の名前|

```sql
CREATE TABLE BU(
    "BU_CODE" VARCHAR2(20),
    "BU_NAME" VARCHAR2(100)
    );
```

下記のデータを登録します。

|BU_CODE|BU_NAME|
|---|---|
|BU_1|製品開発部|
|BU_2|製造部|
|BU_3|品質保証部|

# プロシージャの作成

`EMPLOYEE_2_INDEX_PROCEDURE`という名前のプロシージャを作成します。

```sql
CREATE OR REPLACE PROCEDURE EMPLOYEE_2_INDEX_PROCEDURE
(
    r IN ROWID,
    c IN OUT NOCOPY CLOB
)
IS
BEGIN
    for x in (SELECT emp.EMPLOYEE_NAME, bu.BU_NAME FROM EMPLOYEE_2 emp, BU bu WHERE emp.BU_CODE = bu.BU_CODE AND emp.ROWID = r)
    loop
        dbms_lob.writeappend(c, length('<root>'), '<root>');
        dbms_lob.writeappend(c, length('<emp>'), '<emp>');
        dbms_lob.writeappend(c, length(x.EMPLOYEE_NAME), x.EMPLOYEE_NAME);
        dbms_lob.writeappend(c, length('</emp>'), '</emp>');
        dbms_lob.writeappend(c, length('<bu>'), '<bu>');
        dbms_lob.writeappend(c, length(x.BU_NAME), x.BU_NAME);
        dbms_lob.writeappend(c, length('</bu>'), '</bu>');
        dbms_lob.writeappend(c, length('</root>'), '</root>');
    END LOOP;
END;
/
```

このプロシージャを実行すると下記のような結果が返ってきます。（見やすいように整形しています）

```xml
<root>
  <emp>坂東　さやか</emp>
  <bu>製品開発部</bu>
</root>
```

後程説明しますが、このようにXMLのような形式のテキストにしておくことに意味があります。

# その他必要な準備

下記のスクリプトを実行します。

```sql
-- preferenceの作成
BEGIN
-- datastore
ctx_ddl.create_preference('employee_2_datastore', 'user_datastore');
ctx_ddl.set_attribute('employee_2_datastore', 'procedure',  'EMPLOYEE_2_INDEX_PROCEDURE');
ctx_ddl.set_attribute('employee_2_datastore', 'output_type', 'CLOB');

-- wordlist
ctx_ddl.create_preference('employee_2_wordlist', 'BASIC_WORDLIST');
ctx_ddl.set_attribute('employee_2_wordlist','SUBSTRING_INDEX', 'TRUE');

-- section_group
ctx_ddl.create_section_group('employee_2_section_group', 'BASIC_SECTION_GROUP');
ctx_ddl.add_field_section('employee_2_section_group', 'emp', 'emp', TRUE);
ctx_ddl.add_field_section('employee_2_section_group', 'bu', 'bu', TRUE);
END;
/

-- 検索用のインデックスの作成
CREATE INDEX EMPLOYEE_2_IDX01 ON EMPLOYEE_2(DUMMY_TEXT) indextype IS ctxsys.context parameters
('datastore employee_2_datastore section group employee_2_section_group wordlist employee_2_wordlist lexer CTXSYS.BASIC_LEXER stoplist CTXSYS.EMPTY_STOPLIST');
```

スクリプトの内容について簡単に説明します。

## datastore

スクリプトの下記の部分です。

```sql
-- datastore
ctx_ddl.create_preference('employee_2_datastore', 'user_datastore');
ctx_ddl.set_attribute('employee_2_datastore', 'procedure',  'EMPLOYEE_2_INDEX_PROCEDURE');
ctx_ddl.set_attribute('employee_2_datastore', 'output_type', 'CLOB');
```

`employee_2_datastore`という名前で`user_datastore`を作成します。ここで先ほど作成した`EMPLOYEE_2_INDEX_PROCEDURE`と`employee_2_datastore`の関連付けを行います。

## wordlist

[OracleTextを使ってみた](https://qiita.com/taka194/items/e9b3011070181be0df36#wordlist)の記事にまとめた内容と同じです。

## section_group

セクション検索を使用すると、テキスト問合せをドキュメント内のテキストブロックに絞り込むことができます。今回利用する`BASIC_SECTION_GROUP`では、HTMLやXMLのような形式のドキュメント構造がある場合に有効です。スクリプトの下記の記述で、SECTION_GROUPとタグの定義を行います。

```sql
-- section_group
ctx_ddl.create_section_group('employee_2_section_group', 'BASIC_SECTION_GROUP');
ctx_ddl.add_field_section('employee_2_section_group', 'emp', 'emp', TRUE);
ctx_ddl.add_field_section('employee_2_section_group', 'bu', 'bu', TRUE);
```

## インデックスの作成

スクリプトの下記の部分でインデックスの作成を行います。`EMPLOYEE_2`テーブルの`DUMMY_TEXT`に`EMPLOYEE_2_IDX01`を作成します。

```sql
-- 検索用のインデックスの作成
CREATE INDEX EMPLOYEE_2_IDX01 ON EMPLOYEE_2(DUMMY_TEXT) indextype IS ctxsys.context parameters
('datastore employee_2_datastore section group employee_2_section_group wordlist employee_2_wordlist lexer CTXSYS.BASIC_LEXER stoplist CTXSYS.EMPTY_STOPLIST');
```

作成しておいたdatastoreやsection_groupをindexと関連付けます。その他の項目については[OracleTextを使ってみた](https://qiita.com/taka194/items/e9b3011070181be0df36)にてまとめています。

# 検索してみる

検索を実施する前にインデックスの同期処理が必要です。

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

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

## 普通に検索してみる

下記のようなSQLで検索してみます。

```sql
SELECT
    emp.EMPLOYEE_CODE,
    emp.EMPLOYEE_NAME,
    emp.BU_CODE,
    bu.BU_NAME,
    SCORE(1) AS SCORE
FROM
    EMPLOYEE_2 emp,
    BU bu
WHERE
    emp.BU_CODE = bu.BU_CODE
    AND contains(emp.DUMMY_TEXT, '%坂% or %品質%', 1) > 0
ORDER BY SCORE DESC;
```

結果は下記のようになりました。

|EMPLOYEE_CODE|EMPLOYEE_NAME|BU_CODE|BU_NAME|SCORE|
|---|---|---|---|---|
|EMP_1|坂東　さやか|BU_1|製品開発部|6|
|EMP_4|溝渕　輝|BU_3|品質保証部|4|
|EMP_5|三本木　翔太郎|BU_3|品質保証部|4|

EMPLOYEE_2テーブルのEMPLOYEE_NAMEカラムとBUテーブルのBU_NAMEカラムから検索されていそうです。

## withinを利用した検索

withinを利用すると、検索対象となるセクションを指定することができます。先ほどのSQLを下記のように書き換えて`<emp>`セクションからのみ検索するようにしてみます。

```sql
SELECT
    emp.EMPLOYEE_CODE,
    emp.EMPLOYEE_NAME,
    emp.BU_CODE,
    bu.BU_NAME,
    SCORE(1) AS SCORE
FROM
    EMPLOYEE_2 emp,
    BU bu
WHERE
    emp.BU_CODE = bu.BU_CODE
    AND contains(emp.DUMMY_TEXT, '%坂% within emp or %品質% within emp', 1) > 0
ORDER BY SCORE DESC;
```

すると結果は下記のようになり、`<bu>`セクションは検索対象になっていないことがわかります。

|EMPLOYEE_CODE|EMPLOYEE_NAME|BU_CODE|BU_NAME|SCORE|
|---|---|---|---|---|
|EMP_1|坂東　さやか|BU_1|製品開発部|6|

## 重み付け検索

withinを利用すると重みを付けた検索をすることができます。例えば下記のような記述をすると`<emp>`セクションに重みを付けてSCOREを付けます。

```sql
SELECT
    emp.EMPLOYEE_CODE,
    emp.EMPLOYEE_NAME,
    emp.BU_CODE,
    bu.BU_NAME,
    SCORE(1) AS SCORE
FROM
    EMPLOYEE_2 emp,
    BU bu
WHERE
    emp.BU_CODE = bu.BU_CODE
    AND contains(emp.DUMMY_TEXT, '(%坂% within emp) * 10 or %品質% within bu', 1) > 0
ORDER BY SCORE DESC;
```

`<emp>`セクションのスコアを10倍しています。このSQLは下記のような結果になります。

|EMPLOYEE_CODE|EMPLOYEE_NAME|BU_CODE|BU_NAME|SCORE|
|---|---|---|---|---|
|EMP_1|坂東　さやか|BU_1|製品開発部|64|
|EMP_4|溝渕　輝|BU_3|品質保証部|4|
|EMP_5|三本木　翔太郎|BU_3|品質保証部|4|

このようにすることで、重みの付け方を開発者がコントロールし、ヒット率の高いものから順に並べて表示するといったことができるようになります。

# 参考文献

- [Oracle Text 詳細解説](https://www.oracle.com/jp/a/tech/docs/technical-resources/oracletext-ver12.pdf)
- [Oracle® Textアプリケーション開発者ガイド](https://docs.oracle.com/cd/E16338_01/text.112/b61358/toc.htm)