All Forums Database
brokenp87 7 posts Joined 05/12
26 Jan 2015
Extend Partition Behavior

Hi all,
I was extending the partition of some tables to host the 2015 and I have notice this strange behavior.
I have this table called Table1

CREATE MULTISET TABLE Table1 ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      FieldA CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      FieldB CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      FieldC DATE FORMAT 'YYYY-MM-DD' NOT NULL,
      FieldD SMALLINT,
      FieldE CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      FieldF TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
      FieldG BYTEINT NOT NULL DEFAULT 0 )
UNIQUE PRIMARY INDEX ( FieldA ,FieldB ,DT_RIF )
PARTITION BY ( RANGE_N(DT_RIF  BETWEEN DATE '2010-12-31' AND DATE '2014-12-31' EACH INTERVAL '1' MONTH ),
RANGE_N((HASHBUCKET(HASHROW(FieldA ))) MOD  155  BETWEEN 1  AND 154  EACH 1 , NO RANGE OR UNKNOWN) );

And I want to extend the partition via this statement:

ALTER TABLE Table1
 MODIFY PRIMARY INDEX ADD RANGE#L1 BETWEEN DATE '2015-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH;

The result partition when you SHOW TABLE is:

PARTITION BY ( RANGE_N(DT_RIF  BETWEEN DATE '2010-12-31' AND DATE '2014-12-31' EACH INTERVAL '1' MONTH ,
DATE '2015-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH ),
RANGE_N((HASHBUCKET(HASHROW(FieldA ))) MOD  155  BETWEEN 1  AND 154  EACH 1 , NO RANGE OR UNKNOWN) );

As you can see the DT_RIF new partition is made up of "two separate pieces", but I don't really understand why this happens, since continuity should be present, isn't it?
If you modify the starting table partitioning expression in this way (starting from '2011-01-31' instead '2010-12-31').

PARTITION BY ( RANGE_N(DT_RIF  BETWEEN DATE '2011-01-31' AND DATE '2014-12-31' EACH INTERVAL '1' MONTH ),
RANGE_N((HASHBUCKET(HASHROW(FieldA ))) MOD  155  BETWEEN 1  AND 154  EACH 1 , NO RANGE OR UNKNOWN) );

And resubmit the same ALTER TABLE statement the partition has a "single range":

PARTITION BY ( RANGE_N(DT_RIF  BETWEEN DATE '2011-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH ),
RANGE_N((HASHBUCKET(HASHROW(FieldA ))) MOD  155  BETWEEN 1  AND 154  EACH 1 , NO RANGE OR UNKNOWN) );

Does anyone knows why this happens?

Fred 1096 posts Joined 08/04
27 Jan 2015

In the first example, the CREATE TABLE defines one partition of a single day and the rest full months. Since there is no uniform pattern for the original range, adding new months is treated as a new range definition.
In the second example, the CREATE TABLE defines only full month partitions.

You must sign in to leave a comment.