津島博士のパフォーマンス講座 indexページ▶▶

津島博士のパフォーマンス講座 
第9回 良いSQLについて

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF
皆さんこんにちは、まだまだ暑いですが体調はいかがでしょうか。私はどうにか頑張っております。
今回は、詳しく聞きたいというご要望のありました「パフォーマンスの良いSQL文ついて」まとめて説明しようと思います。すべてのパターンについて解説はできませんが、第4回で少し説明した実行計画を使用して説明しますので、実行計画などを見るときの参考にもして下さい。

■1.良いSQL文とは
まずは、良いSQL文とはどのようなものなのかを説明します。簡単に言うとリソースをできるだけ使用しないようにすることですが、なかなか難しいと思いますので参考になるポイントをまとめてみました。
高速にアクセスするには、索引を使用してアクセスするのが一般的ですから、索引が使用されないSQL文を記述しないことです。そのため、どのようなときに索引が使用されないかを知っておくことも大事になります。それから、テーブルの結合やソート処理はリソースへの負荷が大きいので、効率の悪いアクセスをしないように注意が必要です。これについては、オプティマイザが良いSQL文に置き換えてくれる場合もあると思いますが、知っておくと役に立ちそうなことを、「3.その他の参考になること」として少しまとめておきました。

■2.索引を使用しない
索引を使用しないSQL文は多くのリソースを使用します。これは良く言われていることなのでご存じな方も多いと思います。次に示すSQL文は索引を使用しません(または効率の悪い索引アクセスになります)ので、できるだけ記述しないようにして下さい。
  • NULL比較やNOT(!=)を使用
  • 列を演算している
  • 後方一致(中間一致)条件
  • INリストまたはORを使用
それでは、それぞれについて説明していきます。

(1)NULL比較やNOT(!=)を使用している
Bツリー索引では、NULL値は格納されていませんのでNULL比較(IS NULL、IS NOT NULL)をWHERE句の条件に指定すると索引は使用されせん。また、NOT(!=、<>なども)を使用した場合も索引は使用されません。これは、指定した条件に一致しないものを求めることになるため、フルスキャンの方が速いという判断からです。そのため、NULL比較をしなくても良いようにNULL値に意味を持たせないようにして下さい。また、NOTは別の条件に変えることを検討して下さい(例えば、INやORに置き換えるなどです。ただし、「(4)INリストまたはORを使用している」で説明しているように、値指定が多くなると効率が悪くなるので、注意して下さい)。できれば使用しなくても良いように設計して下さい。

(2)列を演算(関数を使用)している
以下のような列の演算をしているSQL文では索引は使用されません。これは、演算結果の値とその値のオプティマイザ統計が格納されていないため索引を使用することができないからです。
SQL> SELECT * FROM tab1 WHERE c1 / 10 < 10000;
SQL> SELECT * FROM tab1 WHERE SUBSTR(c2,3,4) = 'AAAA';
 この場合は、以下のように列を演算しない同等なSQL文に置き換えて下さい。
SQL> SELECT * FROM tab1 WHERE c1 < 100000;
 置き換えができない場合には、以下のようなファンション索引を作成することで索引が使用されます。ただし、あまり闇雲に作成するのではなく、できるだけ演算や関数を使用しないようにして下さい(第6回で説明したように、索引が多いと更新時のオーバーヘッドが増えてしまいます)。
SQL> CREATE INDEX ix_c2 ON tab1 (SUBSTR(c2,3,4));
 それから、明示的に関数を使用していない場合でも、比較する型が異なることによる暗黙的なデータ型変換が行われている場合もあります。その場合でも索引は使用されませんので注意して下さい(例えば、文字型に数字を比較するとTO_NUMBER関数が実行されてしまうなどです)。

(3)後方一致(中間一致)条件を使用している
LIKEまたは複合索引で先頭列を指定しない後方一致条件(中間一致条件)のSQL文では索引は使用されません。例えば、テーブル tab1に列c1,c2の複合索引(複数列索引)と列c3の索引が作成されていたとすると、以下のようなSQL文では索引は使用されません。これは、大小比較でデータを絞り込んでいくので先頭が決まらないと絞り込めないためです。
テーブル tab1
・列c1,c2の複合索引(複数列索引)
・列c3の索引
SQL> SELECT * FROM tab1 WHERE c3 LIKE '%AA';
SQL> SELECT * FROM tab1 WHERE c3 LIKE '%A%';
SQL> SELECT * FROM tab1 WHERE c2 = 10;
 この場合は、以下のように索引を作成することで索引を使用することができます。
  • 列c2に索引を作成する
  • 列c3に(チェックする文字位置が固定であればLIKEをSUBSTR関数にして)ファンクション索引を作成する
 ただし、これも索引の数が増えてしまい、更新時のオーバーヘッドが増加してしまいます。Oracle9iからは「索引スキップ・スキャン」機能により索引を使用するようになりましたので、このような索引を作成する必要がありません。ただし、前方一致条件よりは効率は悪いので注意して下さい(できるだけ高速にしたい処理には前方一致条件による索引アクセスにして下さい)。
索引スキップ・スキャンについて
ご存知ない方のために、ここで索引スキップ・スキャンについて簡単に説明します。
索引は先頭からの条件指定でないと使用されませんが、この機能を使用することで指定していない先頭の部分をスキップして索引を使用します。具体的にどのようにして索引を使用するかというと、列c1,c2の複合索引があるテーブルに次のSQL文を実行したとします。
    SQL> SELECT * FROM tab1 WHERE c2 = 10;
 このとき列c1の値が'A'と'B'だとすると、内部的には以下のSQL文のように実行して索引スキャンを行います。つまり、先頭の値のカーディナリティが高いと効率が悪いことになります(そのような場合や条件のヒット率が高い場合はオプティマイザが選択しないようにすると思います)。
    SQL> SELECT * FROM tab1 WHERE c1 = 'A' AND c2 = 10
     2   UNION ALL
     3  SELECT * FROM tab1 WHERE c1 = 'B' AND c2 = 10;
(4)INリストまたはORを使用している
これは索引が使用されない場合もありますが、効率が悪い索引アクセスになる場合もあります。以下のようにINリストやORを使用している場合には、1回のフルスキャン又はアクセス範囲の大きい索引範囲スキャンを行う場合よりも、値指定された回数の索引一意スキャン(又はアクセス範囲の小さい索引範囲スキャン)の方が効率が良い場合があります。
SQL> SELECT * FROM tab1 WHERE c1 = 100 OR c2 = 200 OR C3 = 300;

実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  TABLE ACCESS (FULL) OF 'TAB1'
 以下のSQL文のようにUNION ALLを使用することで、そのようにアクセスさせることができます。ただし、INリストに多数の値指定をしていたり、ORで多数連結している場合はUNION ALLのオーバーヘッドが大きくなるので注意して下さい。
SQL> SELECT * FROM tab1 WHERE c1 = 100
  2   UNION ALL
  3  SELECT * FROM tab1 WHERE c2 = 200
  4   UNION ALL
  5  SELECT * FROM tab1 WHERE c3 = 300;
 これについては、オプティマイザがOR-EXPANSION(OR拡張)として自動的に行ってくれるようになっています(以下のように実行計画にCONCATENATIONと出力されるとOR拡張が行われたことを意味します)ので、明示的にUNION ALLを指定する必要はありませんが、このような動作になっていることは知っておくと良いと思います。OR拡張を行っていないときはUSE_CONCATヒントで明示的にOR拡張を行ってみると効果があるかもしれません。また、NO_EXPANDヒントを使用してOR拡張を使用しないようにもできます。オプティマイザ統計が正確でないために誤った判断をする場合もありますので知っておくと便利かと思います。
実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  CONCATENATION
2  1   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' 
3  2    INDEX (UNIQUE SCAN) OF 'PK_TAB1' (UNIQUE) 
4  1   TABLE ACCESS (BY INDEX ROWID) OF 'TAB1' 
5  4    INDEX (RANGE SCAN) OF 'IX_TAB1' (NON-UNIQUE) 
...
■3.その他の参考になること
ここからは、基本はオプティマイザが行ってくれるので気にする必要はないかもしれませんが、常にオプティマイザが行ってくれるとは限りませんので(第5回で説明したオプティマイザ統計が常に正確であるとは限りませんので)、次のようなSQL文を書き換えて速くする方法や考え方を説明しておきます。
  • INとEXISTSについて
  • 結合は件数を絞り込んでから
  • ソート処理はできるだけ少ない件数で
 それでは、それぞについて説明していきます。

(1)INとEXISTSについて
以下の二つのSQL文は実行結果が同じですが、実行方法は異なります。
SQL> SELECT * FROM tab1 WHERE c1 IN (SELECT c1 FROM tab2);                          ...【1】
SQL> SELECT * FROM tab1 WHERE EXISTS (SELECT * FROM tab2 WHERE tab1.c1 = tab2.c1);  ...【2】
 上記の【1】のSQL文は、以下のSQLと同じように実行されます。実行計画を見ると一意性処理(HASH UNIQUE)が行われているのが分かります。一意性処理は、副問合せの結果件数が多いと負荷が大きくなるので注意する必要があります。そのような場合は、オプティマイザが一意性処理しないように(【2】のように)実行すると思いますが、知っておくと便利だと思います。それから、「(2)結合は件数を絞り込んでから」の説明にあるように、重複データが多い場合は効率が良い可能性があることを知っておいて下さい。
SQL> SELECT tab1.* FROM tab1, (SELECT DISTINCT c1 FROM tab2) t2 WHERE tab1.c1 = t2.c1;

実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  HASH JOIN
2  1   TABLE ACCESS (FULL) OF 'TAB1')
3  1   VIEW
4  3    HASH UNIQUE
5  4     TABLE ACCESS (FULL) OF 'TAB2'
(2)結合は件数を絞り込んでから
件数が多いテーブルの結合は負荷が大きいため、件数を少なくしてから行った方が良いです。ただし、すべてのSQL文がそのように処理されるとは限りません。例えば、以下のSQL文はWHERE句の条件(B.c2 = 10)であまりデータを絞り込めない場合には、そのようには実行されません。ただし、tab2.c1(GROUP BYを行わないテーブルの結合列)が重複キーが多い場合には、SQL文を書き換えることで件数を少なくしてから結合することができます。
SELECT A.c2, count(*) FROM tab1 A, tab2 B WHERE A.c1 = B.c1 AND B.c2 = 10 GROUP BY A.c2;

実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  HASH GROUP BY
2  1   HASH JOIN
3  2    TABLE ACCESS (FULL) OF 'TAB1'
4  2    TABLE ACCESS (FULL) OF 'TAB2'
 以下のSQL文に変更すると効率が良くなる場合があります(これは少し高度だと思いますが、同じ結果になることを理解できるようになると、いろいろ工夫できるようになると思います。さすがにオプティマイザもここまでは変換しませんから、私は最後の手段として結構行うことがあります)。
SQL> SELECT A.c2, SUM(ct) FROM tab1 A, (SELECT c1, count(*) ct FROM tab2 WHERE c2 = 10 GROUP BY c1) 
 B2> WHERE A.c1 = B.c1 GROUP BY A.c2;

実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  HASH GROUP BY
2  1   HASH JOIN
3  2    TABLE ACCESS (FULL) OF 'TAB1'
4  2    VIEW
5  4     HASH GROUP BY
6  5      TABLE ACCESS (FULL) OF 'TAB2'
(3)ソート処理はできるだけ少ない件数で行う
ソート処理は負荷が大きい処理になりますので、できるだけ少ないデータで行った方が高速になります。例えば、上位100件だけ出力したいときなどは以下のように記述すると良いです。ソート領域に100件しか格納しないので、ソート処理の負荷を軽減できます。
SQL> SELECT * FROM (SELECT ... FROM tab1 ORDER BY c1) WHERE ROWNUM < 101;
 それから、パラレル処理を行う場合には注意が必要です。パラレル・ソート処理を行うときは、パラレス・スレーブ・プロセスに対してデータをレンジ分割するので、データ値によっては偏ってしまいます。そのような場合にはパラレル処理が効率よく動作しません(あまりパラレル効果がありません)。以下のSQL文の実行計画を見るとGROUP BYとORDER BYは1回のソートで処理されているので(SORT GROUP BY)、一見効率良いように思えるかもしれませんが、GROUP BYをハッシュ分割で実行(HASH GROUP BY)してレコード数を少なく(GROUP BY列の重複度が高くないと少なくならないので注意)してからORDER BY(SORT ORDER BY)を行った方が効率よい場合もあります。これはハッシュ分割の方が偏りが少ないからです。アクセスする件数が多く、ソート処理でプロセスにデータが偏っている場合は効果が良い可能性があることを知っておいて下さい。
SQL> SELECT c1, COUNT(*) FROM tab1 GROUP BY c1 ORDER BY c1;

実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  SORT GROUP BY
2  1   TABLE ACCESS (FULL) OF 'TAB1'
 以下のSQL文のようにインラインビューを使用することでHASH GROUP BYを行うようにできます。NO_MERGEヒントは念のため(インラインビューがマージされないようにするために指定しています)です。
SQL> SELECT /*+ NO_MERGE(A) */ * FROM (SELECT c1,COUNT(*) FROM tab1 GROUP BY c1) A ORDER BY A.c1;

実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  SORT ORDER BY
2  1   VIEW
3  2    HASH GROUP BY 
4  3     TABLE ACCESS (FULL) OF 'TAB1'
USE_HASH_AGGREGATIONヒントでも同じことができます。
SQL> SELECT /*+ USE_HASH_AGGREGATION */ c1, COUNT(*) FROM tab1 GROUP BY c1 ORDER BY c1;

実行計画
----------------------------------------------------------
0    SELECT STATEMENT
1  0  SORT ORDER BY
2  1   HASH GROUP BY 
3  2    TABLE ACCESS (FULL) OF 'TAB1'
■4.ANSI準拠の結合文
最後に、ANSI準拠の結合文について説明します。これは、どちらが速いとかではないのですが、間違いやすいので説明しようと思います。特にOracle8i以前のバージョンから使用している方は、外部結合はOracle独自の構文(以下のようにWHERE句で外部結合演算子(+)を指定する。この例はtab1が優先テーブルとしてすべてのレコードを返します)を使用すると思われるため、あまり使い慣れていないかもしれませんので、間違いないようにして下さい。慣れるとこちらの方が使いやすいと思います。
SQL> SELECT * FROM tab1,tab2 WHERE tab1.a = tab2.a(+);
 特に、外部結合するとき結合前に非優先テーブルを条件で絞り込むようなSQL文だと間違いやすいので注意して下さい。これをANSI準拠スタイルで記述するとどうなると思いますか。以下の二つのSQL文を想像すると思いますが(一見同じ結果になると思われるかもしれませんが)、結果が異なってしまう場合がありますので注意して下さい。
SQL> SELECT * FROM tab1 left outer join tab2 ON tab1.a = tab2.a WHERE tab2.b = 10;  ...【1】
SQL> SELECT * FROM tab1 left outer join tab2 ON tab1.a = tab2.a AND tab2.b = 10;    ...【2】
 【1】は外部結合した後に条件チェック(tab2.b = 10)を行いますが、【2】は外部結合しながら条件チェックを行います。この例だと、実行結果は以下のように異なってしまい、【2】の方が正しいということになります。これは、外部結合は優先テーブルのすべての行を返すために、非優先テーブルに存在しなかったものをNULLとします。そのため、結合後に非優先テーブルを条件チェックするとNULLのレコードも排除されてしまうからです(この例だと、tab1.aの値が2,4,5のレコードです)。
SQL> SELECT * FROM tab1 left outer join tab2 ON tab1.a = tab2.a WHERE tab2.b = 10;  ...【1】

   tab1.a tab1.b tab2.a tab2.b
   ------ ------ ------ ------
        1     10      1     10
        3     30      3     10
SQL> SELECT * FROM tab1 left outer join tab2 ON tab1.a = tab2.a AND tab2.b = 10;    ...【2】

   tab1.a tab1.b tab2.a tab2.b
   ------ ------ ------ ------
        1     10      1     10
        2     20
        3     30      3     10
        4     40
        5     50
 参考までに、私は以下のように書きます。こちらの方が分かりやすいと思います。
SQL> SELECT * FROM tab1 left outer join (SELECT * FROM tab2 WHERE b = 10) B ON tab1.a = B.a;
■5.おわりに
今回はSQL文のノウハウの一部について説明しました。他にも(PL/SQLなど)いろいろありますが、またの機会とさせていただきます。まだ暑いですが皆さま体調に気を付けて下さい。次回も頑張りますのでよろしくお願いします。質問をお待ちしています。
それでは、次回まで、ごきげんよう。
img_tsushima.gif ■津島博士より
長年に渡りデータベースの構築やパフォーマンスチューニングなどに従事し、最近では若手エンジニアの育成および大規模データベース案件などの支援に従事しております。今までの経験が少しでもお役に立てればと思い、この連載を始めることに致しました。できるだけ長く続けたいと思いますのでよろしくお願いいたします。

Oracle Databaseは、技術の進化により非常に扱いやすくなったと思います。私自身も昔のバージョンを使用したころに比べると非常に楽になったと感じています。いろいろと進化したとはいえパフォーマンス問題が発生しなくなった訳ではありません。今でも多くの担当者が色々と苦労していると思います。その中でスキルや機能を知らずに苦労している場合もあるように思いますので、ここで紹介していけたらと考えています。

この連載では、このようなOracle技術者(データベース技術者)の方へのアドバイスとして様々なパフォーマンス問題を題材に解説していこうと考えています。既にデータベース運用を行っている管理者、これから管理者を目指す方までを対象に、様々な疑問に対して少しでも何かの手助けになればと願っています。できるだけ読者の皆様からの疑問に答えていきたいと思っておりますので(問合せなどの具体例を使用して説明した方が分かりやすいと思いますので)、パフォーマンス問題に関する様々な質問をお願いいたします。

津島博士の記事についてのご質問はこちらまでお願いいたします。

津島博士のパフォーマンス講座 indexページ▶▶