Oracle Textを使用したチェックボックスでの検索
Oracle(RDB)でのチェックボックス検索はけっこう鬼門だったりする。
チェックボックスで持っている項目をカラムとして列持ちしてあげると
項目がなくなったり追加の際にカラム自体の追加、削除をすることになり、表ロックがかかりメンテナンスが難しい。
また、項目を行持ちにすると、クエリの複雑化、データ量の増加、メンテナンスがやっかいなことになる。
そんな時の一つの手として、
Oracle Standard Editionでも使用可能な全文検索(Oracle Text)を使用して、
WEBサービスなどのチェックボックス検索を行ったのでメモ。
Oracle Textとは・・・
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を使用したチェックボックス検索は速度的にも遅くもないですし、
列持ちで問題であった項目の追加や削除もデータの変更だけで容易に可能。
行持ちで問題であったクエリは簡潔化し、データ量も抑えることが可能。
注意すべき点としては同じような語句の場合、全文検索なので取得してしまうので、
検索させる語句を調整してあげる必要あり。
(十万
を検索すると五十万
も拾ってしまう)