Partitioning

Database
Enthusiast

Partitioning

Somehow, a couple of our tables have the partition defined as such:

 

CHECK ((RANGE_N(Period_End_Dt  BETWEEN DATE '2013-04-30' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH ,
DATE '2016-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' MONTH ,
 NO RANGE OR UNKNOWN)) BETWEEN 1 AND 65535)

 

How do I change that to

Period_End_Dt  BETWEEN DATE '2013-04-30' AND DATE '2020-12-31' EACH INTERVAL '1' MONTH 

without dropping and recreating the table?

7 REPLIES
Enthusiast

Re: Partitioning

Try: 

 

Alter table tablename modify primary index partition by range_n( columnname between original_startdate and new_enddate);

Enthusiast

Re: Partitioning

You could also use:  alter table tablename modify primary index add range between new-start-date and new-end-date ....

;

Enthusiast

Re: Partitioning

I want to keep the same begin and end date range.  the ALTER / ADD statement doesn't allow ranges that overlap the existing range.

Enthusiast

Re: Partitioning

so if I understood correctly, you want to combine the 2 partition statements in the ddl into 1 statement, that I think will be possible only by deleting the partitions and then readding them back in one statement.  Which doesn't serve any technical purpose except that a simplified looking ddl.

rjg
Supporter

Re: Partitioning

Could you share more of your DDL including the complete primary index expression.

Your code looks like a check constraint.

 

Enthusiast

Re: Partitioning

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 14386 StartFragment: 314 EndFragment: 14354 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

CREATE MULTISET TABLE databasename.tablename ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO     (
      record_id CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC,
      level1key CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC,
      level2key CHAR(19) CHARACTER SET LATIN NOT CASESPECIFIC,
      Name1 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      ADDR_LINE_1 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      ADDR_LINE_2 CHAR(60) CHARACTER SET LATIN NOT CASESPECIFIC,
      ADDR_LINE_3 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      ADDR_LINE_4 CHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
      CITY CHAR(39) CHARACTER SET LATIN NOT CASESPECIFIC,
      State CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      Zip_Code CHAR(9) CHARACTER SET LATIN NOT CASESPECIFIC,
      Country_Name CHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      .
      .
      .
      .
      .
      .
      
      Start_Dt DATE FORMAT 'YYYY/MM/DD' TITLE 'Period//Start//Date' NOT NULL,
      End_Dt DATE FORMAT 'YYYY/MM/DD' TITLE 'Period//End//Date' NOT NULL)PRIMARY INDEX ( column1 ,column2 ,column3 )PARTITION BY RANGE_N(End_Dt  BETWEEN DATE '2013-04-30' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH ,DATE '2016-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' MONTH ,
Enthusiast

Re: Partitioning

That's correct