忍者ブログ
へたれたプログラマの憂鬱

へたれたプログラマの憂鬱

徒然なる日々の日記。 プログラムとお酒の事とか紹介します。
     
>> SQL整形ツールWEB版 [690]  [689]  [688]  [687]  [167]  [324]  [310 Proxy.pac <<
×

[PR]上記の広告は3ヶ月以上新規記事投稿のないブログに表示されています。新しい記事を書く事で広告が消えます。

ATLはWEBプログラマ。

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

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


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

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

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


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


拍手[4回]


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

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万件ほど格納してます。
このテーブル(PKにしかインデックスは張られていない)で、次の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;


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

22776ms

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


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


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


10421ms


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

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


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

ただこの方法だとWhere句の絞りこみカラムと、Select句のカラムを全て複合インデックスに
しないといけないから、まぁ巨大なインデックスになっていくんですけどねぇ^^;


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


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

まぁ後はSQL全体を見直して、絞り込んでから部分一致させると速くなりますが。
PR
この記事にコメントする
お名前:
タイトル:
文字色:
メールアドレス:
URL:
コメント:
パスワード:   Vodafone絵文字 i-mode絵文字 Ezweb絵文字
管理人の眼
ミニミニ不等号ナンプレ
やりこみはコチラ
最新コメント
[10/16 しま]
[03/21 しま]
[01/22 パプティ桝]
[10/18 matsuyoro]
[10/16 Shiro]
広告
Powerd by NINJAブログ / Designed by SUSH
Copyright © へたれたプログラマの憂鬱 All Rights Reserved.
忍者ブログ [PR]