Full Scanを速くしちゃう～Oracle Text編～その１
＜Full Scanを速くしちゃう～Oracle Text編～その１＞
ペンネーム： グリーンぺぺ
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機能について解説していきたい。