Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 05 Jan 2005 @ 11:33:55 GMT


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


Subj:   Re: Drop partition with CASE_N
 
From:   vimala.cherukuri

Hi,

We can only drop partitions if the partitioning expression is derived from the RANGE_N function. This cannot be done for the CASE_N function.

Instead you can use ALTER TABLE to modify the primary index and specify the entire partition expression excluding the partitions that need to be dropped, provided the table is empty.

     Example:
     --------
     show table t1;

      *** 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
          (
           a INTEGER,
           b INTEGER)
     PRIMARY INDEX ( a )
     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ),
     (a >  200 ) AND  (a <=  300 ));


     ALTER TABLE t1 MODIFY PRIMARY INDEX

     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ));

     ALTER TABLE t1 MODIFY PRIMARY INDEX
     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ));

      *** Table has been modified.

      *** Total elapsed time was 1 second.


     show table t1;

      *** 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
          (
           a INTEGER,
           b INTEGER)
     PRIMARY INDEX ( a )
     PARTITION BY CASE_N(
     (a >  0 ) AND  (a <=  100 ),
     (a >  100 ) AND  (a <=  200 ));

Thanks,

Vimala.



     
  <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: 15 Jun 2023