OracleText 結合したテーブルに対し全文検索を行う方法

概要

結合したテーブルに対しOracleTextの全文検索を行うためには、ストアドプロシージャを作成しIndexに設定する必要があります。
Oracleの公式サイトにも情報がありますが、より分かりやすくまとめてみました。
公式サイト Oracle12c
Oracle Textの使い方

以下のようなテーブルに対し、AとBのNameを全文検索し、AのIDを取得する方法です。

※NameはNot Nullとする。

ストアドプロシージャについて

CROB型のデータに全文検索したい文字列を連結し、AテーブルのROWIDを紐づけます。

A

A_Id	Name
A0	人事部
A1	総務部

Dummyはデータ更新時に、Indexへ反映するための項目です。
A,BテーブルのUpdate時にDummyを更新してください。Dummyは日付等でよいです。

B

B_Id	A_Id	Name
B0	A0	採用グループ
B1	A1	環境グループ
B2	A1	庶務グループ

ストアドのデータイメージ

ROWID	CROB
1	人事部採用グループ
2	総務部環境グループ 総務部庶務グループ

SQL

create_procedure.sql

CREATE OR REPLACE PROCEDURE ORACLE_TEXT_CREATOR (p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB)
IS BEGIN FOR r IN (
  SELECT A.Name As A_NAME, B.Name As B_NAME
  FROM   A INNER JOIN B ON A.A_Id = B.A_Id
  WHERE  A.ROWID = p_rowid
)
 LOOP
  DBMS_LOB.writeappend (p_clob, LENGTH (r.A_NAME), r.A_NAME);
  DBMS_LOB.writeappend (p_clob, LENGTH (r.B_NAME), r.B_NAME);
  DBMS_LOB.writeappend (p_clob, 1, ' '); 
 END LOOP;
END;

作成したプロシージャーにctxsysの権限を付与します。

grant_procedure.sql

GRANT EXECUTE ON ORACLE_TEXT_CREATOR TO ctxsys;

ctx_ddlについて

LEXERを指定し、DATASTOREに先ほど作成したストアドプロシージャーを設定します。

create_ctxddl.sql

BEGIN
ctx_ddl.create_preference('LEXER',     'JAPANESE_LEXER');
ctx_ddl.create_preference('DATASTORE', 'user_datastore');
ctx_ddl.set_attribute    ('DATASTORE', 'procedure', 'ORACLE_TEXT_CREATOR');
ctx_ddl.set_attribute    ('DATASTORE', 'output_type', 'CLOB');
END;

Indexについて

先ほど作成したLEXERとDATASTOREを指定し、Indexを作成します。
Commit時に同期する形です。

create_index.sql

create index ORACLE_TEXT on A ( DUMMY ) 
indextype is ctxsys.context
  PARAMETERS ('
    datastore      DATASTORE
    lexer          LEXER
    sync (on commit)
  ');
exit;

検索について

以下の検索でA1が取得できます。

select.sql

select A_Id from A 
where CONTAINS(DUMMY, '環境') > 0;