概要
OracleTextはOracleが提供している全文検索の機能です。
ひょんなことからOracleTextを利用している機能を担当することになり、勉強したのでその内容について備忘の意味も込めてまとめておこうと思います。
OracleTextを利用するための準備
sysdba権限で下記の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の作成は下記のスクリプトを利用しました。
-- テーブルの作成
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の下記の部分です。
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の下記の部分です。
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の下記の部分です。
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 |
インデックスの同期
このままではまだ全文検索は利用できません。インデックスの同期処理を行う必要があります。
-- インデックスの同期
CALL CTX_DDL.SYNC_INDEX('EMPLOYEE_IDX01', '2M');
-- 統計情報の収集
ANALYZE TABLE EMPLOYEE COMPUTE STATISTICS;
検索してみる
準備が完了したので検索を実行してみます。OracleTextの検索ではCONTAINS関数を利用します。
この関数は返り値が1以上だと全文検索の条件に一致し、数値が大きいほど一致する箇所が多いです。
返り値の値はSCORE関数で取得できます。
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関数の仕様を参考にさせていただきました。
結果はこちらです。
一行目のデータは他よりも検索条件に一致する箇所が多いと判断され、SCOREが大きくなりました。
このように、複数のカラムから全文検索ができました。
まとめ
複数のカラムを横断しての検索が簡単に実現でき便利な機能だと感じました。今回紹介したのはほんの１部で、他にもさまざまな検索オプションがあったり、検索結果に重み付けをしたりといろいろなことができるようです。他のオプションについても少しずつ勉強していきたいと思いました。