Altering a Table Teradata

The only thing in life we can consistently count on is change. This is especially true in a data warehouse environment. As business requirements change, sometimes it is necessary to reflect those changes into the tables. Teradata allows for modification of a table at either the table or column level using the ALTER command.

Here is a list of the table changes available to the ALTER TABLE:

  • Add one or more new columns to an existing table.
  • Add new attributes for one or more columns in a table.
  • Drop one or more new columns to an existing table.
  • Modify constraints on an existing table at the column or table level.
  • Add or remove FALLBACK or JOURNALING
  • Modify the DATABLOCKSIZE or FREESPACE PERCENT
  • Change the name of a column in an existing table

You can ALTER a TABLE at both the column and table level. A table level change includes protection features or internal storage options such as FALLBACK, JOURNALING, FREESPACE PERCENT, etc. The column level allows you to change individual column attributes. For example you might be adding the TITLE, FORMAT, or another column level change.

The syntax for the ALTER statement is:

The following ALTER modifies the table to FALLBACK at the table level:
ALTER TABLE TomC.Employee, FALLBACK;

The Employee table in the TomC database now has a FALLBACK copy of each row. You can also remove FALLBACK, which is quick and easy. The system merely places the FALLBACK blocks of a table on the Free Cylinder List and the blocks are gone. This happens immediately. However, adding FALLBACK to a table is another story. This change may take a lot of time, depending on the number of rows in a table. When you add FALLBACK the system duplicates each row and places that row on another AMP in the same cluster. You are essentially doing a Full Table Scan (FTS) and a copy. If you have a million rows in the base table you are creating and distributing a million FALLBACK rows. The good news is that Teradata does this in parallel!

The following ALTER makes journaling changes at the table level:
ALTER TABLE TomC.Employee, NO BEFORE JOURNAL, DUAL AFTER JOURNAL;

The Employee table no longer has a BEFORE JOUNAL and now has a DUAL AFTER JOURNAL.

The following ALTER adds a TITLE of ‘School’ to one of the existing columns:
ALTER TABLE TomC.School
ADD School_Name TITLE 'School';

Although the above appears to be adding a column, the School_Name column already exists. When we explained Teradata to Sherlock Holmes he found another clue. Sherlock said, "Since the School_Name did not contain a data type, it could not possibly be adding a new column, but could only be altering an existing column." Sherlock was impressive. I went on to ask him what type of school he thought this table held. He said, "Elementary my dear".

You can make multiple changes to a table with one ALTER statement. The next request makes multiple changes to a table by adding multiple columns.

This example adds FALLBACK and two new columns (Soc_Sec and Dept_Name) to the table Employee:

ALTER TABLE TomC.Employee, FALL BACKADD Soc_Sec INTEGER,ADD Dept_Name Char(20);

Notice in the above example that when adding new columns you must specify a data type.

The next request makes three changes to the table. The first is a journaling change at the table level. The second is at the column level and it drops the column called Soc_Sec. The third change is also at the column level. It adds a TITLE to the column called Dept_Name:

The next request changes the name of a column. The old column name was lname and the new column name is Last_Name.

ALTER TABLE TomC.Employee
Rename lname to Last_Name;

There are some restrictions when renaming columns, they are:

  • A new column name cannot match any existing column name in the same table
  • The affected column cannot be a part of an index
  • The affected column cannot be part of a referential integrity constraint
  • The affected column cannot be referenced in the UPDATE OF clause of a trigger


Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics