ORDER BY LIMIT を含むクエリのパフォーマンス最適化では、まずストリーミング実行計画を得て、不要なデータの読み取りと処理を削減することが望まれます。ブロッキング演算子を回避できない場合や、他のコストの高いシナリオが発生した場合でも、LIMITが少数の行数を返す特性を活用し、実行時の不要な高コストを削減して最適化できます。
ソートの排除
order by limit を含む単純な単一テーブルクエリでは、結合インデックスを作成することで、インデックスを使用した高速なスキャンと同時にソートを排除し、ストリーミングプランを実現して、フィルター条件を満たす行の大量スキャンを回避できます。
Q3_1_1:
create table t1(c1 int, c2 int, c3 int, c4 int, pk int primary key);
create index idx on t1(c1, c2, c3);
select * from t1 where c1 = 1 and c3 > 0 order by c2 limit 10;
クエリ Q3_1_1 は、c1 = 1 の等価条件を利用してクエリ範囲を抽出し、インデックスで高速にスキャンできます。c1 上の等価条件により、idx インデックスを使用したスキャンでも、c2 でソートされた結果セットを直接取得できます。10 行のデータをスキャンした後、計画の実行を早期に終了できます。
注意点として、クエリ Q3_1_1 には c3 > 0 述語があります。もし (c1, c3, c2) の順序でインデックスを作成すれば、インデックスを直接使用してクエリ範囲を抽出し、c3 > 0 のフィルタリングを実現できます。しかし、この場合は c2 でソートされた結果セットを得ることができません。c3 > 0 のフィルタリング効果が高い場合、ソートを排除してストリーミングプランを実現してデータスキャンを削減しても、ほとんど最適化効果はありません。むしろ、idx(c1, c2, c3) を使用すると、計画が c3 > 0 のフィルタリング効果を十分に活用できなくなります。
スキャン・計算コストの削減
大きなワイドテーブルやLOBなどのデータ型を含むテーブルのスキャンにおいて、クエリに order by と limit が含まれる場合、オプティマイザーは遅延マテリアライゼーションの最適化戦略を使用することで、大規模なワイドテーブルの大量データのスキャンを削減できます。
以下のクエリ Q3_2_1 の場合、フィルター条件およびソート列はすべてインデックス idx 上にあります。まずインデックス idx を通じてソート列、フィルター条件列、主キーをスキャンし、ソート後に実際に返す必要がある行を取得します。その後、主キーを用いてメインテーブルをスキャンしてすべての列を返します。order by と limit を含む複雑なクエリについても、同様の戦略でクエリを書き換えることができます。
Q3_2_2 では no_use_late_materialization により遅延マテリアライゼーションの最適化が禁止されています。この場合、インデックスを通じてテーブルに戻り、フィルター条件を満たすすべてのマッチング行を読み取る必要があります。
Q3_2_1:
create table t1(c1 int, c2 int, c3 int, c4 int, pk int primary key);
create index idx on t1(c1, c2, c3);
select /*+index(t idx) */ * from t1 t where c1 > 0 order by c2 limit 10;
==============================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
----------------------------------------------
|0 |NESTED-LOOP JOIN| |10 |14855|
|1 | TOP-N SORT | |10 |14842|
|2 | TABLE SCAN |t(idx) |93114 |11972|
|3 | TABLE GET |t1_alias|1 |2 |
==============================================
Outputs & filters:
-------------------------------------
0 - output([t.c1], [t.c2], [t1_alias.c3], [t1_alias.c4], [t.pk]), filter(nil), rowset=256,
conds(nil), nl_params_([t.pk])
1 - output([t.pk], [t.c1], [t.c2]), filter(nil), rowset=256, sort_keys([t.c2, ASC]), topn(10)
2 - output([t.pk], [t.c1], [t.c2]), filter(nil), rowset=256,
access([t.pk], [t.c1], [t.c2]), partitions(p0)
3 - output([t1_alias.c3], [t1_alias.c4]), filter(nil), rowset=256,
access([t1_alias.c3], [t1_alias.c4]), partitions(p0)
Q3_2_2:
select /*+index(t idx) no_use_late_materialization*/ * from t1 t
where c1 > 0 order by c2 limit 10;
========================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
----------------------------------------
|0 |TOP-N SORT | |10 |265257|
|1 | TABLE SCAN|t(idx)|93114 |262387|
========================================
Outputs & filters:
-------------------------------------
0 - output([t.c1], [t.c2], [t.c3], [t.c4], [t.pk]), filter(nil), rowset=256, sort_keys([t.c2, ASC]), topn(10)
1 - output([t.pk], [t.c1], [t.c2], [t.c3], [t.c4]), filter(nil), rowset=256,
access([t.pk], [t.c1], [t.c2], [t.c3], [t.c4]), partitions(p0)