例 - Advanced SQL Engine - Teradata Database

例1: 行を割り当てることが可能な4つのパーティションの指定

以下は、RANGE_Nとtotalorders列の値を使って行の割り当て先のパーティションを定義する例です。

   CREATE TABLE orders 
    (storeid INTEGER NOT NULL
    ,productid INTEGER NOT NULL
    ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL
    ,totalorders INTEGER)
    PRIMARY INDEX (storeid, productid) 
     PARTITION BY RANGE_N(totalorders BETWEEN *, 100, 1000 AND *,
                         UNKNOWN);

この例では、RANGE_Nはtotalorders列の値に基づいて、行の割り当て先となる4つのパーティションを指定します。

例2: パーティション式のリストでのRANGE_Nの使用

以下の例では、「例」を変更して、マルチレベルPPIを定義するパーティション化式のリストでRANGE_Nを使用しています。

   CREATE TABLE orders 
    (storeid INTEGER NOT NULL
    ,productid INTEGER NOT NULL
    ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL
    ,totalorders INTEGER NOT NULL)
    PRIMARY INDEX (storeid, productid) 
     PARTITION BY (RANGE_N(totalorders BETWEEN *, 100, 1000 AND *)
                  ,RANGE_N(orderdate BETWEEN *, '2005-12-31' AND *) );

この例では、行が割り当てられる6つのパーティションが定義されています。最初のRANGE_N式は、totalorders列の値に基づいて3つのパーティションを定義しています。2番目のRANGE_N式は、orderdate列の値に基づいて3つのパーティションをそれぞれ2つのパーティションに再分割しています。

例3: 1つのパーティションを指定するパーティション プライマリ インデックスの定義

以下に、NULLを含めたtotalorders列の任意の値について、行の割り当て先の1つのパーティションを指定するパーティション プライマリ インデックスを定義する例を示します。

   CREATE TABLE orders 
    (storeid INTEGER NOT NULL
    ,productid INTEGER NOT NULL
    ,orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL
    ,totalorders INTEGER)
    PRIMARY INDEX (storeid, productid) 
     PARTITION BY RANGE_N(totalorders BETWEEN * AND *);

例4: RANGE_N式を使ってテーブルがパーティション化されている場合の各パーティションの行数をカウントする

以下の例は、RANGE_N式を使ってテーブルがパーティション化された場合の各パーティション内の行カウントを示します。

   CREATE TABLE orders 
    (orderkey INTEGER NOT NULL
    ,custkey INTEGER
    ,orderdate DATE FORMAT 'yyyy-mm-dd')
    PRIMARY INDEX (orderkey);
   
   INSERT INTO orders (1,  100, '1998-01-01');
   INSERT INTO orders (2,  100, '1998-04-01');
   INSERT INTO orders (3,  109, '1998-04-01');
   INSERT INTO orders (4,  101, '1998-04-10');
   INSERT INTO orders (5,  100, '1998-07-01');
   INSERT INTO orders (6,  109, '1998-07-10');
   INSERT INTO orders (7,  101, '1998-08-01');
   INSERT INTO orders (8,  101, '1998-12-01');
   INSERT INTO orders (9,  111, '1999-01-01');
   INSERT INTO orders (10, 111, NULL);

以下のSELECT文中のRANGE_N式は、EACH句を使って一連の12個の範囲を定義しています。ただし最初の範囲は'1998-01-01'から開始し、その後に続く範囲は、1ヶ月ずつの間隔で順番に増加し、開始境界を含みます。

   SELECT COUNT(*), 
          RANGE_N(orderdate 
                  BETWEEN DATE '1998-01-01' AND DATE '1998-12-31'
                  EACH INTERVAL '1' MONTH
          ) AS Partition_Number
   FROM orders 
   GROUP BY Partition_Number 
   ORDER BY Partition_Number;

結果は次のようになります。

      Count(*) Partition_Number
   ----------- ----------------
             2                ?
             1                1
             3                4
             2                7
             1                8
             1               12

例5: RANGE_N式を使ったテーブルのパーティション化

次の例では、パーティション化を指定してテーブルを作成します。このパーティション化の定義には、END境界関数が関与するRANGE_N式を使用します。テーブルには、それぞれのパーティションが各年の販売履歴を表わす10個のパーティションが作成されます。

   CREATE TABLE SalesHistory
     (product_code CHAR (8),
      quantity_sold INTEGER,
      transaction_period PERIOD (DATE))
     PRIMARY INDEX (product_code)
     PARTITION BY
        RANGE_N (END (transaction_period) BETWEEN date'2006-01-01'
                 AND date '2015-12-31' EACH INTERVAL'1' YEAR);

次のSELECT文は、2010年よりも前の販売履歴の5つのパーティションをスキャンします。

   SELECT * 
   FROM SalesHistory 
   WHERE transaction_period < period (date'2010-01-01');

例6: Start_expressionとCURRENT_DATE

CURRENT_DATEまたはCURRENT_TIMESTAMPが、RANGE_N内の最初の範囲であるstart_expressionに指定されている場合、CURRENT_DATEまたはCURRENT_TIMESTAMPで改めて解決されたstart_expressionがパーティション境界と一致すると、一致したパーティションより前のすべてのパーティションが削除されます。それ以外の場合は、新しいパーティション式でテーブル全体が再パーティション化されます。

次のCREATE TABLE文が、2006年4月1日に実行されたとします。

   CREATE TABLE ppi (i INT, j DATE)
   PRIMARY INDEX (i) 
   PARTITION BY 
      RANGE_N (j BETWEEN CURRENT_DATE AND
               CURRENT_DATE + INTERVAL '1' YEAR - INTERVAL '1' DAY
               EACH INTERVAL '1' MONTH);

最後に解決された日付は、2006年4月1日になります。ALTER TABLE TO CURRENT文を2006年6月1日に実行すると、start_expressionは、改めてCURRENT_DATE('2006-06-01')に解決されます。これは、3番目のパーティション境界と一致します。そのため、パーティション1と2は削除され、最後に調整された日付は、改めて解決されたCURRENT_DATEに設定されます。

ただし、ALTER TABLE TO CURRENT文を2006年6月1日ではなく、2006年6月10日に実行したとすると、start_expressionは改めてCURRENT_DATE('2006-06-10')に解決され、パーティション境界と一致しなくなります。そのため、すべての行がスキャンされ、それらの行は新しいパーティション式に基づいて再パーティション化されます。この文の後のパーティション境界は、それまでの月の最初の日ではなく、月の10日目に揃えられます。

例7: 履歴を記録するためのテーブルのパーティション化

次のテーブル定義は、2007年(この時点で、現在の年)に作成されています。テーブルは、5年間分の注文履歴と、現在および将来のそれぞれ1年分の注文を記録するためにパーティション化されます。

   CREATE TABLE Orders
     (o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
      o_orderpriority CHAR(21),
      o_comment VARCHAR(79))
   PRIMARY INDEX (o_orderkey)
   PARTITION BY RANGE_N(
      o_orderdate BETWEEN DATE '2002-01-01' AND DATE '2008-12-31' 
      EACH INTERVAL '1' MONTH)
   UNIQUE INDEX (o_orderkey);

2008年に、以前と同じように、5年間分の履歴と、現在および将来のそれぞれ1年間分の記録を保守するためにテーブルを変更しようと考えたときには、次の文を2008年に実行します。

   ALTER TABLE Orders MODIFY PRIMARY INDEX (o_orderkey)
      DROP RANGE WHERE PARTITION BETWEEN 1 AND 12
      ADD RANGE BETWEEN DATE '2009-01-01' AND DATE '2009-12-31' 
         EACH INTERVAL '1' MONTH
   WITH DELETE;

この場合、新しい日付を計算して、その日付を明示的にADD RANGE句に指定する必要があります。これでは、この文を実行する年ごとに手動の介入が必要になってしまいます。

それとは別に、CURRENT_DATEを使用して、次に示すようにテーブルを定義できます。これにより、パーティションの変更が容易になります。

 CREATE TABLE Orders
     (o_orderkey INTEGER NOT NULL,
     o_custkey INTEGER,
     o_orderstatus CHAR(1) CASESPECIFIC,
     o_totalprice DECIMAL(13,2) NOT NULL,
     o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
     o_orderpriority CHAR(21),
     o_comment VARCHAR(79))
   PRIMARY INDEX (o_orderkey)
   PARTITION BY RANGE_N(o_orderdate BETWEEN
      CAST(((EXTRACT(YEAR FROM CURRENT_DATE)-5-1900)*10000+0101) AS DATE)
      AND
      CAST(((EXTRACT(YEAR FROM CURRENT_DATE)+1-1900)*10000+1231) AS DATE)
      EACH INTERVAL '1' MONTH)
   UNIQUE INDEX (o_orderkey);

次のALTER TABLE文を毎年実行するように、スケジュールすることができます。この文は、パーティションの削除と追加を効率的に実行して、パーティションのウィンドウを前方に進めます。

ALTER TABLE Orders TO CURRENT WITH DELETE;

CURRENT_DATEを使用することで、新しい日付に基づいてデータを再パーティション化しようとしたときに、ALTER TABLE文を毎回修正する必要がなくなります。

どちらの場合も、年境界でパーティション化が開始されます。最初の例では、これをALTER TABLE文が変更することはありません。そのため、パーティション化は年境界で開始され続けます。ただし、別の境界で開始するようにパーティション化を変更する、ALTER TABLE文の指定は可能です。例えば、ALTER TABLE文に希望の日付を指定すると、ある年の特定の月に開始するようにロール フォワードできます。

CURRENT_DATEを使用する2番目の例では、年境界で開始するロール フォワードのみ可能です。ただし、この例を変更すると、月初めに開始するロール フォワードにパーティション化を使用できるようになります。この例では、CREATE TABLEの日付を起点として、Ordersテーブルに過去71カ月分の履歴と、今月および将来の12カ月分(合計84カ月)の記録を格納することを想定しています。

   CREATE TABLE Orders
     (o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
      o_orderpriority CHAR(21),
      o_comment VARCHAR(79))
   PRIMARY INDEX (o_orderkey)
   PARTITION BY RANGE_N(o_orderdate BETWEEN 
      CAST(((EXTRACT(YEAR FROM CURRENT_DATE)-1900)*10000 +
             EXTRACT(MONTH FROM CURRENT_DATE)*100 + 01) AS DATE) -
          INTERVAL '71' MONTH
      AND
      CAST(((EXTRACT(YEAR FROM CURRENT_DATE)+1-1900)*10000 +
             EXTRACT(MONTH FROM CURRENT_DATE)*100 + 01) AS DATE)+
          INTERVAL '13' MONTH - INTERVAL '1' DAY
      EACH INTERVAL '1' MONTH)
   UNIQUE INDEX (o_orderkey);

次のALTER TABLE文を、毎月またはそれより少ない(ただし、将来の月を越えない)頻度で実行するようにスケジュールできます。この文は、Ordersテーブルに過去71カ月の履歴と、現在の月、将来の12カ月の記録が含まれ続けるように、パーティションの削除と追加を実行してパーティションのウィンドウを先に進めます。

   ALTER TABLE Orders TO CURRENT WITH DELETE;

次に示す、より単純なパーティション化を定義できますが、これは最適化されないことがあり、ALTER TABLE TO CURRENT文を実行すると、調整のためにテーブル全体がスキャンされる可能性があります。この例では、CREATE TABLEの日付を起点として、Ordersテーブルには約2,191日分の履歴と、今日と将来の356日分(合計、約7年)の記録を格納することを想定しています。

   CREATE TABLE Orders
     (o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL,
      o_orderpriority CHAR(21),
      o_comment VARCHAR(79))
   PRIMARY INDEX (o_orderkey)
   PARTITION BY RANGE_N(o_orderdate BETWEEN
      CURRENT_DATE - INTERVAL '6' YEAR
         AND
      CURRENT_DATE + INTERVAL '1' YEAR
      EACH INTERVAL '1' MONTH)
   UNIQUE INDEX (o_orderkey);

次のALTER TABLE文を、毎日またはそれより少ない(ただし、将来の日を越えない)頻度で実行するようにスケジュールできます。この文は、CURRENT_DATEと、最後にCREATE TABLEまたはALTER TABLE TO CURRENT文が実行された日付が同じ場合にのみ、パーティションの削除と追加を実行してパーティションのウィンドウを前進させます。それ以外の場合は、行を調整するために、テーブル全体がスキャンされます。

   ALTER TABLE Orders TO CURRENT WITH DELETE;

これは、最後にCREATE TABLEまたはALTER TABLE TO CURRENT文が実行された日とは違う日にALTER TABLE文が実行された場合に、効率が非常に悪化することがあります。パフォーマンスは、最後に解決された日付と改めて解決された日付の間の日数が増えるほど悪化します。その理由は、移動しなければならない行の数が増えるためです。

例えば、最後に解決された日付が2008年1月1日で、その後の2008年2月2日にALTER TABLE TO CURRENT文が実行されたとすると、テーブル内のすべての行が新しいパーティションに移動されることになります。

例8: 範囲の定義

次の例では、5つの範囲を定義しています。セッションの照合はASCIIです。

   RANGE_N(animal BETWEEN *, 'ape', 'bird', 'bull' AND 'cow', 
           'dog' AND *, NO RANGE, UNKNOWN)

説明:

animalの値が定義した範囲のいずれかと一致すると、RANGE_Nは一致した範囲の番号を返します。

animalの値が、'cow'より大きく'dog'よりも小さい場合には、どの範囲にも一致しませんが、NO RANGEが指定されているため、RANGE_Nは6を返します。

animalの値がNULLの場合には、UNKNOWNが指定されているため、RANGE_Nは7を返します。

例9: 5つの範囲の定義

次の例では、5つの範囲を定義しています。セッションの照合はASCIIです。

   RANGE_N(animal BETWEEN *, 'ape', 'bird', 'bull' AND 'cow', 
           'dog' AND *, UNKNOWN)

説明:

animalの値が定義した範囲のいずれかと一致すると、RANGE_Nは一致した範囲の番号を返します。

animalの値が、'cow'より大きく'dog'よりも小さい場合には、どの範囲にも一致しません。この場合、NO RANGEが指定されていないため、RANGE_NはNULLを返します。

animalの値がNULLの場合には、UNKNOWNが指定されているため、RANGE_Nは6を返します。

例10: 2つの範囲の定義

この例では、CREATE TABLE文を実行しているときのセッションの照合はASCIIです。また、埋め込み文字は<スペース>です。この例では、2つの範囲を定義しています(番号1と2)。

• 'a         '(aの後ろに9個のスペース)以上'b         '未満の値はパーティション1にマップされます。
• 'b         '以上'c         '未満の値は、パーティション2にマップされます。

     CREATE SET TABLE t2
        (a VARCHAR(10) CHARACTER SET UNICODE NOT CASESPECIFIC,
         b INTEGER)
     PRIMARY INDEX (a)
     PARTITION BY RANGE_N(a BETWEEN 'a','b' AND 'c');

以下のINSERT文では、'b'と'1'の間に1つの<tab>文字が入る文字列を挿入します。

   INSERT t2 ('b	1', 1);

以下のINSERT文では、'b'と'1'の間に1つの<space>文字が入る文字列を挿入します。

   INSERT t2 ('b 1', 2);

以下のSELECT文は、INSERT文の結果を示します。タブ文字はスペース文字よりもコード ポイントが小さいため、最初に挿入された文字列はパーティション1にマップされます。

   SELECT PARTITION, a, b FROM t2 ORDER BY 1;
   *** Query completed. 2 rows found. 3 columns returned.
   *** Total elapsed time was 1 second.
   PARTITION  a          b
 -----------  ------ -----
           1  b 1        1   (string contains single <tab> character)
           2  b 1        2   (string contains single <space> character)