0

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.

tipanverella
  • 3,477
  • 3
  • 25
  • 41

1 Answers1

3

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.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thank you so much. `ALTER TABLE mydb.mytable TO CURRENT` is exactly what I was looking for. I get your point about creating partition that goes far enough into the future, but I prefer the elegance afforded by `CURRENT_DATE`. Thank you for enabling me. – tipanverella Mar 21 '18 at 17:35
  • 1
    Trouble is without an empty partition for tomorrow nor a NO RANGE partition, if you do not update the partition definition daily your ETL. While elegant, I'd rather avoid the daily modification of a production table's definition for partitioning maintenance. – Rob Paller Mar 21 '18 at 17:42
  • 2
    @RobPaller is abolutely right, but I wouldn't call it elegant, it's error prone (and even worse, when you add `NO RANGE` and forget to extend). This syntax was ment to be used with a rolling range, i.e. CURRENT_DATE for both begin and end. – dnoeth Mar 21 '18 at 17:51