Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2012
    Posts
    32

    Unanswered: Table partition query

    When i use a table partition by year for example say 2010 date in 2010 partition , 2011 in 2011 partion etc.

    I have a column row_id which is primary key so what i tried to do is keep the default date 2010-03-03 and transfer all date into the 2010 partition then i alter the table and made the default date to 2011-03-03 to put all the data in 2011 partition but i got error since the same row_id got duplicate values, is it possible to have similar row_id in the table ? i thought that they keep it in diffrent partition so it wont affect? can any one give me a possible solution to this problem

    Code:
       "YEARDATE" DATE WITH DEFAULT '2010-03-03' )  
    		  
    		 PARTITION BY RANGE("YEARDATE") 
    		 (PART "PART0" STARTING('2010-01-01') IN "USERSPACE1", 
    		 PART "PART1" STARTING('2011-01-01') IN "USERSPACE1", 
    		 PART "PART2" STARTING('2012-01-01') IN "USERSPACE1", 
    		 PART "PART3" STARTING('2013-01-01') IN "USERSPACE1", 
    		 PART "PART4" STARTING('2014-01-01') IN "USERSPACE1", 
    		 PART "PART5" STARTING('2015-01-01') IN "USERSPACE1", 
    		 PART "PART6" STARTING('2016-01-01') IN "USERSPACE1", 
    		 PART "PART7" STARTING('2017-01-01') IN "USERSPACE1", 
    		 PART "PART8" STARTING('2018-01-01') IN "USERSPACE1", 
    		 PART "PART9" STARTING('2019-01-01') IN "USERSPACE1", 
    		 PART "PART10" STARTING('2020-01-01') ENDING('2020-12-31') IN "USERSPACE1");

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Table partitioning is a physical data organization concept, while logically it is a single table. In other words, if row_id is your primary key, you can only have unique values of row_id, regardless of partitioning. If you want to have the same row_id in different partitions, you'll need to change your PK definition to include the partitioning key.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •