ALTER TABLE TO CURRENTの例 - Advanced SQL Engine - Teradata Database

例: パーティション式が複数のCURRENT_DATE関数に基づく場合のテーブル行パーティションの変更

テーブルでは直近の2つの四半期がそれぞれ個別に行パーティション化されており、その他のすべてのデータが別の行パーティションにあるとします。以下の例では各四半期の開始時にCURRENT_DATEを調整します。テーブルは2009年1月1日に作成されているとします。従って、CURRENT_DATEは作成時に2009年1月1日に解決されます。

     CREATE SET TABLE sales, NO FALLBACK (
       storeID   INTEGER,
       amount    DECIMAL(10,2),
       sale_date DATE FORMAT 'YYYY/MM/DD' NOT NULL)
     PRIMARY INDEX (storeID) 
     PARTITION BY CASE_N(sale_date>=CURRENT_DATE/*latest quarter data*/,
                         sale_date<CURRENT_DATE 
                     AND sale_date>=CURRENT_DATE-INTERVAL '3' MONTH,
                         NO CASE);

テーブルには、解決された2009年1月1日のCURRENT_DATEを持つ次の行が含まれています。

2009年4月1日に次のリクエストを実行するとします。

     ALTER TABLE sales TO CURRENT; 

解決されたCURRENT_DATEは2009年4月1日に変わり、テーブルの行は次のように調整されます。

2009年1月1日に次のCREATE TABLEリクエストを実行したとします。

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD' NOT NULL)
     PRIMARY INDEX (cust_no) 
     PARTITION BY CASE_N(policy_expiration_date>=CURRENT_DATE,
                         policy_expiration_date<CURRENT_DATE 
                     AND policy_expiration_date>=CURRENT_DATE-
                                                 INTERVAL '3' MONTH);

テーブルの行には、2009年1月1日に解決されたCURRENT_DATEが入り、次のようになります。

2009年4月1日に次のALTER TABLE TO CURRENTリクエストを実行します。

     ALTER TABLE customer TO CURRENT; 

次の行は、改定されたCURRENT_DATE値に基づくどの行パーティションにも移動できないため、システムは要求元にエラー メッセージを返します。

このエラーを回避するには、代わりに2009年4月1日に次のALTER TABLE TO CURRENTリクエストを実行します。

     ALTER TABLE customer TO CURRENT WITH DELETE; 

このALTER TABLE TO CURRENTリクエストの結果として、Vantageはもう関係がなくなったために次の行をテーブルから削除します。

テーブルの行には、2009年4月1日に解決されたCURRENT_DATEが入り、次のようになります。

例: CREATE TABLEリクエストでのCURRENT_DATE関数の最適化されない使用法

この例は、CASE_N条件内でCURRENT_DATE関数を指定しますが、この方法ではALTER TABLE TO CURRENTリクエストを使用した調整を、全パーティションよりも少ないパーティション数をスキャンするように最適化できません。この結果、customerに対してALTER TABLE TO CURRENTリクエストが実行されるたびに、Vantageは新たに解決される日付の行パーティションを調整するために、フル テーブル スキャンを実行する必要があります。

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX(cust_no) 
     PARTITION BY CASE_N(policy_expiration_date=CURRENT_DATE, 
                  NO CASE);

例: CREATE TABLEリクエストでのCURRENT_DATE関数の最適化されない使用法

この例でも、CASE_N条件の引数としてCURRENT_DATE関数を指定しますが、この方法ではALTER TABLE TO CURRENTリクエストを使用した調整を、customerの全行パーティションよりも少ないパーティション数をスキャンするように最適化できません。

このリクエストは2つの異なるパーティション列にCURRENT_DATE関数を指定しますが、Vantageはどのような場合にある行パーティションのすべての行が影響を受けないのかを判別できません。これは、新たに解決される日付の影響を判別するために使用できるpolicy_expiration_date列とbirth_date列との間の既知の関係がないためです。この結果、customerに対してALTER TABLE TO CURRENTリクエストが実行されるたびに、Vantageは新たに解決される日付の行パーティションを調整するために、フル テーブル スキャンを実行する必要があります。

     CREATE SET TABLE customer, NO FALLBACK (
       cust_name              CHARACTER(10),
       cust_no                INTEGER,
       birth_date             DATE,
       policy_expiration_date DATE FORMAT 'YYYY/MM/DD')
     PRIMARY INDEX (cust_no) 
     PARTITION BY CASE_N(policy_expiration_date >= CURRENT_DATE,
                         policy_expiration_date >= CURRENT_DATE,
                         birth_date < CURRENT_DATE - INTERVAL '20' YEAR, 
                         NO CASE);

例: 結合インデックスの行パーティションの変更

「例: パーティション式が複数のCURRENT_DATE関数に基づく場合のテーブルの行パーティションの変更」のテーブルで、2009年1月1日に、1つの行パーティションに現行四半期のデータを含め、2000.00以降の他のすべてのsale_amtのデータをもう1つの行パーティションに含めるように、行パーティションのスパース結合インデックスj_salesを定義するとします。

     CREATE JOIN INDEX j_sales AS 
       SELECT * 
       FROM sales 
       WHERE sale_amt>=2000.00
     PRIMARY INDEX (store_ID)
     PARTITION BY CASE_N(sale_date >= CURRENT_DATE, NO CASE);

解決されたCURRENT_DATEが2009年1月1日であると仮定した場合、結合インデックスj_salesには次の行が含まれます。

2009年4月1日に、次のALTER TABLE TO CURRENTリクエストを実行します。

     ALTER TABLE j_sales TO CURRENT;

結合インデックスj_salesの行は次のように調整されます。

例: 行パーティションの変更

この例では、現行パーティションおよび履歴パーティションが、パーティション式で解決されたCURRENT_TIMESTAMP関数またはCRRENT_DATE関数に関して定義されている場合、データを1つ以上の現行行パーティションおよび1つ以上の履歴行パーティションとしてパーティション化しなければならないときに、CURRENT_TIMESTAMP関数またはCURRENT_DATE関数をパーティション式で使用する最も適切な方法を示しています。

例は、ALTER TABLE DROP RANGEまたはALTER TABLE ADD RANGEリクエストを実行するたびに決定しなければならない明示的な日付を使用してテーブルに対して行パーティションを再定義するのではなく、ALTER TABLE TO CURRENTリクエストを使用して定期的にテーブルを調整し、古いデータを現在の行パーティションから1つ以上の履歴パーティションに移動させる方法を示しています。パーティション式でCURRENT_DATEおよびCURRENT_TIMESTAMP関数を使用する場合は、式をテーブルまたは結合インデックスに定義する前に、関数の使用目的について慎重に評価する必要があります。

次のテーブルの定義が2009年(その時点でのCURRENT年)に作成されたと仮定します。

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

5年分の履歴データ、現行年のデータ、および次年のデータを記録するように、テーブルが行パーティション化されているとします。

次年に、注文に対する行パーティションを変更して、5年分の履歴データ、現行年のデータ、および次の年のデータを維持する場合、以下のようなALTER TABLEリクエストを2011年に実行できます。

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

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

最初に作成した方法でordersを作成する代わりに、パーティション式にCURRENT_DATE関数を使用して作成し、パーティションの変更を簡素化するとします。このリクエストは、CREATE TABLE日付時点で、直近の5年間の履歴データ、現行年のデータ、および翌年のデータが、全部で7年間のテーブルに保管されることを想定しており、これは前の事例と同じです。

この場合のCREATE TABLE DDLは次のようになります。

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(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 TO CURRENTリクエストを毎年実行するようにスケジュールすることができます。このリクエストは、パーティションを効率的に削除および追加することにより、行パーティション ウィンドウをずらしていきます。

     ALTER TABLE orders TO CURRENT WITH DELETE;

CURRENT_DATE関数を使用して行パーティション式を指定しなかった場合には、新しい日付に基づいたデータの再パーティション化が必要になるたびに文を変更する必要がありましたが、この文は変更する必要がありません。

前述のすべての事例では、行パーティションは年が変わるときに開始します。

最初の事例では、年の境目で毎回開始するようにパーティション化の変更が行なわれますが、ALTER TABLEリクエストで希望の日付を指定することにより、開始月をずらして年の途中の月で開始するように変更することも可能です。

2番目の例は、年の境目でのみ開始するように作成されています。

次のCREATE TABLEリクエストを使用して、月の初めにパーティション化を開始するようにできます。この場合、CREATE TABLE日付の時点で、直近の71ヶ月分の履歴、現行月のデータ、および今後の12ヶ月のデータが、全部で84ヶ月のテーブルに保管されます。

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(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 TO CURRENTリクエストを、毎月またはもっと期間を空けて(ただし、今後の月数が終わりになる前のいつかに)実行するようにスケジュールできます。

このリクエストは、パーティションを効率的に削除および追加することにより、ALTER TABLE TO CURRENT日付時点で直近の71ヶ月分の履歴データ、現行月のデータ、およびそれからの12ヶ月のデータを、合計84ヶ月のテーブルに含めることができるように、パーティション ウィンドウをずらします。

     ALTER TABLE orders TO CURRENT WITH DELETE;

次の例ではより単純な行パーティションを使用していますが、ユーザーが行パーティションを変更するためにALTER TABLE TO CURRENTリクエストを実行するとき、行を調整するためにテーブル全体のスキャンが必要な場合があるため、これが最適とは言えない可能性があります。この例では、CREATE TABLE日付の時点で、およそ2191日間分の履歴データ、現行日のデータ、およびおよそ365日の今後のデータを、合計でほぼ7年のテーブルに含めることができると仮定しています。

     CREATE TABLE orders (
       o_orderkey      INTEGER NOT NULL,
       o_custkey       INTEGER,
       o_orderstatus   CHARACTER(1) CASESPECIFIC,
       o_totalprice    DECIMAL(13,2) NOT NULL,
       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,
       o_orderpriority CHARACTER(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 TO CURRENTリクエストを、毎日またはもっと期間を空けて(ただし、今後の日数が終わりになる前のいつかに)実行するようにスケジュールできます。このリクエストは、CURRENT_DATEと、最後にCREATE TABLEまたはALTER TABLE TO CURRENTリクエストが実行された日付が同じ場合にのみ、行パーティションの削除と追加を実行してパーティションのウィンドウを前進させます。それ以外の場合は、行を調整するためにテーブル全体をスキャンする必要があります。

     ALTER TABLE orders TO CURRENT WITH DELETE;

このリクエストは、最新のCREATE TABLEリクエストまたはALTER TABLE TO CURRENTリクエストが実行されたのと同じ日にALTER TABLE TO CURRENTリクエストを実行しない場合、非常に非効率的になる可能性があります。

移動しなければならない行数が増えるため、最後に解決された日付と新しく解決された日付との間の日数の増加に応じて、パフォーマンスが低下します。例えば、最後に解決された日付が2010年1月1日であり、次のALTER TABLE TO CURRENTリクエストが2010年2月2日に実行される場合、Vantageはorders表のすべての行を新しいパーティションに移動しなければなりません。

また、この例で指定されたパーティションを使用すると、CREATE TABLEまたはALTER TABLE TO CURRENTリクエストが2月29日に実行された場合に、システムは無効日エラーを報告するエラー メッセージを返します。