Archives of the TeradataForum

Message Posted: Mon, 10 Jul 2006 @ 13:41:47 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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?

Thanks,

Martin

	<Prev	Next>		<<First	<Prev	Next>	Last>>