Teradata：如何将范围分区添加到非空表？

小编典典

Teradata：如何将范围分区添加到非空表？

sql

我有这样的表：

CREATE SET TABLE ONLINE_BANKING.TRANSACTIONS ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      transaction_id INTEGER NOT NULL,
      date_of_transaction DATE FORMAT 'YYYYMMDD' NOT NULL,
      amount_of_transaction DECIMAL(38,2) NOT NULL,
      transaction_type_code BYTEINT NOT NULL DEFAULT 25 , 
UNIQUE PRIMARY INDEX ( transaction_id );

我想将分区添加到我的填充数据表的date_of_transaction列中。

我尝试过这种方式：

ALTER TABLE TRANSACTIONS
MODIFY PRIMARY INDEX (date_of_transaction) -- tried to write different columns, but failed
ADD RANGE BETWEEN DATE '1998-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH;

但是Teradata返回错误：

DROP RANGE / ADD RANGE子句无对应级别，即RANGE_N函数

这是什么意思，我怎么能达到目标？

阅读 285

2021-05-16

共1个答案

小编典典

您不能将分区添加到尚未分区的现有表中，但是只能从分区表中添加或删除范围。

唯一的方法是创建一个新表和INSERT / SELECT，如下所示：

CREATE SET TABLE ONLINE_BANKING.TRANSACTIONS_PPI ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      transaction_id INTEGER NOT NULL,
      date_of_transaction DATE FORMAT 'YYYYMMDD' NOT NULL,
      amount_of_transaction DECIMAL(38,2) NOT NULL,
      transaction_type_code BYTEINT NOT NULL DEFAULT 25 , 
      ...)
PRIMARY INDEX ( transaction_id )
PARTITION BY
   RANGE_N (date_of_transaction BETWEEN DATE '1998-01-01' AND DATE '2015-12-31' 
   EACH INTERVAL '1' MONTH);

INSERT INTO ONLINE_BANKING.TRANSACTIONS_PPI
SELECT * FROM ONLINE_BANKING.TRANSACTIONS;

-- when everything is ok
DROP TABLE ONLINE_BANKING.TRANSACTIONS;
RENAME TABLE ONLINE_BANKING.TRANSACTIONS_PPI AS ONLINE_BANKING.TRANSACTIONS;

2021-05-16