CODEWITHSUNDEEP
sqlteradatapartitioningdatabase-partitioning
Rocketq
Rocketq

Reputation: 5781

Teradata: How to add range partition to non empty table?

I have such table:

CREATE SET TABLE ONLINE_BANKING.TRANSACTIONS ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      transaction_id INTEGER NOT NULL,
      date_of_transaction DATE FORMAT 'YYYYMMDD' NOT NULL,
      amount_of_transaction DECIMAL(38,2) NOT NULL,
      transaction_type_code BYTEINT NOT NULL DEFAULT 25 , 
UNIQUE PRIMARY INDEX ( transaction_id );

I would like to add partition to my filled with data table to date_of_transaction column.

I tried this way:

ALTER TABLE TRANSACTIONS
MODIFY PRIMARY INDEX (date_of_transaction) -- tried to write different columns, but failed
ADD RANGE BETWEEN DATE '1998-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH;

However Teradata returned error:

DROP RANGE/ADD RANGE clause no corresponding level that is a RANGE_N function

What does it mean and what how can I achieve the goal?

Upvotes: 0

Views: 9339

Answers (1)

dnoeth
dnoeth

Reputation: 60462

You can't add partitioning to an existing table which is not partitioned, yet, you can only add or drop ranges from a partitioned table.

The only way is to create a new table and INSERT/SELECT like this:

CREATE SET TABLE ONLINE_BANKING.TRANSACTIONS_PPI ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO
     (
      transaction_id INTEGER NOT NULL,
      date_of_transaction DATE FORMAT 'YYYYMMDD' NOT NULL,
      amount_of_transaction DECIMAL(38,2) NOT NULL,
      transaction_type_code BYTEINT NOT NULL DEFAULT 25 , 
      ...)
PRIMARY INDEX ( transaction_id )
PARTITION BY
   RANGE_N (date_of_transaction BETWEEN DATE '1998-01-01' AND DATE '2015-12-31' 
   EACH INTERVAL '1' MONTH);

INSERT INTO ONLINE_BANKING.TRANSACTIONS_PPI
SELECT * FROM ONLINE_BANKING.TRANSACTIONS;

-- when everything is ok
DROP TABLE ONLINE_BANKING.TRANSACTIONS;
RENAME TABLE ONLINE_BANKING.TRANSACTIONS_PPI AS ONLINE_BANKING.TRANSACTIONS;

Upvotes: 2

Related Questions