New in Release 13 ? Alter table not possible on columns with statistics

Database
Enthusiast

New in Release 13 ? Alter table not possible on columns with statistics

Hello,

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!

3 REPLIES
Senior Apprentice

Re: New in Release 13 ? Alter table not possible on columns with statistics

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
Enthusiast

Re: New in Release 13 ? Alter table not possible on columns with statistics

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

Teradata Employee

Re: New in Release 13 ? Alter table not possible on columns with statistics

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.