ROWIDを利用したインデックス風のアクセス

Amazonであまりに星がたくさんついていたので気になって買ってみました。

Effective Oracle by Design (Oracle Press)

で、この本におもしろい例があったのでちょっと載せてみます。（本の内容そのままではなく若干説明しやすいように変えてます。）

従業員を、名前や電話番号やE-MAILアドレスなどで問い合わせたいとする。検索は%HOGE%のようなlike検索で、大文字小文字関係なしで行いたい。従業員の数は多い。高速に問い合わせたいが、%HOGE%のような中間一致の検索ではインデックスが使えない。

そこで、常にバッファキャッシュに乗るような小さなテーブルを作り、それをインデックス代わりに使用する。

create table fast_emp pctfree 0 cache as
select 
upper(first_name) || '/' || upper(last_name) || '/' || upper(email) || '/' || phone_number search_string,
rowid row_id
from employees;

このテーブルは、検索条件の文字列と、ROWIDの2つのカラムだけを持つ。検索条件の文字列では検索条件に含めたい元のテーブルのカラムを連結している。

問い合わせるときは、こんな感じで。名前やE-MAILのどこかに「SH」が含まれるとヒットする。従業員テーブルにはROWIDで直接アクセスすることになるので、FAST_EMPはまさにインデックスの代わり。

select *
from employees
where rowid in
  (select row_id
   from fast_emp
   where search_string like '%SH%'
   and rownum < 100);

この本で、似たような例が少なくとも2回でてくるので著者のお気に入りの方法なのかも。

ちなみに著者によると、関数ベースの索引を使う方法や、interMediaを使う方法や、従業員テーブルにカラムを追加する方法を試した上で、上記の方法が適切だと判断したそうです。