kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracle Textを使用したチェックボックスでの検索

ORACLE SE

Oracle(RDB)でのチェックボックス検索はけっこう鬼門だったりする。

チェックボックスで持っている項目をカラムとして列持ちしてあげると
項目がなくなったり追加の際にカラム自体の追加、削除をすることになり、表ロックがかかりメンテナンスが難しい。
また、項目を行持ちにすると、クエリの複雑化、データ量の増加、メンテナンスがやっかいなことになる。

そんな時の一つの手として、
Oracle Standard Editionでも使用可能な全文検索(Oracle Text)を使用して、
WEBサービスなどのチェックボックス検索を行ったのでメモ。　　

Oracle Textとは・・・

Oracleカーネルに組み込まれた、全文検索およびドキュメント分類のためのエンジン　　

Oracle Textは以下がとーーっても細かく解説されており、参考になります。　　

http://www.oracle.com/technetwork/jp/ondemand/db-technique/oracletext-ver12-351879-ja.pdf

Oracle Textのインストールする

  • 11gR2をインストールすると明示的にはずさない限りoracle textが有効になっている
  • ctxsysユーザを復活させる
alter user ctxsys account unlock;
conn CTXSYS/change_on_install
新しいパスワード登録
  • ORAUSERユーザに対して CTXAPPロールを付与
grant ctxapp to ORAUSER;

これでOracle Textの使用が可能となる

テーブル準備

例えば以下のようなクレジットカード検索チェックボックス画面があるとして 

年会費　　　□ 無料　　□ 有料
種類　　　　□ VISA　　□ MASTER　□ AMEX　□ JCB
利用枠　　　□ ～10万　□ ～100万 □ ～1000万　

クレジットカード検索用のテーブルを作成する。
1. card_idはカード番号を格納。
2. infoはチェックボックスで選択される項目を格納。

  • テーブル作成
 create table card_info(card_id int,
                        info varchar2(4000),
                        constraint pk_card_info primary key(card_id));
  • プリファレンス作成
execute ctx_ddl.create_preference('card_info_text', 'JAPANESE_VGRAM_LEXER');
  • 指定したテーブルのカラム対してOracleText用インデックスを作成
create index textidx_card_info on card_info( info ) 
　　　　　　　　indextype is ctxsys.context parameters ('lexer card_info_text');
  • データ追加
insert all 
  into card_info values (1,'無料 VISA MASTER 十万')
  into card_info values (2,'有料 VISA MASTER 千万')
  into card_info values (3,'無料 有料 AMEX 百万')
  into card_info values (4,'無料 有料 VISA JCB 千万')
  into card_info values (5,'有料 AMEX JCB 十万')
  into card_info values (6,'有料 MASTER 百万')
  into card_info values (7,'無料 JCB 十万')
select * from dual;
  • データ追加だけではOracle Text用インデックスに反映しないのsyncを実施
EXEC CTX_DDL.SYNC_INDEX('textidx_card_info ');

チェックボックス検索

  • 年会費：無料　種類：VISAのカードを検索する 
年会費　　　■ 無料　　□ 有料
種類　　　　■ VISA　　□ MASTER　□ AMEX　□ JCB
利用枠　　　□ ～10万　□ ～100万 □ ～1000万　

select * from card_info where contains ( info, '無料 and VISA') > 0;

   CARD_ID INFO
---------- ------------------------------
         1 無料 VISA MASTER 十万
         4 無料 有料 VISA JCB 千万
  • 年会費：無料または有料　種類：MASTERまたはJCB　のカードを検索する 
年会費　　　■ 無料　　■ 有料
種類　　　　□ VISA　　■ MASTER　□ AMEX　■ JCB
利用枠　　　□ ～10万　□ ～100万 □ ～1000万　　

select * from card_info where contains ( info, 
                           '(無料 or 有料) and (MASTER or JCB)') > 0;

   CARD_ID INFO
---------- ----------------------------------------
         1 無料 VISA MASTER 十万
         2 有料 VISA MASTER 千万
         4 無料 有料 VISA JCB 千万
         5 有料 AMEX JCB 十万
         6 有料 MASTER 百万
         7 無料 JCB 十万

シソーラスの追加

ここで利用枠は～1000万は～100万と～10万を含み、
～100万は～10万を含むシソーラス（同義語）を追加する。 　 （シソーラスを作成しなくても検索する項目名で分けて取得できるようにしてもよいと思う）

BEGIN
 CTX_THES.CREATE_THESAURUS(
   name => 'thes',
   casesens => FALSE
 );
END;
/
  • 同義語設定
exec CTX_THES.CREATE_RELATION ('thes','千万','SYN','百万');
exec CTX_THES.CREATE_RELATION ('thes','千万','SYN','十万');
exec CTX_THES.CREATE_RELATION ('thes','百万','SYN','十万');
  • 種類：MASTER 利用枠：～1000万　のカードを検索する 
年会費　　　□ 無料　　□ 有料
種類　　　　□ VISA　　■ MASTER　□ AMEX　□ JCB
利用枠　　　□ ～10万　□ ～100万 ■ ～1000万　　

select * from card_info where contains ( info, 'SYN(千万,thes) and MASTER') > 0;

   CARD_ID INFO
---------- ----------------------------------------
         1 無料 VISA MASTER 十万
         2 有料 VISA MASTER 千万
         6 有料 MASTER 百万

まとめ

Oracle Textを使用したチェックボックス検索は速度的にも遅くもないですし、
列持ちで問題であった項目の追加や削除もデータの変更だけで容易に可能。
行持ちで問題であったクエリは簡潔化し、データ量も抑えることが可能。
注意すべき点としては同じような語句の場合、全文検索なので取得してしまうので、
検索させる語句を調整してあげる必要あり。
（十万を検索すると五十万も拾ってしまう）