Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Question on PPIs & JIs
 
From:   Antzakas, Constantinos

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016