Oracle TEXTの全文検索について使ってみた

概要

• 業務でoracleTEXTの全文検索機能を利用すると従来の曖昧検索SQLより性能がよい
  今後の業務でも使っていきたいし、
  皆様にも情報を共有したい目的で記事を書きました。

実行環境

• Windows 10
• Oralce 12c
• SQLDeveloper

Oracle TEXTとは

• oracle TEXTの詳細解説は以下のリンクで確認することができます。
• Oracle TEXT詳細解説

Oracle TEXTの書き方

業務では使用しています個人PCにOracleインストールしていないため、その時の思いましただけを書いて、コードの説明を付ける感じで進めます
Oracle TEXTを使うために以下のものを作る必要があります。

• １、プロシージャ作成
• ２、プリファレンス作成
• ３、索引作成

プロシージャ作成

プロシージャ作成

CREATE OR REPLACE PROCEDURE PROC_INDEX_FOR_MYWORD (p_rowid IN ROWID, p_clob IN OUT NOCOPY CLOB) IS
BEGIN
    FOR r IN (
        SELECT
            emp.ename,
            dep.dname
        FROM
            emp
        INNER JOIN dep ON
            emp.dep_Cd = dep.dep_Cd
        WHERE
            emp.ROWID = p_rowid
    )
    LOOP
        DBMS_LOB.writeappend (p_clob, LENGTH (r.ename), r.ename);
        DBMS_LOB.writeappend (p_clob, 1, ' '); 
        DBMS_LOB.writeappend (p_clob, LENGTH (r.dname), r.dname);
        DBMS_LOB.writeappend (p_clob, 1, ' '); 
    END LOOP;
END;

ここで名前はPROC_INDEX_FOR_MYWORDのプロシージャを作りました、全文検索対象の項目はempテーブルのunameとdepテーブルのdnameです、
この二つのテーブルを結合して、検索されるキーワードがどっちにあればヒットされる感じです。次にプリファレンスを作ります。

プリファレンス作成

プリファレンス作成

BEGIN
  CTX_DDL.CREATE_PREFERENCE('JVL1_LEXER','JAPANESE_VGRAM_LEXER');
  CTX_DDL.CREATE_PREFERENCE('MY_DATASTORE', 'user_datastore');
  CTX_DDL.SET_ATTRIBUTE ('MY_DATASTORE', 'procedure', 'PROC_INDEX_FOR_MYWORD');
  CTX_DDL.SET_ATTRIBUTE ('MY_DATASTORE', 'output_type', 'CLOB');
END;

• 説明するとここではプリファレンス作成し、データストアの各設定を行っている
• JAPANESE_VGRAM_LEXER：日本語用レクサー（解析エンジン）
• user_datastore：データストア
• PROC_INDEX_FOR_MYWORD：プロシージャ名
• CLOB:DBMS_LOBパッケージ、(BLOB、CLOB、およびNCLOBを操作するためのサブプログラムを提供)

インデックスの作成

インデックスの作成

CREATE INDEX [インデックス名] ON [テーブル名] ([カラム名])
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('DATASTORE MY_DATASTORE　LEXER JVL1_LEXER SYNC (ON COMMIT)');

• 特定のテーブルの項目にインデックスを作り、前回作った'MY_DATASTORE'データストアに紐づける
• ここでは全文検索対象にしたいテーブルの項目を複数設定することができる、例えば

CREATE INDEX emp_index ON emp (ename)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('DATASTORE MY_DATASTORE　LEXER JVL1_LEXER SYNC (ON COMMIT)');
CREATE INDEX dep_index ON dep (dname)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('DATASTORE MY_DATASTORE　LEXER JVL1_LEXER SYNC (ON COMMIT)');

検索SQLの作成

--wordという文字を検索している
-- ename,dnameのどっちかにword文字があればヒットされる
SELECT
   emp.ename,
   dep.dname
FROM
   emp
INNER JOIN dep ON
  　emp.dep_Cd = dep.dep_Cd
WHERE CONTAINS (ename, 'word') > 0

その他SQL

--プロシージャの削除例
DROP PROCEDURE ORACLE_TEXT_CREATOR; 
--プリファレンスの削除
begin
ctx_ddl.drop_preference('jvl');
end;
--indexの削除
drop index PROMO_PACK_idx;

終了