Archives of the TeradataForum

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

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

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>>