Archives of the TeradataForum
Message Posted: Mon, 10 Jul 2006 @ 13:41:47 GMT
We have a very large transaction table with PPI assigned on the transaction date as follows:
PARTITION BY RANGE_N(TXN_DTE BETWEEN DATE '2004-06-01' AND DATE '2006-05-31' EACH INTERVAL '1' MONTH , DATE '2006-06-01' AND DATE '2006-06-30' EACH INTERVAL '1' MONTH , DATE '2006-07-01' AND DATE '2006-07-31' EACH INTERVAL '1' MONTH , DATE '2006-08-01' AND DATE '2006-08-31' EACH INTERVAL '1' MONTH , DATE '2006-09-01' AND DATE '2006-09-30' EACH INTERVAL '1' MONTH , NO RANGE);
The table is to hold 25 months worth of data and on the 1st of the month we run code to drop the old partition and add a new one:
ALTER TABLE TRANS_FINANCIAL.FINANCIAL_TXN MODIFY PRIMARY INDEX(PRDID) DROP RANGE BETWEEN DATE '2004-05-01' AND DATE '2004-05-31' EACH INTERVAL '1' MONTH ADD RANGE BETWEEN DATE '2006-09-01' AND DATE '2006-09-30' EACH INTERVAL '1' MONTH WITH DELETE;
I expected this to remove any data that sat in the partition being removed, but this does not appear to be the case. After a bit of testing it would appear that this is down to the NO RANGE being assigned to the PPI. Presumably all the data that was in the range being removed has simply been moved to the NO RANGE partition.
Is there any way of removing the NO RANGE without the need to drop and recreate the table?
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|