Archives of the TeradataForum

Message Posted: Thu, 11 Nov 2010 @ 17:16:58 GMT

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

Hello:

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

Question:

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.

Proposed Solution

     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;

Regards,

Mark Jahnke

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