Why might PeakPerm spike during an ALTER TABLE... ADD operation?

Database

Why might PeakPerm spike during an ALTER TABLE... ADD operation?

Hi everyone -

 

I ran into a space issue in a data lab that got me wondering about both the practical and theoretical implications of this.  Basically, a well-distributed table gets generated and its CurrentPerm is roughly equal to its PeakPerm.  When I "ALTER TABLE mytbl ADD myvar VARCHAR(5)", PeakPerm spikes to around 2.4x its initial size, which caused the original space issue.  So...

 

1.  Why is this?  I would have guessed this ALTER to be a pretty trivial task that shouldn't require much disk overhead.

2.  Is there a way to structure things so that ALTER is more conservative?  I'd much rather alter than select into a new table in this particular case.

 

Some background:  The original case was a 30GB table with ~100m rows with skew=1 with a PI, but I've experimented with NoPI and other indexes, as well as tables of varying sizes and skews (eg., 50MB, 5GB) -- they all temporarily expand to about 2.4x their CurrentPerm size during the ALTER/ADD operation.

 

Many thanks for any insight!


Accepted Solutions
Teradata Employee

Re: Why might PeakPerm spike during an ALTER TABLE... ADD operation?

Sounds like your tables are block compressed. From my expereince, ALTER will check for amount of available space that is equivalent to uncompressed data. However, the table will remain compressed after the ALTER completes.

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: Why might PeakPerm spike during an ALTER TABLE... ADD operation?

Sounds like your tables are block compressed. From my expereince, ALTER will check for amount of available space that is equivalent to uncompressed data. However, the table will remain compressed after the ALTER completes.

Re: Why might PeakPerm spike during an ALTER TABLE... ADD operation?

Thank you very much!  That was exactly it.  If I insert the table uncompressed and then alter that, it doesn't expand Peak during the Alter.

 

Thanks again!