17.00 - 17.05 - Rowkeyベースのマージ結合 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

単一レベルおよびマルチレベル パーティションの行キー ベース マージ結合では、すべてのプライマリ インデックス列と2つのリレーションのパーティション列に等式条件が必要です。

Rowkeyベースのマージ結合の対象となるには、両方のリレーションに同じパーティション化が指定される必要があります。パーティション化が異なる場合、2つのリレーションのパーティション化が同じになるように、リレーションの1つをスプールしてパーティション化する必要があります。

Vantageでは、8バイトのパーティション化を含む行パーティション化されたPIテーブルのパーティション再マッピングを使用した、完全外部行キー ベースのマージ結合はサポートされていません。

文字PPIに適用されるその他の制限については、「文字パーティション化されたリレーションを含むRowkeyベースのマージ結合に必要な条件」も参照してください。

Rowkeyベースのマージ結合にサポートされている結合方式

Rowkeyベースのマージ結合では、次の結合タイプがサポートされています。高速パスとは、左と右の一致するパーティション内の行ハッシュ照合スキャンを示し、低速パスとは、1つのリレーションにおけるインデックス アクセスに続いて、もう一方のテーブルで、対応するパーティション内に一致する行ハッシュがある行を検索することを示します。
  • 高速パス内部、左、および完全外部マージ結合。
  • 低速パス内部および左外部マージ結合。
  • 高速パス内部および外部包含/排他マージ結合。
  • 低速パス内部および外部包含/排他マージ結合。
  • 高速パス相関包含/排他マージ結合。
  • 低速パス相関包含/排他マージ結合。

    このタイプの結合は、テーブルとスプール間でのみ行なわれ、2つのテーブルの間では直接行なわれないことに注意してください。

Rowkeyベースのマージ結合では、右外部マージ結合はサポートされていません。最適化ルーチンは、結合タイプが左外部マージ結合となるようにリレーションを切り替えることができることに注意してください。

内部パーティション マッピングが両方のリレーションで同じでない場合、Rowkeyベースのマージ結合は、同期スキャンに適格でありません。内部パーティション マッピングが両方のリレーションで同じである場合、Rowkeyベースのマージ結合は、次の結合タイプについてのみ、同期スキャンに適格になります。
  • 低速パス左外部マージ結合。
  • 低速パス内部マージ結合。

Rowkeyベースのマージ結合は、プライマリ インデックス付きのテーブルと、結合条件にならないプライマリ インデックスを持つテーブルとの間での結合と見なすことができます。そのような結合を行なうために、システムはスプールに他のテーブルのRowkeyを作成します。このRowkeyは結合相手の行パーティション化されたPIテーブルのパーティション式に基づいて作成されます。

サポートされていないRowkeyベースのマージ結合のパーティション排除機能

Vantageでは、8バイト パーティション化のパーティション再マッピングを使用した完全外部行キー ベースのマージ結合について、動的行パーティション排除をサポートしていません。

行パーティションPIとスプールの結合の作成

この例では、データベースはディメンション テーブルから行を選択し、中間結果セットを結合して、行パーティション化されたPIテーブルの行キーと一致する行キーでソートされたスプールを形成できます。次にシステムは、PPIテーブルからスプールへRowkeyベースのマージ結合を実行できます。

こうすると、スプールを再分散してそれを結合列でソートさせる必要がなくなり、結合されているその他のテーブルおよびスプールを結合列でソートさせ、2つのスプールを行ハッシュ マージ結合または他の結合計画を使って結合させることができます。最終的に、最適化ルーチンは、利用可能なオプションのうち、最もコストがかからないと見積もられる計画を選択します。

marketsテーブルについての定義DDLリクエスト テキスト。

CREATE TABLE markets (
  productid       INTEGER NOT NULL,
  region          BYTEINT NOT NULL,
  activity_date   DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  revenue_code    BYTEINT NOT NULL,
  business_sector BYTEINT NOT NULL,
  note            VARCHAR(256))
PRIMARY INDEX (productid, region)
PARTITION BY (
RANGE_N(region          BETWEEN 1
                        AND     9
                        EACH    3),
RANGE_N(business_sector BETWEEN 0
                        AND    49
                        EACH   10),
RANGE_N(revenue_code    BETWEEN 1
                        AND    34
                        EACH    2),
RANGE_N(activity_date   BETWEEN DATE '1986-01-01'
                        AND     DATE '2007-05-31'
                        EACH INTERVAL '1' MONTH));

productsテーブルの定義DDLリクエスト テキストは次のとおりです。

CREATE TABLE products (
  productid    INTEGER NOT NULL,
  product_name CHARACTER(30),
  description  VARCHAR(256))
PRIMARY INDEX (productid);

regionsテーブルの定義DDLリクエスト テキストは次のとおりです。

CREATE TABLE regions (
  region      INTEGER NOT NULL,
  region_name CHARACTER(30),
  description VARCHAR(256))
PRIMARY INDEX (region_name);

business_sectorsテーブルの定義DDL文テキストは次のとおりです。

CREATE TABLE business_sectors (
  productid            INTEGER NOT NULL,
  business_sector_name CHARACTER(30),
  description          VARCHAR(256))
PRIMARY INDEX (business_sector_name);

revenue_codesテーブルの定義DDL文テキストは次のとおりです。

CREATE TABLE revenue_codes (
  revenue_code      INTEGER NOT NULL,
  revenue_code_name CHARACTER(30),
  description       VARCHAR(256))
PRIMARY INDEX (revenue_code_name);

activity_calendarテーブルの定義DDL文テキストは次のとおりです。

CREATE TABLE activity_calendar (
  quarter       CHARACTER(6),
  activity_date DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  description   VARCHAR(256))
PRIMARY INDEX (quarter);

これらのテーブルすべてを結合する問合わせの例を次に示します。

SELECT p.product_name, r.region_name, b.business_sector_name,
       rc.revenue_code_name, a.quarter, m.activity_date, m.note
FROM   markets AS m, products AS p, regions AS r,
       business_sectors AS b, revenue_codes AS rc,
       activity_calendar AS a
WHERE  p.productid BETWEEN 4000
                   AND     4999
  AND r.region_name = 'West'
  AND b.business_sector_name IN ('Cosmetics','Snack Food','Hats')
  AND rc.revenue_code_name IN ('Web', 'Catalog')
  AND a.quarter IN ('2006Q1', '2005Q1')
  AND m.productid       = p.productid
  AND m.region          = r.region
  AND m.business_sector = b.business_sector
  AND m.revenue_code    = rc.revenue_code
  AND m.activity_code   = a.activity_code;

文字パーティション化されたリレーションを含むRowkeyベースのマージ結合に必要な条件

文字パーティション化されたリレーションに対する行キー ベースの直接のマージ結合には、次のルールが適用されます。それぞれのケースで、Rowkeyベースのマージ結合方法の対象となるには、結合に含まれる両方のリレーションの属性が同じである必要があります。
直接結合とは、対象となるリレーションが結合の準備中にスプールされない2項結合演算です。例えば、直接マージ結合は、対象となるリレーションがマージ結合の準備中にスプールされない結合です。同じように、直接積結合は、対象となるリレーションが積結合の準備中にスプールされない積結合です。
  • リレーションの照合が同じである必要があります。
  • リレーションの文字パーティション列のサーバー文字セットが同じである必要があります。
  • 列の長さおよび大文字小文字の区別の属性を含めて、リレーションの文字パーティション列のデータ型が同じである必要があります。

結合される両方のリレーションがこれらすべての条件を満たしていない場合、結合の他方のパーティション化されたリレーションのパーティション化と一致させるために、どちらかをスプールしてから再パーティション化する必要があります。

最適化ルーチンが、直接の行キー ベースのマージ結合を選択するかどうか、または文字パーティション化されたリレーションのパーティション化と一致させるために1つのリレーションがスプールされてからパーティション化されるかどうかに関係なく、最適化ルーチンで行キー ベースのマージ結合を指定するには、次のルールが満たされている必要があります。
  • この結合の見積もりコストが、他のすべての結合タイプの見積もりコストより小さくなっている必要があります。
  • 各パーティション レベルで文字パーティション列を1つのみにする必要があります。
  • セッション照合またはテーブル照合がMULTINATIONALまたはCHARSET_COLLのどちらかであり、いずれかのパーティション化レベルでパーティション式内の非定数式に関与する比較関数または文字列関数が大文字と小文字を区別しない場合、セッション照合指定とテーブル照合指定が一致している必要があります。
    次の関数および属性は大文字小文字を区別しません。
    • LOWER関数
    • SOUNDEX関数
    • UPPER関数
    • UPPERCASE属性
    次の関数は大文字小文字を区別します。
    • CHAR2HEXINT
    • TRANSLATE
    • TRANSLATE_CHK
    • TRIM
    • VARGRAPHIC

    連結演算子(||)が存在する場合、大文字小文字の区別ありと区別なしの両方のプロパティを持つ式としてマークされます。

    次の関数は、比較演算子、関数入力引数、および文字パーティションが作成または変更されたときに有効だったセッション モードのデフォルトの大文字小文字の区別と同じルールに従います。システムはこれらのすべてを調べて大文字小文字の区別を決定します。
    • INDEX
    • MINDEX
    • POSITION

    SUBSTRING関数を指定しても、大文字小文字の区別には影響しません。

  • RowKeyベースのマージ結合のためのパーティション化レベルを修飾するWHERE句の述部で大文字と小文字が区別されない場合、そのレベルのパーティション式に含まれる非定数式のすべての比較関数および文字列関数でも大文字と小文字が区別されないようになります。

    条件内の非定数式を含むいずれかの比較関数または文字列関数の大文字小文字が区別されない場合、WHERE句の述部では大文字小文字が区別されないと見なされます。

  • 等価結合条件内の文字パーティション列の文字セットが、結合条件内の他のリレーション内でその列に関連する式の文字セットと異なる場合は、Unicodeサーバー文字セットを使用してパーティション列のデータを保存する必要があります。

    これは、システムが、比較を行なう前に両方の式をUnicodeに暗黙的に変換することによって、非定数文字式間の比較を処理するためです。

文字パーティション列のすべての等価結合条件で大文字小文字が区別される場合、最適化ルーチンでRowKeyベースのマージ結合を指定するために、現在のセッションの照合指定がそのテーブルの照合指定と一致する必要はありません。

ANSIモードのセッションで文字パーティション化されたテーブルを作成する場合は、比較内の定数式がNOT CASESPECIFICとして明示的にCASTされていない限り、システムは、CASE_Nベースのパーティション式または一般化されたパーティション式で比較されるパーティション列を暗黙的にCASESPECIFICにキャストします。

これは、そのようなパーティション プライマリ インデックスを含むRowKeyベースのマージ結合を使用することで評価できる結合条件のタイプに影響します(Rowkeyベースのマージ結合が使用できない場合の例については、このトピックで後述します)。

この例では、2つの文字パーティション化されたテーブルがプライマリ インデックスおよびパーティション式で結合されます。次のテーブル定義は、いずれも同じセッション照合で作成されたと仮定します。

CREATE SET TABLE test1, NO FALLBACK, NO BEFORE JOURNAL,
                        NO AFTER JOURNAL,CHECKSUM = DEFAULT (
  i INTEGER,
  j CHARACTER(4) CHARACTER SET UNICODE CASESPECIFIC)
PRIMARY INDEX (i)
PARTITION BY RANGE_N(j BETWEEN 'AAAA','ZZZZ','aaaa','yyyy'
                       AND     'zzzz');
CREATE SET TABLE test11, NO FALLBACK, NO BEFORE JOURNAL,
                         NO AFTER JOURNAL, CHECKSUM = DEFAULT (
  i INTEGER,
  j CHARACTER(4) CHARACTER SET UNICODE CASESPECIFIC)
PRIMARY INDEX (i)
PARTITION BY RANGE_N(j BETWEEN 'AAAA','ZZZZ','aaaa','yyyy'
                       AND     'zzzz');

これらのテーブルを結合する次のSELECTリクエストを実行依頼するとします。

SELECT *
FROM test1 INNER JOIN test11
WHERE test1.i = test11.i
AND   test1.j = test11.j;
結合されるリレーションが次の直接の行キー ベースのマージ結合の要件を満たしているので、データベースは、それらのリレーションの両方をスプールに再分散してからプライマリ インデックスのハッシュによってスプールを並べ替えて、行ハッシュ マージ結合を実行する代わりに、直接の行キー ベースのマージ結合を使用してこれらのテーブルを結合することができます。
  • リレーションの文字パーティション列で同じセッション照合を使用している。
  • リレーションの文字パーティション列のサーバー文字セットが同じである。

    この場合、両方のリレーションで文字パーティション列jに一致するサーバー文字セットはUnicodeです。

  • リレーションの文字パーティション列jのデータ型、列の長さ、大文字小文字の区別の属性が同じである。

    この場合、両方のリレーションの文字パーティション列jのデータ型(CHARACTER)、長さ(4文字)、および大文字小文字の区別(CASESPECIFIC)が同じになります。

この例では、2つのテーブル(そのうち1つだけが文字パーティション化されている)がプライマリ インデックスで結合されます。次のテーブル定義は、いずれも同じセッション照合で作成されたと仮定します。

CREATE TABLE markets (
  product_name    VARCHAR(50) CHARACTER SET UNICODE CASESPECIFIC,
  region          BYTEINT NOT NULL,
  activity_date   DATE FORMAT 'yyyy-mm-dd' NOT NULL,
  revenue_code    BYTEINT NOT NULL,
  business_sector BYTEINT NOT NULL,
  note            VARCHAR(256))
PRIMARY INDEX (productname)
PARTITION BY (RANGE_N(productname BETWEEN 'A','B','C','D','E','F',
                                          'G','H','I','J','K','L',
                                          'M','N','O','P','Q','R',
                                          'S','T','U','V','W','X',
                                          'Y','Z'
                                  AND
            'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ',
                 NO RANGE,
                 UNKNOWN));
CREATE TABLE products (
  product_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,
  description VARCHAR(256))
PRIMARY INDEX (productname);

marketsは文字パーティション化されたリレーションであり、productsは文字パーティション化されていません。

これらのテーブルを結合する次のSELECTリクエストを実行依頼するとします。

SELECT *
FROM products INNER JOIN markets
WHERE products.product_name = markets.product_name;
次の点で、結合されるリレーションが直接の行キー ベースのマージ結合の要件を満たしていないので、データベースは、直接のマージ結合を使用してこれらのテーブルを結合できません。代わりにproductsテーブルをスプールに再分散し、プライマリ インデックスのハッシュによってスプールを並べ替えてから、行キー ベースのマージ結合を実行する必要があります。
  • 結合内の1つのリレーションmarketsのみがパーティション化されています。
  • これらのリレーションは、結合列product_nameに異なるケース指定属性を持ちます。

    この場合、marketsリレーションの列product_nameの大文字小文字の区別はCASESPECIFICですが、productsリレーションの文字パーティション列product_nameの大文字小文字の区別はNOT CASESPECIFICになっています。

システムは、行キー ベースのマージ結合を使用する代わりに、productsテーブルから行を選択してスプールを形成し、次に文字パーティション化されたリレーションmarketsと同じ行キーによってソートしてから、文字パーティション化されたリレーションmarketsからスプールされたリレーションproductsへの行キー ベースのマージ結合を実行します。

パーティション化されたテーブル内のすべての比較はケース指定なので、セッション照合は文字パーティション化されたテーブル照合と一致する必要はありませんが、テーブルの作成後にテーブル照合の定義を変更すると(これはMULTINATIONAL照合およびCHARSET_COLL照合にのみ適用されます)、最適化ルーチンはRowKeyベースのマージ結合を指定しなくなります。

次のCREATE TABLEリクエストがANSIセッション モードで実行依頼されます。

CREATE MULTISET TABLE tt1(
  a INTEGER,
  b CHARACTER(30) NOT CASESPECIFIC)
PRIMARY INDEX (a)
PARTITION BY CASE_N(b BETWEEN 'A' AND 'Z',
                    b BETWEEN 'a' AND 'z',
             NO CASE OR UNKNOWN);
CREATE MULTISET TABLE tt2(
  a INTEGER,
  b CHARACTER(30) NOT CASESPECIFIC)
PRIMARY INDEX (a);

これらのテーブルを結合する次のSELECTリクエストがTeradataセッション モードで実行依頼されます。

SELECT *
FROM tt1 INNER JOIN tt2
WHERE tt1.a = tt2.a
AND   tt1.b = tt2.b;

パーティション式内の比較では大文字小文字が区別されますが、SELECTリクエスト内の結合条件では大文字小文字が区別されないので、システムはこのリクエストに対してRowKeyマージ結合を使用できません。

次のCREATE TABLEリクエストがTeradataセッション モードで実行依頼されます。

CREATE MULTISET TABLE tt3(
  a INTEGER,
  b CHARACTER(30) NOT CASESPECIFIC)
PRIMARY INDEX (a)
PARTITION BY CASE_N(TRIM(LEADING 'a' FROM b) BETWEEN 'A'
                                             AND     'Z',
                    TRIM(LEADING 'a' FROM b) BETWEEN 'a'
                                             AND     'z',
                    NO CASE OR UNKNOWN);
CREATE MULTISET TABLE tt2(
  a INTEGER,
  b CHARACTER(30) NOT CASESPECIFIC)
PRIMARY INDEX (a);

これらのテーブルを結合する次のSELECTリクエストがTeradataセッション モードで実行依頼されます。

SELECT *
FROM tt3 INNER JOIN tt2
WHERE tt3.a = tt2.a
AND   tt3.b = tt2.b;

TRIM関数のためにtt3のパーティション式内の比較では大文字小文字が区別されますが、SELECTリクエスト内の結合条件では大文字小文字が区別されないので、この場合、最適化ルーチンはRowKeyマージ結合を選択できません。