Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Sat, 24 Jan 2009 @ 20:17:49 GMT


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


Subj:   Re: Changing Partition definition
 
From:   Joerg Mutsch

Hi Ronald,

You have different options for adding a new partition. With the command

     ALTER TABLE table_name
     MODIFY [[NOT] UNIQUE] PRIMARY INDEX [index_name | NOT NAMED]
     [(primary_index_column_list)]
     PARTITION BY { new_partitioning_expression
     |(new_partitioning_expression
     {, new_partitioning_expression} ) }
     [null_partition_handler];

You can 're-create' your partitions which means it drops your old partitions and creating the new one.

If you want to add just a new partition use

     ALTER TABLE TABLENAME
     MODIFY PRIMARY INDEX
     ADD RANGE BETWEEN DATE '2000-01-01' AND DATE '2002-12-01' EACH INTERVAL
     '1' MONTH

Be aware that the table then looks like

     CREATE TABLE  ....
     (....)
     PRIMARY INDEX ( L_ORDERKEY )
     PARTITION BY RANGE_N(L_SHIPDATE  BETWEEN DATE '1992-01-01' AND DATE
     '1998-12-01' EACH INTERVAL '1' MONTH ,
     DATE '2000-01-01' AND DATE '2002-12-01' EACH INTERVAL '1' MONTH )

In this case I just would re-arrange the Partitions completely

If you want to delete a partition use

     alter table Tablename
     modify primary index
     drop range between 1020301 and 1020331 with delete

This drops your partition and all related rows in this partition.

Hope this helps


Regards

Joerg



     
  <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