Teradata:如何将范围分区添加到非空表? [英] Teradata: How to add range partition to non empty table?
本文介绍了Teradata:如何将范围分区添加到非空表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这样的桌子:
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 列的填充数据表添加分区.
I would like to add partition to my filled with data table to date_of_transaction column.
我试过这种方式:
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 返回错误:
However Teradata returned error:
DROP RANGE/ADD RANGE 子句无对应级别即为 RANGE_N 函数
DROP RANGE/ADD RANGE clause no corresponding level that is a RANGE_N function
这是什么意思,我如何才能实现目标?
What does it mean and what how can I achieve the goal?
推荐答案
您不能向未分区的现有表添加分区,但只能在已分区表中添加或删除范围.
You can't add partitioning to an existing table which is not partitioned, yet, you can only add or drop ranges from a partitioned table.
唯一的方法是像这样创建一个新表和 INSERT/SELECT:
The only way is to create a new table and INSERT/SELECT like this:
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;
这篇关于Teradata:如何将范围分区添加到非空表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文