Archives of the TeradataForum

Message Posted: Tue, 18 May 2004 @ 11:06:18 GMT

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

Hi,

This can be done using 'Alter Table':

     ALTER TABLE  MODIFY PRIMARY INDEX
     PARTITION BY CASE_N (
     (PERIOD_ID =  200401 ) AND  (POLE_ID =  1 ),
     (PERIOD_ID =  200401 ) AND  (POLE_ID =  2 ),
     (PERIOD_ID =  200401 ) AND  (POLE_ID =  3 ),
     (PERIOD_ID =  200402 ) AND  (POLE_ID =  1 ),
     (PERIOD_ID =  200402 ) AND  (POLE_ID =  2 ),
     (PERIOD_ID =  200402 ) AND  (POLE_ID =  3 ),
     (PERIOD_ID =  200403 ) AND  (POLE_ID =  1 ),
     (PERIOD_ID =  200403 ) AND  (POLE_ID =  2 ),
     (PERIOD_ID =  200403 ) AND  (POLE_ID =  3 ),
     (PERIOD_ID =  200404 ) AND  (POLE_ID =  1 ),
     (PERIOD_ID =  200404 ) AND  (POLE_ID =  2 ),
     (PERIOD_ID =  200404 ) AND  (POLE_ID =  3 ),
     (PERIOD_ID =  200405 ) AND  (POLE_ID =  1 ),
     (PERIOD_ID =  200405 ) AND  (POLE_ID =  2 ),
     (PERIOD_ID =  200405 ) AND  (POLE_ID =  3 ),
     (PERIOD_ID =  200406 ) AND  (POLE_ID =  1 ),
     (PERIOD_ID =  200406 ) AND  (POLE_ID =  2 ),
     (PERIOD_ID =  200406 ) AND  (POLE_ID =  3 ),
     .
     .
     .
     .

     NO CASE, UNKNOWN);

For Example:

     CREATE TABLE TAB1( COL1 INT,
     COL2 INT,
     COL3 INT)
     PRIMARY INDEX(COL1)
     PARTITION BY CASE_N(
     (COL1 = 100) AND (COL2 = 1),
     (COL1 = 100) AND (COL2 = 2),
     (COL1 = 100) AND (COL2 = 3),
      NO CASE, UNKNOWN);

      *** Table has been created.

      *** Total elapsed time was 1 second.

Altering the table to add more partitions:

     ALTER TABLE TAB1 MODIFY PRIMARY INDEX
     PARTITION BY CASE_N(
     (COL1 = 100) AND (COL2 = 1),
     (COL1 = 100) AND (COL2 = 2),
     (COL1 = 100) AND (COL2 = 3),
     (COL1 = 200) AND (COL2 = 1),
     (COL1 = 200) AND (COL2 = 2),
     (COL1 = 200) AND (COL2 = 3),
      NO CASE, UNKNOWN);

      *** Table has been modified.

      *** Total elapsed time was 1 second.


     SHOW TABLE TAB1;

      *** Text of DDL statement returned.

      *** Total elapsed time was 1 second.

     ------------------------------------------

     CREATE SET TABLE TEST.T1 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           COL1 INTEGER,
           COL2 INTEGER,
           COL3 INTEGER)
     PRIMARY INDEX ( COL1 )
     PARTITION BY CASE_N(
     (COL1 =  100 ) AND  (COL2 =  1 ),
     (COL1 =  100 ) AND  (COL2 =  2 ),
     (COL1 =  100 ) AND  (COL2 =  3 ),
     (COL1 =  200 ) AND  (COL2 =  1 ),
     (COL1 =  200 ) AND  (COL2 =  2 ),
     (COL1 =  200 ) AND  (COL2 =  3 ),
      NO CASE, UNKNOWN);

Thanks,

Vimala.

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