クエリに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列でソートします。