What is better? Teradata ALTER TABLE or INSERT INTO…SELECT?

8
1074
Teradata ALTER TABLE

Teradata ALTER TABLEModifying an existing table is expensive and can consume a lot of resources  (especially IOs). In this article, 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.

I was creating a test scenario were the table’s data block size was 127 KB. This table contained short rows (each of them consuming only a few KB). Into each data block several hundreds of rows were packed.

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.

In order to get a more detailed picture, I was creating a second test scenario. While keeping the data block size at 127KB, I replaced the small rows with huge rows. With each row being about 64KB in size, only 1-2 rows were fitting into each data block.

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!

I tried several additional setups, leading to the same conclusion: ALTER TABLE is much cheaper. What I learned as well: Increasing the data block size in case of very large rows can drastically decrease IOs.

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.

Here are the summarized results:

Logical IO Physical IO
INSERT BIG ROW 270 853 12 992
ALTER BIG ROW 539 15
INSERT SMALL ROW 322 30
ALTER SMALL ROW 113 14

Another argument in favor of the ALTER TABLE approach is that no spool is required. INSERT INTO…SELECT needs to spool 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:

  1. ALTER TABLE can’t be aborted.You can’t stop it as soon as it’s started.This may lead to unexpected situations, as the following example shows: Let’s assume your workload management defines a rule which kills all sessions consuming more than 100.000 CPU seconds. What happens if your ALTER TABLE statement hits this limit?The workload management will of course send the signal to kill your session, but as no abort is possible the session will finish the ALTER TABLE activity and fail afterwards. Definitely not the workload management inventor’s intention!
  2. ALTER TABLE exclusively locks the table as long as the ALTER TABLE is active, nobody else can access the table. On the other hand, the INSERT INTO..SELECT approach only uses a read lock.
  3. ALTER TABLE takes place on the original table. This might be considered as a safety problem

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)

Our Reader Score
[Total: 6    Average: 4.8/5]
What is better? Teradata ALTER TABLE or INSERT INTO…SELECT? written by Roland Wenzlofsky on September 24, 2015 average rating 4.8/5 - 6 user ratings

8 COMMENTS

  1. everything said and done. I found the BEST approach is
    — 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. But you now have an online backuo
    –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
    Caveats
    — 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 – I don’t mind loaning space for brief time
    — Make sure it does not contend with Arch jobs in any way

    Is there anything else that I could have missed.

  2. Hi.

    Another thing to keep in mind is the GRANTs on the table. If you issue a CREATE TABLE + INSERT…SELECT + RENAME TABLE you must be sure that all the GRANTs on the original table are replicated to the new one.

    Cheers.

    Carlos.

    • everything said and done. I found the BEST approach is
      — 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. But you now have an online backuo
      –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
      Caveats
      — 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 – I don’t mind loaning space for brief time
      — Make sure it does not contend with Arch jobs in any way

      Is there anything else that I could have missed.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.