ASC、DESCとNULLS FIRST/LAST


Applies to
DB2Yes
MySQLNo
OracleYes
PostgreSQLYes
SQL ServerYes

データベースは、どちらの方向に向かってもインデックスを読むことができます。 つまり、パイプライン化されたorder byは、 スキャンされるインデックスの範囲がorder by句と 全く反対の時にも可能になるということです。order by句の ASCDESC修飾語がパイプライン化された処理の 邪魔をしそうに思うかもしれませんが、多くのデータベースではインデックスの順序を 変える方法を用意しており、パイプライン化されたorder byに インデックスを使えるようにできます。

次の例では、インデックスを逆順で使用し、日付の逆順および PRODUCT_IDの逆順に並べて、昨日までの売上を取り出します。

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date DESC, product_id DESC

実行計画にも、データベースはインデックスを降順に読んでいると出ています。

DB2
Explain Plan
---------------------------------------------------------------------
ID | Operation                      |                     Rows | Cost
 1 | RETURN                         |                          |  688
 2 |  FETCH SALES                   |     394 of 394 (100.00%) |  688
 3 |   IXSCAN (REVERSE) SALES_DT_PR | 394 of 1009326 (   .04%) |   24

Predicate Information
 3 - STOP ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)

DB2では、インデックスの作成時にDISALLOW REVERSE SCAN句を使うことで、インデックスの逆順での 使用を抑制できます。

Oracle
---------------------------------------------------------------
|Id |Operation                    | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT             |             |  320 |  300 |
| 1 | TABLE ACCESS BY INDEX ROWID | SALES       |  320 |  300 |
|*2 |  INDEX RANGE SCAN DESCENDING| SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

この場合、データベースは最後に一致するエントリを 探すのに、インデックスツリーを使います。 図 6.2にあるように、 「上に向って」リーフノードチェーンをたどるのです。これが、 データベースがリーフノードチェーンを作るのに双方向連結リストを 使う理由です。

図6.2 逆順のインデックススキャン


もちろん、スキャンされるインデックスの範囲は、 order by句と完全に逆順である必要があります。

重要

データベースは、インデックスをどちらの方向からも読むことができます。

次の例では、order by句にASCDESC修飾語が混在しているので、その条件を満たしていません。

SELECT sale_date, product_id, quantity
  FROM sales
 WHERE sale_date >= TRUNC(sysdate) - INTERVAL '1' DAY
 ORDER BY sale_date ASC, product_id DESC

このクエリでは、PRODUCT_IDで降順に並んでいる 昨日の売上を返し、その後また、PRODUCT_IDで降順に並んでいる 今日の売上を返さなくてはなりません。図 6.3では、このプロセスを図示しています。 要求する順序で売上を返すには、データベースはインデックススキャン中に 「ジャンプ」しなくてはなりません。

図6.3 不可能なパイプライン化されたorder by


インデックスは、昨日の売上で最小のPRODUCT_IDを持っている エントリから、今日の売上で最も大きいPRODUCT_IDを持っている エントリへのリンクは持っていません。そのためデータベースは、 明示的なソート処理をしないためにインデックスを使うことはできません。

このような場合、多くのデータベースでは、order by句に インデックスの順序を合わせる簡単な方法が用意されています。具体的には、 インデックスの宣言時もASCDESCを使えます。

  DROP INDEX sales_dt_pr;
CREATE INDEX sales_dt_pr
    ON sales (sale_date ASC, product_id DESC);

これで、インデックスの順序がorder byと一致したので、 データベースはソート処理をしなくてよくなりました。

DB2
Explain Plan
-----------------------------------------------------------
ID | Operation            |                     Rows | Cost
 1 | RETURN               |                          |  675
 2 |  FETCH SALES         |     387 of 387 (100.00%) |  675
 3 |   IXSCAN SALES_DT_PR | 387 of 1009326 (   .04%) |   24

Predicate Information
 3 - START ((CURRENT DATE - 1 DAYS) <= Q1.SALE_DATE)
Oracle
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |  320 |  301 |
| 1 |  TABLE ACCESS BY INDEX ROWID| SALES       |  320 |  301 |
|*2 |   INDEX RANGE SCAN          | SALES_DT_PR |  320 |    4 |
---------------------------------------------------------------

図 6.4は、 新しいインデックスの順序の図です。2つ目の列に対するソートの方向が変わったので、 前の図からは矢印の方向が変わっています。これにより、 最初の矢印は2つ目の矢印の始点を向くようになり、インデックスは 希望の順序に並んでいることが分かります。

重要

order byASCDESCを 混在させる場合には、パイプライン化されたorder byを 使う場合と同じようにインデックスを定義する必要があります。

where句でのインデックスの使い方には影響はありません。

図6.4 順序が混在したインデックス


ASC/DESC付きのインデックスは、 各列をそれぞれ逆の方向にソートする場合にのみ必要になります。全ての列を 逆順にする場合は、インデックスを逆にたどれるので、その必要はありません。 ただし、索引構成表の セカンダリインデックスは例外です。 セカンダリインデックスは、ソート順序の指定があるかどうかに関係なくクラスタリングキーを インデックスに追加します。クラスタリングキーを逆順にソートする必要がある時、 他の列も全て逆順に並べ替える以外の方法はありません。 データベースがインデックスを逆順に読み込むには、そうする他ないのです。

このウェブサイトにぴったりのカップは僕たちのショップにあります。
#見た目もいい感じだし、ここでの僕の仕事を支えてくれています

ASCDESCに加えて SQL標準では、order by句に対して、 あまり知られていないであろうNULLS FIRSTNULLS LAST修飾語を定義しています。 NULLのソートに対する明示的な制御は、「今のところ」 SQL:2003のオプション拡張として登場しています。 そのため、各データベースでのサポートは十分ではありません。 標準ではNULLのソート順を明確に定義していないため、 この事は特に注意すべきと言えます。全てのNULLは ソートしたらまとめて現われるべきであると書かれているだけで、 他のエントリの先に出てくるのか、後に出てくるのかは書かれていないのです。 厳密に言うと、明確に定義された結果が欲しいなら、 order by句でnullになり得る全ての列に対して、 NULLのソート順を定義してやる必要があります。

実際には、このオプショナルな拡張はSQL Server 2012でもMySQL 5.6でも 実装されていません。Oracleは標準にNULLSのソートが登場する前から これをサポートしていましたが、11gでは インデックス定義にこれを書く事はできません。Oracleでは、NULLS FIRSTでソートする場合には、パイプライン化されたorder byは 使えません。PostgreSQL(8.3以降)だけは、order by句及び インデックス定義でのNULLS修飾語をサポートしています。

次の表は、 この機能の各データベースでのサポート状況を示したものです。

この説明が気に入れば、きっと この本も 気に入るはず。

図6.5 データベースごとの機能表


Photo of Markus Winand
Markus Winand氏は、開発者がSQLパフォーマンスを改善するお手伝いをしています。 彼は、SQL Performance Explainedの 著者でもあり、出張トレーニングhttp://winand.at/での リモート講義も 行っています。