Archives of the TeradataForum
Message Posted: Thu, 11 Nov 2010 @ 17:16:58 GMT
My firm is planning to expand our Teradata Version 12 system, but until then space is limited. (Since it is V12 the Hot/Cold V13 solution is not an option)
I have been tasked with coming up with a "Teradata Archive Solution". What are other companies doing in this area? Is there a good presentation out there somewhere? We'd like to know if the proposed solutin below is "within the norm".
Since most of the large tables we have are PPI, I am proposing archiving the oldest partition (month) and then using ALTER TABLE table_1 MODIFY PRIMARY INDEX DROP RANGE... dropping the oldest month and adding the newest month "at the ends" of the range. (There is an example in the Physical DB Design course page 9-58 through 9-67
Should we use the WITH DELETE option or the WITH INSERT INTO option? If we use the WITH INSERT INTO seems like we won't save the space.
Current partitioning Scheme is... (notice the partitoning from year 2001 to year 2100)
PRIMARY INDEX ( KEY_ACCT ) PARTITION BY RANGE_N( DT2_MONTH_END BETWEEN DATE '2001-01-01' AND DATE '2100-12-31' EACH INTERVAL '1' MONTH ) Min (DT2_MONTH_END) = 12/31/2008 Max (DT2_MONTH_END) = 7/31/2010
PI of KEY_ACCT has time stamps going back to year 1999
So they are keeping 18 months of data online.
ALTER TABLE table_1 MODIFY PRIMARY INDEX DROP RANGE BETWEEN DATE 2008-12-01 AND DATE 2008-12-31 EACH INTERVAL 1 MONTH ADD RANGE BETWEEN DATE 2010-08-01 AND DATE 2010-08-31 EACH INTERVAL 1 MONTH WITH DELETE; ALTER TABLE table_1 MODIFY PRIMARY INDEX DROP RANGE BETWEEN DATE 2008-12-01 AND DATE 2008-12-31 EACH INTERVAL 1 MONTH ADD RANGE BETWEEN DATE 2010-08-01 AND DATE 2010-08-31 EACH INTERVAL 1 MONTH WITH INSERT INTO table_1_old_history;
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|