最初のN行のみ選択するクエリとは、結果を一定の行数だけに限定するクエリです。
これは、結果セットの中から最新あるいは「最高の」エントリを問い合わせる時に使います。効率的な実行のために、このようなランキングは、パイプライン化したorder byを使って実行されるべきです。
クエリの最初の数行のみを取り出す一番シンプルな方法は、最初の数行を取り出した時点で文の実行を終了してしまう事です。しかし残念な事に、
実行計画を準備する時点では、オプティマイザはそれには気付いてくれません。
最適な実行計画を選択するためには、アプリケーションが最終的に全行を取得するのかどうかを、
オプティマイザは知っていなければなりません。パイプライン化されたorder byを使う方が、データベースが各行を別々にフェッチする必要があるとしても、
最初の10行だけを取り出すのなら良い選択でしょう。しかしこの場合は、
明示的なソート処理を行うフルテーブルスキャンが最適な方法という事になってしまいます。つまり、オプティマイザが最適な実行計画を選択するには、
全行を取得する前に文の実行を止めるつもりかどうかを知っている必要があります。
ヒント
クエリの実行結果の全行が必要かどうかを、データベースに伝えましょう。
SQL標準は、長らくこの必要条件を扱わないできました。これに対応する拡張(fetch first)は、SQL:2008で登場したばかりで、IBM DB2、PostgreSQL、SQL Server 2012、Oracle 12cでのみ使用できます。これは、1つはこの機能がノンコアな拡張とされているためでもあり、また、
各データベースが長年にわたって独自の方法でこの機能を実現してきたからでもあります。
次の例は、最新の売上10エントリを問い合わせるクエリを通じて、この拡張の使い方を示したものです。基本的な動きは全て同じで、 最新の売上を始点に全ての売上を取得しています。 それから、それぞれの最初のN行のみを選択するクエリの文法を使って、10行取り出した後にクエリの実行の中断を指示しています。
- DB2
DB2は、バージョン9(LUWとzOS)からSQL標準の
fetch first文法をサポートしています。SELECT * FROM sales ORDER BY sale_date DESC FETCH FIRST 10 ROWS ONLYプロプラエタリな
limitキーワードは、 DB2 LUW 9.7からサポートしています(db2set DB2_COMPATIBILITY_VECTOR=MYSが必要)。- MySQL
MySQLとPostgreSQLは、取り出す行数を制限するのに
limit句を使います。SELECT * FROM sales ORDER BY sale_date DESC LIMIT 10- Oracle
Oracleでは、
fetch first拡張が12cから使えるようになりました。それより前のリリースでは、 結果セットの行数を自動的に制限する疑似列であるROWNUMを使う必要があります。この列をフィルタで使うには、 クエリを以下のようにラップする必要があります。SELECT * FROM ( SELECT * FROM sales ORDER BY sale_date DESC ) WHERE rownum <= 10- PostgreSQL
PostgreSQLは
fetch first拡張を バージョン8.4からサポートしています。それより前から使用できたlimit句も、MySQLの例として挙げたのと同じように 使用可能です。SELECT * FROM sales ORDER BY sale_date DESC FETCH FIRST 10 ROWS ONLY- SQL Server
SQL Serverは、取りだす行数を制限するのに
top句をサポートしています。SELECT TOP 10 * FROM sales ORDER BY sale_date DESC
ここまでのSQL文は全て、最初のN行のみを選択することをデータベースが認識できるようになっている、特別なクエリです。
重要
データベースは、部分結果のみを取得する事を事前に知っている場合のみ、部分結果向けにクエリを最適化できます。
オプティマイザが、実際には最初の10行しか必要ないと分かっている場合は、パイプライン化されたorder byが使えるなら、それを使った実行計画を選択してくれます。
- DB2
Explain Plan ----------------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 24 2 | FETCH SALES | 10 of 1009326 | 458452 3 | IXSCAN (REVERSE) SALES_DT_PR | 1009326 of 1009326 | 2624 Predicate InformationDB2においては、最初のN件のみを選択するクエリの挙動は、
SORT処理がない場合は実行計画で直接見ることはできません(last_explainedビューでSORT (TOP-N)のように括弧内に表示されます。次のコード例に表示されています)。この例の場合、他のフィルタ述語の条件が影響しているのでもないはずなのに(Predicate Informationには何も書かれていません) 行数が減っているので、最初のN件のみを選択するクエリになっている はずだと推測できるでしょう。
- Oracle
------------------------------------------------------------- | Operation | Name | Rows | Cost | ------------------------------------------------------------- | SELECT STATEMENT | | 10 | 9 | | COUNT STOPKEY | | | | | VIEW | | 10 | 9 | | TABLE ACCESS BY INDEX ROWID| SALES | 1004K| 9 | | INDEX FULL SCAN DESCENDING| SALES_DT_PR | 10 | 3 | -------------------------------------------------------------
Oracleの実行計画には、COUNT STOPKEYを使った実行停止が計画されていると表示されています。
データベースは、最初のN行のみのクエリの文法を理解している事が分かります。
ヒント
付録A, 「実行計画」,では、DB2、MySQL、Oracle、PostgreSQL、SQL Serverの各データベースにおける 対応する処理の概要を説明しています。
重要
パイプライン化された最初のN行のみを選択するクエリは、 結果セットの全てを読む必要も、並べ替える必要もありません。
パイプライン化されたorder byに適したインデックスが
SALE_DATEにない場合は、データベースはテーブル全体を読み込み、
並べ替えを行う必要があります。結果の最初の行は、テーブルから最後の行を読み込んだ後にしか出力されません。
- DB2
Explain Plan ----------------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 59835 2 | TBSCAN | 10 of 10 (100.00%) | 59835 3 | SORT (TOP-N) | 10 of 1009326 ( .00%) | 59835 4 | TBSCAN SALES | 1009326 of 1009326 (100.00%) | 59739 Predicate Information- Oracle
-------------------------------------------------- | Operation | Name | Rows | Cost | -------------------------------------------------- | SELECT STATEMENT | | 10 | 59558 | | COUNT STOPKEY | | | | | VIEW | | 1004K| 59558 | | SORT ORDER BY STOPKEY| | 1004K| 59558 | | TABLE ACCESS FULL | SALES | 1004K| 9246 | --------------------------------------------------
この実行計画では、パイプライン化されたorder byには
なっておらず、クライアント側から中断したのと同じぐらい実行は遅いでしょう。それでも最初のN件のみを選択するクエリの方がましな理由としては、
データベースは全ての結果ではなく、最初の10件だけをマテリアライズすれば良いことです。
その方が、明らかにメモリの消費量が少なくなります。Oracleの実行計画ではこの最適化を表すのに、SORT ORDER
BY処理にSTOPKEY修飾語を付けています。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
パイプライン化された最初のN件のみを選択するクエリの優位点は、 目前のパフォーマンス向上だけでなく、スケーラビリティも改善してくれるところにあります。 パイプライン化した実行でない場合、最初のN件のみを選択するクエリの応答時間は、 テーブルサイズに比例して長くなります。パイプライン化した実行の場合は、選択する行数にのみ比例します。言い換えると、 パイプライン化した最初のN件のみを選択するクエリのレスポンスタイムは、テーブルサイズとはほとんど関係なく、常に一定であると言う事です。 Bツリーの深さが深くなった時にだけ、少し遅くなります。
図7.1は、
それぞれのパターンでデータ量が増えていった時のスケーラビリティを表した図です。パイプライン化されていないorder byでは、実行時間が一定の割合で増加してしまっているのが明らかです。
一方で、パイプライン化された方の実行時間は、ほぼ一定です。
図7.1最初のN件のみを選択するクエリのスケーラビリティ
パイプライン化された最初のN件のみを選択するクエリの応答時間は、 テーブルの大きさには比例しませんが、選択する行数には比例して長くなります。行数が2倍になれば、応答時間も2倍になります。この点は、いわゆる 「ページング」を行うために多くの行を選択するクエリにおいて、大きく影響してきます。ページングにおいては、前のページで表示したものも含めて 最初の行から選択し、次のページに必要なデータを取得したらそれ以前のものを捨てるという動作をするためです。これに対しては、別の方法がありますので、 次の節で説明していきます。
この説明が気に入れば、きっとこの本も 気に入るはず。
