Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 01 Mar 2006 @ 11:00:56 GMT


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


Subj:   Partitioning statement
 
From:   Okell, Alan

All,

     CREATE MULTISET TABLE Txn_Table ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           TXN_ID DECIMAL(13,0) NOT NULL,
           PRDID INTEGER NOT NULL
     ..
           TXN_DTE DATE FORMAT 'YYYY/MM/DD' NOT NULL)
           PRIMARY INDEX ( PRDID )
     PARTITION BY RANGE_N(TXN_DTE  BETWEEN DATE '2001/05/01' AND DATE
     '2006/02/28' EACH INTERVAL '1' MONTH , NO RANGE);

For a table with the above DDL, when the oldest partition is dropped using an Alter Table, the difference in DDL when seen from a SHOW TABLE is that the start date (Between Date '2001-05-01' advances one month to '2001-06-01'). However, when a new partition is added, rather than the end date '2006-02-28' being advanced one month as I would have expected the new DDL appears as follows:

     CREATE MULTISET TABLE Txn_Table ,NO FALLBACK ,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL,
         CHECKSUM = DEFAULT
         (
          TXN_ID DECIMAL(13,0) NOT NULL,
          PRDID INTEGER NOT NULL,
          PRIMARY INDEX ( PRDID )
     ..
     TXN_DTE DATE FORMAT 'YYYY/MM/DD' NOT NULL))
     PARTITION BY RANGE_N(TXN_DTE  BETWEEN DATE '2001/05/01' AND DATE
     '2006/02/28' EACH INTERVAL '1' MONTH ,
     DATE '2006/03/01' AND DATE '2006/03/31' EACH INTERVAL '1' MONTH , NO RANGE);

I guess the question I am asking is are there any fundamental differences between the following partitioning statements, or is one just a long handed version of the other.

     PARTITION BY RANGE_N(TXN_DTE  BETWEEN DATE '2001/05/01' AND DATE
     '2006/03/31' EACH INTERVAL '1' MONTH , NO RANGE);

     PARTITION BY RANGE_N(TXN_DTE  BETWEEN DATE '2001/05/01' AND DATE
     '2006/02/28' EACH INTERVAL '1' MONTH ,
     DATE '2006/03/01' AND DATE '2006/03/31' EACH INTERVAL '1' MONTH , NO RANGE);

Regards,

Alan Okell
LTSB



     
  <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