動的行パーティション排除を使用するプロダクト ジョインについて
最適化ルーチンでは、行パーティション化されたテーブルともう一方のリレーションとの間をIN条件またはNOT IN条件で結合するためのコストが最小になる方法を判断するときに、動的行パーティション排除(DPE)を使用する包含プロダクト ジョインまたは排他プロダクト ジョインのいずれかをそれぞれ使用することができます。DPEによる包含プロダクト ジョインと排他プロダクト ジョインのどちらを使用するかは、リレーションが結合される条件によって異なります。
| 結合条件の基になる用語 | 最適化ルーチンが選択するプロダクト ジョイン |
|---|---|
| IN | DPEを使用する包含プロダクト ジョイン |
| NOT IN | DPEを使用する排他プロダクト ジョイン |
- 左リレーション行と右リレーション行間の比較操作の回数が削減され、その結果、結合にかかる左右リレーションのI/OおよびCPUコストが削減されます。
- 結合内の1つのリレーションがテーブルであり、もう一方のリレーションがスプールである場合には次の事柄が当てはまります。
スプールの値の数が少なく、その結果、行パーティション化されたテーブルの配置済み行パーティションよりも、配置済みスプール パーティションの方が少ない場合、スプールにないテーブル パーティションは読み取りや結合の必要がないため、結合にかかるI/OとCPUの両方のコストが削減されます。
DPE結合によって得られる性能の向上は、各行パーティションの行数(および必要な行比較の回数)に強く依存しているため、単一列のPARTITION統計が結合の外部(行パーティション化された)テーブルで収集されていない場合は、最適化ルーチンはDPE結合を選択しません。PARTITION統計は最適化ルーチンの性能を向上させ、実行されるべき比較数の見積りを実行可能にします。最適化ルーチンは、サブクエリー スプールのカーディナリティの信頼度の見積もりがない場合、DPE結合を選択しません。
例えば、次のテーブルを作成して、これらのテーブルに対して指定されたSELECTリクエストの実行を依頼するとします。
CREATE SET TABLE MWS.t1, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( a INTEGER, b INTEGER) PRIMARY INDEX (a); CREATE SET TABLE MWS.t2, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( a INTEGER, b INTEGER, c INTEGER, d INTEGER) PRIMARY INDEX (a) PARTITION BY (RANGE_N(b BETWEEN 1 /* Partitioning level one. */ AND 100 EACH 7, NO RANGE OR UNKNOWN), RANGE_N(c BETWEEN 1 /* Partitioning level two. */ AND 100 EACH 10, NO RANGE OR UNKNOWN), RANGE_N(d BETWEEN 1 /* Partitioning level three. */ AND 100 EACH 20, NO RANGE OR UNKNOWN)); SELECT * FROM t2 WHERE (b,c) IN (SELECT a,b FROM t1);
このリクエストを、DPEを使った包含プロダクト ジョインまたは排他プロダクト ジョインの対象にするには、次の統計を収集する必要があります。
COLLECT STATISTICS t1 COLUMN(a); COLLECT STATISTICS t2 COLUMN(PARTITION);
これらの統計を収集するには、次のルールが適用されます。
| 有効にする結合のタイプ | 収集する必要のある統計 |
|---|---|
| DPEを使った積(すべてのタイプ) |
|
| DPEを使用する包含プロダクト ジョインまたは排他プロダクト ジョイン | 行パーティション化されたテーブルのシステム派生PARTITION列セット。 |
包含プロダクト ジョインおよび排他プロダクト ジョインで行なわれる結合タイプは、準結合という名前でも呼ばれています。そのため、DPEによる包含積結合および排他積結合は、DPEによる積準結合という名前で呼ばれることもあります。
最適化ルーチンによってDPEを使用する積準結合が適用されると、その問合わせについてのEXPLAINテキストには、これに対応するAMPステップについてのenhanced by dynamic partition eliminationが示されます。以下の部分的なEXPLAINの出力では、このテキストは太字で強調表示されています。
EXPLAIN SELECT COUNT(*) FROM t55 WHERE (b,c) NOT IN (SELECT 1, 1); 3) We do an INSERT into Spool 5. 4) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of an all-rows scan into Spool 4 (group_amps), which is redistributed by the hash code of (1, 1) to all AMPs. The size of Spool 4 is estimated with high confidence to be 1 row (22 bytes). The estimated time for this step is 0.03 seconds. 5) We do a group-AMP RETRIEVE step from Spool 4 (Last Use) by way of an all-rows scan into Spool 7 (all_amps), which is duplicated on all AMPs. Then we do a SORT to partition by rowkey. The size of Spool 7 is estimated with high confidence to be 2 rows (30 bytes). The estimated time for this step is 0.03 seconds. 6) We do an all-AMPs JOIN step from MWS.t55 by way of an all-rows scan with no residual conditions, which is joined to Spool 7 (Last Use) by way of an all-rows scan. MWS.t55 and Spool 7 are joined using an exclusion product join, with a join condition of ( "(MWS.t55.b = Field_2) AND (MWS.t55.c = Field_3)") enhanced by dynamic row partition elimination. The input table MWS.t55 will not be cached in memory, but it is eligible for synchronized scanning. The result goes into Spool 3 (all_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 3 is estimated with index join confidence to be 2,370,668 rows (35,560,020 bytes). The estimated time for this step is 39.38 seconds. 7) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by way of an all-rows scan. Aggregate Intermediate Results are computed globally, then placed in Spool 8. The size of Spool 8 is estimated with high confidence to be 1 row (23 bytes). The estimated time for this step is 3.06 seconds. 8) We do an all-AMPs RETRIEVE step from Spool 8 (Last Use) by way of an all-rows scan into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with high confidence to be 1 row (25 bytes). The estimated time for this step is 0.02 seconds.
最適化ルーチンは、パーティション列間に等式結合条件がある場合、行パーティション化されたテーブル(準結合の外部テーブル)と他方のリレーションの1つ以上のパーティション化レベルで、DPEを使用する包含プロダクト ジョインまたは排他プロダクト ジョインを適用します。DPEを使用する包含プロダクト ジョインと排他プロダクト ジョインは、行パーティション化されたテーブルが大きく、通常の包含または排他マージ結合の準備でコストのかかるソートを省略できる場合に、このようなクエリーのパフォーマンスを向上します。このような問合わせは、subquery によって選択された結合列の列数が小さい場合は、スプールの値の数が小さくなります。その結果、行パーティション化されたテーブルの配置済みテーブル パーティションよりも配置済みスプール行パーティションの方が少なくなるため、より多くのメリットを得られます。結果として、スプールにない表の行パーティションを読み取ったり結合したりする必要がなくなり、結合にかかるI/OとCPUの両方のコストが削減されます。
例えば、次のテーブル定義とカーディナリティがあるとします。
CREATE SET TABLE t1 ( a INTEGER, b INTEGER, c INTEGER) PRIMARY INDEX ( a ); CREATE SET TABLE t8 ( a INTEGER, b INTEGER, c INTEGER) PRIMARY INDEX (a) PARTITION BY (RANGE_N(c BETWEEN 1 AND 1200 EACH 30, NO RANGE OR UNKNOWN), RANGE_N(b BETWEEN 1 AND 11000 EACH 7, NO RANGE OR UNKNOWN));
| テーブル名 | テーブル カーディナリティ(行) |
|---|---|
| t1 | 1,000 |
| t8 | 9,000,000 |
次のクエリーでは、サブクエリーにより返された行数が少ないため、t8の多くの行パーティションを動的に排除することが可能で、プロダクト ジョインのコスト効率を高めることができます。さらに、結合を行なうためにt8行をソートする必要もありません。
SELECT COUNT(*) FROM t8 WHERE (b,c) IN (SELECT a,b FROM t1 WHERE c = 1); *** Query completed. One row found. One column returned. *** Total elapsed time was 1 second. Count(*) ----------- 65
一方で、最適化ルーチンがDPEを使用する包含プロダクト ジョインの代わりにマージ結合を使用する場合は、t8行をソートする必要があります。これはかなりの処理時間がかかり、クエリーを完了させるのに1秒ではなく57秒かかります。
SELECT COUNT(*) FROM t8 WHERE (b,c) IN (SELECT a,b FROM t1 WHERE c = 1); *** Query completed. One row found. One column returned. *** Total elapsed time was 57 seconds. Count(*) ----------- 65
- 行パーティション化されたテーブルの行パーティションあたりの行数。
- Subquery により射影される列の数
コスト計算では、行パーティション化されたテーブルの行パーティションあたりの行数が大きく変化する場合を考慮に入れ、そのようなテーブルに対してDPEを使用する包含積結合または排他積結合の適用を回避します。
動的行パーティション排除を使用する包含積結合
動的行パーティション排除(DPE)を使用する包含プロダクト ジョインは、IN条件に基づく結合内の外部リレーションが、行パーティション化されたテーブルの場合に使用するように意図されたものであり、DPEのない包含プロダクト ジョインとは異なり、DPE版の結合は、外部テーブルやパーティション化されたテーブルではなく、内部テーブル主導で行なわれます。それに対して、非DPE版の結合は外部テーブル主導で行なわれます。
例えば、これらのテーブルに対する次のテーブル定義と問合わせがあるとします。
CREATE SET TABLE MWS.t1, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL,CHECKSUM = DEFAULT ( a INTEGER, b INTEGER) PRIMARY INDEX (a); CREATE SET TABLE MWS.t2, FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( a INTEGER, b INTEGER) PRIMARY INDEX (a) PARTITION BY (RANGE_N(a BETWEEN 1 /* Partitioning level 1 */ AND 60000 EACH 60000, NO RANGE OR UNKNOWN), RANGE_N(b BETWEEN -3 /* Partitioning level 2 */ AND 31580 EACH 1, NO RANGE, UNKNOWN) ); SELECT * FROM t2 WHERE (b) IN (SELECT a FROM t1);
最適化ルーチンでこのリクエストの実行を依頼するために標準包含マージ結合を選択する場合、システムは次のように結合を実行します。
- t1をソートして、a列の重複した値を削除します。
- ソートされたt1の値をスプールに複製します。
- t2の最初の行を読み取ります。
読み取る行がなくなったら、包含プロダクト ジョインのプロセスは完了です。
- 結合条件t2.b=t1.aに基づいて、一致が見つかるかスプールのすべての行が読み取られるまで一致を検索します。
- ステップ3に進み、t2の次の行を読み取ります。
データベースは、t2のすべての行を順番に読み取り、同様にこれらをスプールと結合します。
DPEを使った包含プロダクト ジョインの場合、システムはt2を順番に読み取らず、スプール ファイルのすべての行をt2の各行と比較することもありません。
結合条件が行パーティション化されたテーブルのすべてのパーティション化レベルまたはそのレベルのサブセットで指定されている場合、システムはこの情報を使用して行パーティション化されたテーブルと同じパーティション化を行なったスプールを作成します。システムは、基本テーブルの行パーティションとスプールの行パーティションの間で同等のパーティション化の範囲のみ結合できます。
DPEを使った包含プロダクト ジョインの設定は、標準包含マージ結合の設定と同じです。ただし、スプールが複製されたときに、基本テーブルに定義されたものと同じパーティションが作成され、パーティション番号でソートされる、という点は異なります。前の例では、パーティション化されたテーブルt2に定義されているパーティション式を使用してスプールが行パーティション化されます。
PARTITION BY (RANGE_N(a BETWEEN -3 AND 31580 EACH 1, NO RANGE, UNKNOWN) );
結合条件t2.b=t1.aリンクは、t1.aとt2.bで分割するため列t1スプールの列で行分割は行なわれます。
システムはDPEを使った包含プロダクト ジョインを、次のように実行します。
- t1をソートして、a列の重複した値を削除します。
- ソートされたt1の値を複製し、t1に定義されたパーティション化の範囲で、その値をスプール ファイル内でパーティション化します。
- スプールの最初の行パーティションにある、最初の行を読み取ります。
- 現在のスプール パーティションが結合される行パーティション化されたテーブルに、パーティション リストを動的に作成します。
- 行パーティション化されたテーブルの最初のDPE行パーティションにある、最初の行を読み取ります。
- 一致が見つかるか、スプール行パーティション内の行がなくなるまで、読み取った行を現在のスプール行パーティションにあるすべての行と比較します。
一致の有無 結果 見つかった場合 行パーティション化されたテーブルの行を返します。 見つからない場合 ステップ7に進みます。 - 関与するDPEパーティション内の行パーティション化されたテーブルの次の行を読み取ります。
条件 進むステージ 読み取る行がない 6 読み取る行がある 4 - 現在のスプール行パーティションよりも大きいパーティション番号を持つスプールの、最初の行を読み込みます。
条件 結果 そのような行がない 結合が完了しました。 そのような行がある ステップ2に進みます。
動的行パーティション排除を使用する排他積結合
DPEを使った排他積結合のアルゴリズムは、DPEを使った包含積結合のアルゴリズムに似ていますが、制限と、いくつかの違いがあります。
制限は、DPEを使用する排他積結合は各パーティション レベルのパーティション式がRANGE_N関数のみで構成されており、テスト式が単一列である行パーティション化されたテーブルでのみ使用できることです。このような制限を課す理由は、任意の組み合わせパーティション レベルにnullのパーティション列の値を持つ行はそのレベルにおけるすべてのパーティションと結合する必要があるためです。このことが、DPEから得られるパフォーマンス向上の大部分を打ち消してしまう可能性があるため、DPEを使用する排他積結合では、パーティション列の値がnullのすべてのスプール行をエラー パーティションに配置して、行パーティション化されたテーブルの余分な行を読み取らないようにします。nullが含まれている列における式は非nullとなる可能性があるため、単一列のテスト式を持つRANGE_N関数を使用する以外にnullを持つすべての行を1つのパーティションにグループ化する簡単な方法はありません。
nullのパーティション列を持つ行がエラー パーティションに配置されたことを確認するため、システムはRANGE_N式を変更して、UNKNOWNおよびNO RANGEパーティションがNO RANGE OR UNKNOWNとして1つにグループ化されるときに、これらの両方のパーティションをその定義から削除します。その後、結合アルゴリズムは、エラー パーティションにある行と行パーティション化されたテーブルのすべての行を結合します。
すべての結合条件は、最適化ルーチンがDPEを使った排他積結合を選択できるように、パーティション列を参照する必要があります。追加の非パーティション列が結合条件の構成要素である場合、最適化ルーチンはDPEを使った排他積結合アルゴリズムを選択しません。
Teradata DatabaseはDPEを使った排他プロダクト ジョインを、次のように実行します。
- 行パーティション化されたテーブル内の、排除されていないすべてのパーティションを含む、行パーティションのリストを作成します。
これは未処理の行パーティション リストです。このリストは、すべてのDPEパーティションが結合された後、エラー パーティションと結合する必要があります。
- スプールの最初の行パーティションにある、最初の行を読み取ります。
- 現在のスプール行パーティションを結合する、t2の行パーティションのリストを動的に作成します。
未処理の行パーティション リストからこの行パーティション セットを削除します。
- 行パーティション化されたテーブルの最初のDPEパーティションにある、最初の行を読み取ります。
- 読み取った行と、現在のスプール行パーティションにあるすべての行、およびエラー パーティションにあるすべての行を比較します。
条件 結果 一致がある ステップ6に進みます。 一致がない 行パーティション化されたテーブルの行を返します。 - 関与するDPEパーティション内の行パーティション化されたテーブルの次の行を読み取ります。
条件 進むステージ 読み取る行がない 7 読み取る行がある 5 - 現在のスプール パーティションよりも大きいパーティション番号を持つスプールの、最初の行を読み込みます。
条件 進むステージ そのような行がない 8 そのような行がある 5 - 未処理の行パーティション リストによって排除されていない行パーティション化されたテーブルにある、最初の行を読み取ります。
-
条件 結果 結合条件のすべての列がnullである 行を返しません。 ステップ12に進みます。
結合条件のすべてではなく、一部の列がnullである 行とスプールの行を結合します。 ステップ11に進みます。
結合条件のすべての列がnullでない ステップ10に進みます。 - この行を、スプールのエラー パーティションのすべての行に結合します。
- 一致が見つからない場合は、行を返します。
- 未処理のパーティション リストによって排除されていないパーティション化されたテーブルにある、次の行を読み取ります。そのような行がある場合、ステップ9に進みます。