Can we cancel Alter Table?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Can we cancel Alter Table?

Hi, 

 

We are stuck with a long running ALTER table command. From previous forum articles/blogs and posts, I have gathered that we cannot cancel an ALTER Table once submitted. Since the TJ size is changing with this statement, I presume that this does not fall into a classical "Alter Table" definition and can be cancelled. I just don't want to go-ahead and abort it without knowing whether it will go into recovery or will not work at all. 

 

This is what I am running for more than 30 hours. 

ALTER TABLE <DB_NAME>.<TB_NAME>TO CURRENT WITH DELETE;

 

We migrated on 15.10 and moved to intelliflex recently - obviously everything is FB protected. The table is around ~5-6TB (non-BLC'd) and 500GB with BLC. Unfortunately it has NUSI as well (which I beleive is again FB protected). 

 

Any help here will be much appreciated. 

 

Many Thanks,

Khalid

 

 

 

 

 

5 REPLIES
Enthusiast

Re: Can we cancel Alter Table?

Hi @dnoeth @Fred, refering to you as you have already posted some information about it. 

 

 Can you please add anyting for the above request? 

 

Many Thanks,

Khalid

Highlighted
Junior Contributor

Re: Can we cancel Alter Table?

You're right, this is not a normal ALTER TABLE.

I don't know if it can be aborted (but then you might wait another 30 hours to finish the rollback).

 

Seems like you defined partitioning in a (dumb) way that leads to reassigning each row to a new partition, didn't you test it before?

Can you show the PARTITION BY definition?

 

 

Enthusiast

Re: Can we cancel Alter Table?

Its a dynamic partition. I will paste the partition definition here.
We are thinking to abort this transaction and cancel the rollback and restore from archives that might be quicker.
Having said that, its been implemented for ages and used to finish in 8ish hours. I believe this is all down to INFX fallback compulsion.
Enthusiast

Re: Can we cancel Alter Table?

SyntaxEditor Code Snippet

DATE_COLUMN   BETWEEN ADD_MONTHS(((DATE + 1 )- (EXTRACT(DAY FROM (DATE ))( TITLE 'Day'))),(-36 )) , DATE - (EXTRACT(DAY FROM (DATE ))( TITLE 'Day')) EACH INTERVAL '1' MONTH 
Junior Contributor

Re: Can we cancel Alter Table?

This covers the previous 36 months not including the current month, the ALTER should not change the internal partition number.

 

Assuming there's an ALTER TABLE TO CURRENT WITH DELETE this will delete the oldest month, but as far as I know it's not a fastpath delete, thus using the Transient Journal. 

I don't have expertise in IF, maybe it's actually slower due to Fallback & BLC, but I know that I would never apply WITH DELETE, I would submit a SQL DELETE before.

In your case this would be a DELETE FROM myTable WHERE PARTITION = 1

 

Btw, why do you use those rolling partitions?

I would define a fixed range far into the future, DATE_COLUMN BETWEEN DATE '2015-01-01' AND DATE '2040-12-31' EACH INTERVAL '1' MONTH, thus you will never have to modify the partitioning again.

Then you simply delete the oldest month using a SQL DELETE, an empty partition has zero overhead.

No need for ALTER TABLE, exclusive locks, Transient Journal.