Lock & Timing Considerations for Alter on Multi-Terabyte Table

Database
Fan

Lock & Timing Considerations for Alter on Multi-Terabyte Table

I'm looking for Lock and Timing considerations for ALTER of a multi-terabye table.   We're considering using ALTER since we are not changing the PI or SI's and we don't need to rebuild our AJI's.

DNoeth has done a great job of explaining the difference between ALTER TABLE and INSERT/SELECT approach here: http://developer.teradata.com/blog/dnoeth/2013/08/why-dont-you-use-alter-table-to-alter-a-table

Is there any way to determine or estimate when the Tables Locks on DBC will occur? 

How can I determine the system impact when the DBC locks do occur?

 

Do you have any thoughts on this?

Thanks. Steve

6 REPLIES
Junior Contributor

Re: Lock & Timing Considerations for Alter on Multi-Terabyte Table

When you look at Explain you will notice that all locks are applied when the query starts. As those locks are on a RowHash level they should only impact queries requiring table level write locks (imho no DDL needs those).
Fan

Re: Lock & Timing Considerations for Alter on Multi-Terabyte Table

In other words, you don't expect the ALTER TABLE to affect other DDL operations, e.g. CREATE VOLATILE TABLE unless that other DDL operation is directly referencing my large table.  Makes sense.

 

What about the timing?  Is there any way to estimate the duration of the LOCKs?   
 - Can I  assume that the ALTER will only affect the table header. 
 - This table has several compressed columns with many values.  Will this affect my run time?

Junior Contributor

Re: Lock & Timing Considerations for Alter on Multi-Terabyte Table

Creating Volatile Tables doesn't access any DBC tables, the definition is only within memory.
You already read http://community.teradata.com/t5/Database/Alter-table-on-huge-table/m-p/28040/highlight/true#M12545, which describes when only the table header is modified. Compressed columns will not affect runtime (unless you alter the compression of existing columns).
Fan

Re: Lock & Timing Considerations for Alter on Multi-Terabyte Table

The referenced article, http://www.teradatamagazine.com/table_dressing.aspx, indicates that in order to estimate time, we need to understand if the table header has enough room for the  presense bits for our new NULL columns. 
- If there is room for the NULL column presense bits, the ALTER could run quite fast.
- If no room, the ALTER will need to rewrite each block.

Maybe we can test this with a smaller copy of the table.


Enthusiast

Re: Lock & Timing Considerations for Alter on Multi-Terabyte Table

Hi, I am new to TD. I have a query about locks. If user palced a lock on database to execute a batch of transactions and DBA by mistake releases the lock with override on that specific database. Will it roll back that batch of transactions or what will happen exactly and may it harm the business logic or can leave some inconsistencies etc. if there is some effect because of this which log table have its record? Thank You

Teradata Employee

Re: Lock & Timing Considerations for Alter on Multi-Terabyte Table

What makes you think the DBA has the ability to do that?