All Forums Database
abort_session 6 posts Joined 07/14
15 Jul 2014
New in Release 13 ? Alter table not possible on columns with statistics

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!

dnoeth 4628 posts Joined 11/04
15 Jul 2014

It's not a bug, this is how it's implemented :-)
At least you don't have to re-collect the stats:

  • CREATE TABLE table_copy AS old_table WITH NO DATA
  • COLLECT STATS ON table_copy FROM old_table
  • DROP STATS old_table
  • do the ALTER
  • COLLECT STATS ON old_table FROM table_copy
  • DROP TABLE table_copy



abort_session 6 posts Joined 07/14
02 Sep 2014

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

 collect stats on old_table   from table_copy


ERROR: Source and target table definitions did not match


ToddAWalter 316 posts Joined 10/11
02 Sep 2014

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.

You must sign in to leave a comment.