In Release 13, i can't alter a Column in a table if the column has statistics.
This is a big problem. So we had to write a complex SAS Macro to do this:
1. save the statistics in SAS (also multicolumn statistics !)
2. drop statistics for the column and all multicolumn statistics
3. alter the table for example to do a compress statement
4. make new statistics for the column
This must be a bug!
It's not a bug, this is how it's implemented :-)
At least you don't have to re-collect the stats:
We have a production crash after installion this workaround above.
We have tested positive with an alter statement with compress, but not with adding
a new column to a table.
We can't save the statistics back if the table has a new column:
alter table old_table add new_column
Not clear what you mean by a production crash. If you had a failure of the Teradata database then it should be reported to the Teradata Global Support Center so that it can be found and repaired.
It should be possible to ALTER TABLE adding a new column under the new rules. What is not allowed currently is to ALTER the definition of an existing column since that could change the stats collected on it. The rules for what can be accepted are being re-reviewed for a future release but for now it is not possible to change an existing column.
When changing a column or dropping a column, it should be possible to do so by just removing the stats on the directly affected column and any multi-column stats that span that column in the logical column order in the table. You should not have to remove all the stats.
It is true that copying stats from another table as a complete set is not allowed for tables with different definitions because again it may affect the integrity of the stats. However, there is a mechanism for restoring the stats without having to re-collect them from scratch:
Export the stats (very easy now with new SHOW STATS VALUES command) and import them back after the ALTER. These commands can be scripted and the export and import is a sub second operation. During the import the database adjusts the column descriptors as required and makes them in sync with the column definitions. Also, these operations retain the original collection times, history records and other options such as MAXVALUELENGTH, MAXINTERVALS, etc. Nothing gets lost. So, no impact to autostats continuity.