クエリに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|
=======================================
出力とフィルター:
-------------------------------------
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列に従ってソートします。