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)