Archives of the TeradataForum

Message Posted: Wed, 25 Feb 2009 @ 10:12:25 GMT

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

Good morning all,

I'm working on a client which utilizes both time-dependent partitioning over certain tables' primary index and Join Indexes using the same tables.

The relative table DDLs are typically following the below pattern:

     /CREATE SET TABLE InvoiceDetails, /
     /NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT ( .../
     /.../
     /PRIMARY INDEX(SourceID, TrnsID)/
     /PARTITION BY RANGE_N (TrnsDate BETWEEN '2007-01-01' AND DATE
     '2008-12-31' EACH INTERVAL '1' MONTH, NO RANGE, UNKNOWN)/

The task we need to accomplish is to change the partition definition, so that TrnsDate = ['2008-01-01', '2009-12-31'] but I'm concerned on the impact that change might have over some JIs which are using the InvoiceDetails table.

Which procedure would you recommend to follow so that to minimaze the impact and the time needed to recreate both the DDL and JI ?

I was thinking of the following:

1. Store JIs DDLs on a file.

2. Drop JIs.

3. Copy InvoiceDetails table data to a new table with the new partition definition (2008-2009).

4. Delete/Drop old InvoiceDetails table and rename the new table as "InvoiceDetails"

5. Collect statistics on the "new" InvoiceDetails table

6. Recreate JIs based on the step 1 DDLs.

Would someone else recomment a different approach ?

Thank you in advance,

Constantinos Antzakas
Athens, Greece

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