Extend Partition Behavior

Database
Enthusiast

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?

1 REPLY
Teradata Employee

Re: Extend Partition Behavior

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.