Full Scanを速くしちゃう~Oracle Text編~その1

<Full Scanを速くしちゃう~Oracle Text編~その1>
ペンネーム: グリーンぺぺ

2008年に8回連載したFull Scanを速くしちゃうの続編として
またいくつかの手法を紹介したい。
ちなみに前回の内容は↓

Vol.249,250	db_file_multiblock_read_count
Vol.251-253	マルチブロックサイズ
Vol.254		Index Fast Full Scan
Vol.255,256	Materialized View

今回は4回に別けてOracle Textを紹介する。
Here we go!

■環境
Oracle 11.2.0.2 64bit on RHEL5.4

■検証
以下のような楽曲情報が格納されている表より、歌詞のサビの一部で
曲情報を抽出するSQLを実行し性能比較してみる。
SQL> desc ranking
 名前                                      型
 ----------------------------------------- ----------------------------
 RANK                                      NUMBER
 TITLE                                     VARCHAR2(40)
 ARTIST                                    VARCHAR2(120)
 WRITER                                    VARCHAR2(40)
 COMPOSER                                  VARCHAR2(40)
 ARRANGER                                  VARCHAR2(40)
 LYRIC                                     VARCHAR2(3000)

◆歌詞で全件検索
中間一致の検索は索引があっても全件検索になってしまう。

-- lyric列に索引作成
create index ix_lyric on ranking(lyric);

-- lyric列で中間一致検索
select TITLE,ARTIST from ranking where lyric like '%バイバイ バイヨン%';

TITLE                ARTIST
-------------------- --------------------
虹色のバイヨン       氷川きよし

経過: 00:00:10.30

実行計画
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |  8192 |  9368K|  7934   (1)| 00:01:36 |
|*  1 |  TABLE ACCESS FULL| RANKING |  8192 |  9368K|  7934   (1)| 00:01:36 |
-----------------------------------------------------------------------------

◆歌詞で全文検索
今回紹介するOracle TextはOracleの全文検索エンジンである。
Enterprise Edition/Standard Editionに関係なくデフォルトで導入されているので
そのまま利用できる。オプションライセンス費用も必要ない。

-- プリファレンスの作成
exec ctx_ddl.create_preference('japanese_lexer', 'JAPANESE_LEXER');

-- lyric列に索引作成
CREATE INDEX ix_lyri2 ON ranking(lyric) 
INDEXTYPE IS ctxsys.context
PARAMETERS('lexer japanese_lexer');

-- lyric列で全文検索
select TITLE,ARTIST from ranking 
where contains(lyric,'バイバイ バイヨン')>0;

TITLE                ARTIST
-------------------- --------------------
虹色のバイヨン       氷川きよし

経過: 00:00:00.31

実行計画
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    82 | 97006 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| RANKING  |    82 | 97006 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | IX_LYLR2 |       |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

■まとめ
Oracle Textの機能を利用することで歌詞検索は10秒から0.31秒へ高速化
することができた。次回Oracle Text機能について解説していきたい。