OracleTextを使ってみた2

概要

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

前回は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	全文検索用のカラム

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	部署の名前

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という名前のプロシージャを作成します。

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;
/

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

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

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

その他必要な準備

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

-- 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

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

-- 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を使ってみたの記事にまとめた内容と同じです。

section_group

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

-- 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を作成します。

-- 検索用のインデックスの作成
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を使ってみたにてまとめています。

検索してみる

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

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

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

普通に検索してみる

下記のような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>セクションからのみ検索するようにしてみます。

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を付けます。

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 詳細解説
• Oracle® Textアプリケーション開発者ガイド