2010/01/16 (Sat) 部分一致の高速化

ＡＴＬはＷＥＢプログラマ。

最近ブログをサボってしまったので、今日はデータベースの記事でも書いて、ご機嫌をとってみようかと＾＾；

部分一致の高速化について考察したいと思います。


部分一致の高速化の件は、「大きなカラムの場合」という記事で、テーブル設計時の記事を書きました。

設計フェーズで検討が出来ればこの方法でも問題ないのですが、
こういった部分一致での問題が出るはテストフェーズというのが世の常です。

テストフェーズにおいて、テーブル設計を変えるなんてことは許されることがまずないので、
今回は大きなテーブルサイズにしてしまったときの部分一致の高速化のお話。


環境はOracle10gXE。
1回ＳＱＬ文を実行するたびに、バッファキャッシュと共有プールをクリアして計測してます。
 

まずは今回の検証に利用するテーブルを紹介。

create table atl.HOGE (
    PK              number(10)      NOT NULL,
    FOO             varchar2(512)   NOT NULL,
    BAR             varchar2(512)   NOT NULL,
    BAZ             varchar2(512)   NOT NULL,
    CONSTRAINT PK_HOGE PRIMARY KEY(PK)
) tablespace ATL_SPACE;
 

データは100万件ほど格納してます。
このテーブル（ＰＫにしかインデックスは張られていない）で、次のSQL文を実行すると・・・・
select PK from HOGE where FOO like '%aaa%';

実行結果
SELECT STATEMENT   Cost = 57095
    TABLE ACCESS FULL HOGE

23276ms

とまぁこんな結果。23秒です。
100万件にテーブルフルスキャンがかかっているので、こんなものでしょう。遅いです。


このテーブルを高速化してみたいと思います。
まずは誤りの事例からご紹介。

インデックスを張ってみましょう。
CREATE INDEX IDX_HOGE1 ON
    atl.HOGE (FOO)
    TABLESPACE ATL_SPACE；

この状態で同じＳＱＬ文を実行した時の実行結果は次の通り。
SELECT STATEMENT   Cost = 57095
    TABLE ACCESS FULL HOGE

22776ms

・・・遅いです。速くなってないですねー。実行計画も変わってません。
ダメなインデックスの張り方ですね。


次はインデックスが（多少なりとも）効くように複合インデックスを張ってみます。
（IDX_HOGE1は別途削除してます）
CREATE INDEX IDX_HOGE2 ON
    atl.HOGE (FOO,PK)
    TABLESPACE ATL_SPACE

この状態で同じＳＱＬ文を実行した時の実行結果は次の通り。
SELECT STATEMENT   Cost = 19056
    INDEX FAST FULL SCAN IDX_HOGE2

10421ms

多少速くなってますね。10秒まで速くなりました。
これでも遅いですが、前述のものよりはましでしょう＾＾；
実行計画を見ても、テーブルフルスキャンからインデックスフルスキャンに変わってます。

ポイントは、FOOとPKに張った複合インデックスですね。


実行したＳＱＬは、ＦＯＯで部分一致して、ＰＫのデータを取得しています。
テーブルにアクセスしないために、複合インデックスを張ってインデックスからのスキャンとしています。

ただこの方法だとＷｈｅｒｅ句の絞りこみカラムと、Ｓｅｌｅｃｔ句のカラムを全て複合インデックスに
しないといけないから、まぁ巨大なインデックスになっていくんですけどねぇ＾＾；


というわけでＡＴＬの部分一致考察でした。


紹介した方法以外で、部分一致の高速化は、
・oracle text
くらいでしょうか。

まぁ後はＳＱＬ全体を見直して、絞り込んでから部分一致させると速くなりますが。