CODEWITHSUNDEEP
teradata
tipanverella
tipanverella

Reputation: 3677

Teradata: How to extend the range partition of a non-empty partitioned table?

I have create a table, mydb.mytable, with essentially the following SQL, say last week:

CREATE MULTISET TABLE mydb.mytable ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( master_transaction_header VARCHAR(64) CHARACTER SET LATIN NOT CASESPECIFIC, demand_date DATE FORMAT 'YY/MM/DD', item_id BIGINT, QTY INTEGER, price DECIMAL(15,2)) PRIMARY INDEX ( master_transaction_header ) PARTITION BY RANGE_N(demand_date BETWEEN DATE '2018-01-01' AND CURRENT_DATE EACH INTERVAL '1' DAY );

When I try to insert data into it, for say yesterday, teradata gives me the following error message

Partitioning violation for table mydb.mytable

When I try to extend the partition using:

ALTER TABLE mydb.mytable MODIFY PRIMARY INDEX (master_transaction_header) ADD RANGE BETWEEN DATE '2018-03-15' AND CURRENT_DATE EACH INTERVAL '1' DAY;

I get the following error message from teradata:

The altering of RANGE_N definition with CURRENT_DATE/CURRENT_TIMESTAMP is not allowed.

I understand that I could:

  1. Create a copy with PARTITION BY RANGE_N(demand_date BETWEEN DATE '2018-01-01' AND '9999-12-31' EACH INTERVAL '1' DAY );
  2. Insert all the data from the old table into the new one
  3. drop the old table
  4. rename the new table

but I am hoping that teradata provides a more elegant way to add partitions to an existing partitioned table.

I have already consulted the following stackoverflow posts:

They were enlightening, but I could not conjure an answer from the discussion therein.

Upvotes: 0

Views: 6432

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Using CURRENT_DATE for partitioning is possible, but I never found a use case for it.

When you create that table it is resolved to the current date, but not changed afterwards, check the ResolvedCurrent_Date column in dbc.PartitioningConstraintsV. When you submit an ALTER TABLE mydb.mytable TO CURRENT it's resolved again and the range modified.

But there's no reason to do this, simply define the range large enough, so you never have to modify it again, e.g.

PARTITION BY RANGE_N(demand_date
                     BETWEEN DATE '2018-01-01'
                         AND DATE '2040-01-01' EACH INTERVAL '1' DAY);

Unused partitions have zero overhead in Teradata.

Upvotes: 3

Related Questions