I understand the ALTER TABLE statement can add a column, but I don't understand how to populate it with data without overwriting the table. How do you fill the column with data, especially when that data needs to ID match on the record level?
The ALTER TABLE statement itself will probably not do what you need. The only data value this statement can put into a new column is a 'default' value, either NULL or the one specified in the ALTER TABLE statement itself.
I think what you'll need is to either:
- use ALTER TABLE to add a default value and then use UPDATE to set the correct value for each row, OR
- create a new table, insert/select from old to new and populate the new column as part of the SELECT.
And creating a new table and then insert-selecting (or merging) into it is usually the fastest method. (Then drop the old table & rename the new one.)
— Clone Newtable with data and statistics
– copy stats from old to new table Both these even for a TB table are not bad in terms of CPU.
– Alter table on the original – Conditional BTEQ if the above ran well drop the clone
— That way you do not have to deal with reassigning security again on that table.
— Original table will not be available till DDL is done But MOST TB tables are accessed through views
– re-point to the clone till work is done.
— If the clone was updated then the same update needs to synch in the Altered table. BUT really for a a huge fact table mostly used for OLAP that is a unlikely scenario but still the clone will confirm this
— Perm Space will momentarily shoot up. Understanding that this is a temp operation.
Please find the diffrence between ALTER TABLE or INSERT INTO…SELECT
Modifying an existing table is expensive and can consume a lot of resources (especially IOs).
we will discuss the advantages and disadvantages of the two available primary strategies: ALTER TABLE and INSERT INTO…SELECT into a copy of the table.
Both strategies have their individual applications and usefulness.
The ALTER TABLE approach required 133 logical IOs, the INSERT INTO…SELECT approach logical 322 IOs.
The ALTER TABLE was the clear winner with 59% less logical IOs.
Physical IOs showed a similar picture: While the ALTER TABLE only caused 14 physical IOs, the INSERT INTO…SELECT needed 30 IOs.
Interestingly, CPU usage was almost identical.
The result was impressive:
The ALTER TABLE approach required 539 logical IOs, the INSERT INTO…SELECT method 270 853 logical IOs. The ALTER TABLE needed 99.8% less logical IOs than the INSERT INTO…SELECT!
The difference between physical IOs was as well impressive: ALTER TABLE needed 15 physical IOs, the INSERT INTO… SELECT 12 992!
If the table has indexes defined (USI, NUSI, Join Index, Hash Index), the difference between the two strategies becomes even bigger, as ALTER TABLE don’t have to create indexes for the copy of the table.
And you are no doubt wondering why you should not always use the ALTER TABLE approach after seeing these results. The answer is: ALTER TABLE comes with some limitation:
Teradata ALTER TABLE or INSERT INTO…SELECT
Most of the ALTER TABLE’s disadvantages are related to data accessibility during the operation and in case of an error.
If you can live with these restrictions, you definitely should implement the ALTER TABLE approach.
It requires less IOs, doesn’t need spool and it’s easier to use (it’s just one statement; for example, you don’t have to create a copy of the table, run the insert into…select, recreate indexes and statistics)