データベースは、どちらの方向に向かってもインデックスを読むことができます。つまり、パイプライン化されたorder byは、スキャンされるインデックスの範囲がorder by句と全く反対の時にも可能になるということです。order by句の
ASCやDESC修飾語がパイプライン化された処理の
邪魔をしそうに思うかもしれませんが、多くのデータベースではインデックスの順序を変える方法を用意しており、パイプライン化された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 (LUW)
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句にASCと
DESC修飾語が混在しているので、その条件を満たしていません。
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句に
インデックスの順序を合わせる簡単な方法が用意されています。具体的には、
インデックスの宣言時もASCやDESCを使えます。
DROP INDEX sales_dt_prCREATE INDEX sales_dt_pr
ON sales (sale_date ASC, product_id DESC)これで、インデックスの順序がorder byと一致したので、
データベースはソート処理をしなくてよくなりました。
- Db2 (LUW)
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 byにASCとDESCを
混在させる場合には、パイプライン化されたorder byを
使う場合と同じようにインデックスを定義する必要があります。
where句でのインデックスの使い方には影響はありません。
図6.4順序が混在したインデックス
ASC/DESC付きのインデックスは、
各列をそれぞれ逆の方向にソートする場合にのみ必要になります。全ての列を逆順にする場合は、インデックスを逆にたどれるので、その必要はありません。
ただし、索引構成表のセカンダリインデックスは例外です。
セカンダリインデックスは、ソート順序の指定があるかどうかに関係なくクラスタリングキーを
インデックスに追加します。クラスタリングキーを逆順にソートする必要がある時、他の列も全て逆順に並べ替える以外の方法はありません。
データベースがインデックスを逆順に読み込むには、そうする他ないのです。
協力してください
この記事が気に入ったら、私の書いた本「SQLパフォーマンス詳解」や私によるトレーニングもきっと気にいるはず。
ASCとDESCに加えてSQL標準では、order by句に対して、あまり知られていないであろうNULLS FIRSTとNULLS LAST修飾語を定義しています。
NULLのソートに対する明示的な制御は、「今のところ」
SQL:2003のオプション拡張として登場しています。
そのため、各データベースでのサポートは十分ではありません。標準ではNULLのソート順を明確に定義していないため、
この事は特に注意すべきと言えます。全てのNULLはソートしたらまとめて現われるべきであると書かれているだけで、
他のエントリの先に出てくるのか、後に出てくるのかは書かれていないのです。厳密に言うと、明確に定義された結果が欲しいなら、order by句でnullになり得る全ての列に対して、
NULLのソート順を定義してやる必要があります。
実際には、このオプショナルな拡張はSQL Server 2019でもMySQL 8.0でも
実装されていません。Oracleは標準にNULLSのソートが登場する前から
これをサポートしていましたが、19cでは
インデックス定義にこれを書く事はできません。Oracleでは、NULLS
FIRSTでソートする場合には、パイプライン化されたorder byは使えません。PostgreSQL(8.3以降)だけは、order by句及びインデックス定義でのNULLS修飾語をサポートしています。
次の表は、この機能の各データベースでのサポート状況を示したものです。
この説明が気に入れば、きっとこの本も 気に入るはず。
