クエリにLIMIT句が含まれる場合、生成された実行計画にブロッキング演算子が含まれているかどうかは、実行パフォーマンスに大きな影響を与えます。ソートは一般的なブロッキング演算子であり、クエリにORDER BY句が含まれる場合や、生成された計画でソートアルゴリズムに基づく演算子の割り当てが必要な場合（MERGE DISTINCT、MERGE GROUP BY、MERGE JOINなど）、指定されたソートキーに従って順序付けられた（中間）結果セットを取得するために、ソート演算子の割り当てを試みる必要があります。不必要なソート演算子の生成を避け、既に順序付けられた中間結果セットを最大限に活用するため、オプティマイザーはいくつかの順序関連情報を維持し、ソート演算子の割り当てを試みる際に最適化を行います。
プランツリーにおける順序
実行計画において、演算子の入力順序は順序付けられている場合があります。ベーステーブルスキャンの場合、この順序はテーブル構造インデックスによって生成されます。他の演算子の場合、この順序はソート演算子の出力順序に由来する可能性があります。ORDER BYで明示的に要求される出力順序とは異なりますが、ソートアルゴリズムを使用するほとんどの演算子では、入力順序を調整でき、その順序は演算子の出力順序に継承されます。選択ソート型演算子で使用される順序を最適化することで、複数回のソートを回避できます。
オプティマイザーは、ソート型演算子で使用される順序を決定する際、既存の入力順序と以降に必要となる可能性のある順序から、最終的に使用すべき順序を導き出します。以下のクエリを例にとると、GROUP BYでMERGE GROUP BY演算子を使用する場合、演算子の入力順序におけるc1, c2の前後の位置やソート方向を任意に調整できます。
Q2_1_1では、インデックスidxの選択によりc1, c2の順序が生成され、ソート演算子の発生を回避し、ORDER BY c1, c2で必要なソート演算子の割り当ても回避しています。Q2_1_2では、Q2_1_1に対してORDER BYを調整し、主キーの使用を指定しています。MERGE GROUP BYは、ORDER BY c2, c1で必要な順序をそのまま演算子の集約入力順序として使用し、GROUP BYの下にソート演算子を割り当てます。Q2_1_3では、Q2_1_2と比較して主キーの使用を指定していないため、コストに基づいてMERGE GROUP BYが直接使用できるidxによって生成されたc1, c2の順序を生成し、集約後にORDER BY c2, c1にソート演算子を割り当てます。
create table t1(c1 int, c2 int, c3 int, c4 int, pk int primary key);
create index idx on t1(c1, c2, c3);
Q2_1_1:
select /*+no_use_hash_aggregation*/ c1, c2, sum(c3)
from t1 t
group by c2, c1
order by c1, c2;
==========================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------
|0 |MERGE GROUP BY| |7072 |10965|
|1 | TABLE SCAN |t(idx)|78858 |8124 |
==========================================
Outputs & filters:
-------------------------------------
0 - output([t.c1], [t.c2], [T_FUN_SUM(t.c3)]), filter(nil), rowset=256,
group([t.c1], [t.c2]), agg_func([T_FUN_SUM(t.c3)])
1 - output([t.c1], [t.c2], [t.c3]), filter(nil), rowset=256,
access([t.c1], [t.c2], [t.c3]), partitions(p0)
Q2_1_2:
select /*+no_use_hash_aggregation full(t)*/ c1, c2, sum(c3)
from t1 t
group by c1, c2
order by c2, c1;
========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
----------------------------------------
|0 |MERGE GROUP BY| |7072 |41410|
|1 | SORT | |100000 |37988|
|2 | TABLE SCAN |t |100000 |8026 |
========================================
Outputs & filters:
-------------------------------------
0 - output([t.c1], [t.c2], [T_FUN_SUM(t.c3)]), filter(nil), rowset=256,
group([t.c2], [t.c1]), agg_func([T_FUN_SUM(t.c3)])
1 - output([t.c2], [t.c1], [t.c3]), filter(nil), rowset=256,
sort_keys([t.c2, ASC], [t.c1, ASC])
2 - output([t.c1], [t.c2], [t.c3]), filter(nil), rowset=256,
access([t.c1], [t.c2], [t.c3]), partitions(p0)
Q2_1_3:
select /*+no_use_hash_aggregation index(t idx)*/ c1, c2, sum(c3)
from t1 t
group by c1, c2
order by c2, c1;
===========================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-------------------------------------------
|0 |SORT | |7072 |12771|
|1 | MERGE GROUP BY| |7072 |10965|
|2 | TABLE SCAN |t(idx)|78858 |8124 |
===========================================
Outputs & filters:
-------------------------------------
0 - output([t.c1], [t.c2], [T_FUN_SUM(t.c3)]), filter(nil), rowset=256,
sort_keys([t.c2, ASC], [t.c1, ASC])
1 - output([t.c2], [t.c1], [T_FUN_SUM(t.c3)]), filter(nil), rowset=256,
group([t.c1], [t.c2]), agg_func([T_FUN_SUM(t.c3)])
2 - output([t.c1], [t.c2], [t.c3]), filter(nil), rowset=256,
access([t.c1], [t.c2], [t.c3]), partitions(p0)
ソート演算子の割り当てと最適化
期待されるソートを得るためにソート演算子を割り当てようとする際、ソート演算子の出力が既に順序付けられている可能性があります。まず、入力順序とその他の情報を確認し、入力順序が期待される出力順序を満たせるかどうかを判断します。以下の3つのクエリでは、order by 句により最終的な結果の順序付けが必要ですが、インデックスまたは主キーが提供する順序と一致するため、最終的にはソート演算子の割り当ては不要です：
Q2_2_1:idxインデックスは(c1, c2, c3)の順序を提供でき、出力順序はorder by c1, c2と一致します。Q2_2_2:idxインデックスは(c1, c2, c3)の順序を提供できますが、c1 = 4述語が含まれているため、出力順序はorder by c2と一致します。Q2_2_3: 主キーは(pk)の順序を提供します。主キーの一意性により、出力順序はorder by pk, c3, c2, c1と一致します。
create table t1(c1 int, c2 int, c3 int, c4 int, pk int primary key);
create index idx on t1(c1, c2, c3);
Q2_2_1: select /*+index(t idx)*/ * from t1 t order by c1, c2;
Q2_2_2: select /*+index(t idx)*/ * from t1 t where c1 = 4 order by c2;
Q2_2_3: select /*+index(t primary)*/ * from t1 t order by pk, c3, c2, c1;
Q2_2_1実行計画:
=======================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
---------------------------------------
|0 |TABLE SCAN|t(idx)|76557 |216994|
=======================================
Outputs & filters:
-------------------------------------
0 - output([t.c1], [t.c2], [t.c3], [t.c4], [t.pk]), filter(nil), rowset=256,
access([t.pk], [t.c1], [t.c2], [t.c3], [t.c4]), partitions(p0)
排除できないソートの場合、オプティマイザーはソート演算子を割り当てる前に、ソートが必要な列に対して一定の最適化を行います。
プレフィックスソート
ソート演算子の割り当てが必要な場合、演算子の入力順序が期待される順序の一部と一致する場合、プレフィックスソートを使用して、入力順序の一部を活用して最適化します。
以下のクエリでは、idx インデックスを使用した後、table scan 演算子の出力順序は (c1, c2, c3) となります。プレフィックスソートを使用すると、各 c1 グループについて、pk のみがソートされます。
Q2_2_4:
select /*+index(t idx)*/ c1 from t1 t order by c1, pk;
=======================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
---------------------------------------
|0 |SORT | |76557 |18874|
|1 | TABLE SCAN|t(idx)|76557 |5319 |
=======================================
Outputs & filters:
-------------------------------------
0 - output([t.c1]), filter(nil), rowset=256,
sort_keys([t.c1, ASC], [t.pk, ASC]), prefix_pos(1)
1 - output([t.pk], [t.c1]), filter(nil), rowset=256,
access([t.pk], [t.c1]), partitions(p0)
ソート列の簡略化
ソートが必要な列に対して一定の簡略化最適化を行い、ソートが必要な列の数を減らします。ソート列の簡略化プロセスでは、主に以下の方法を使用します：
等値条件関係を利用したソート列の簡略化
select c1 from t1 t where c3 = 1 and c2 = c1 order by c3, c2, c1;上記のクエリでは、
c3 = 1およびc2 = c1を使用して簡略化し、最終的には c2 列でソートします。主キー/一意インデックスを利用したソート列の簡略化
select c1 from t1 t order by c1, pk, c3, c2;上記のクエリでは、pk が主キーであるため、最終的には c1, pk の2列でソートします。