Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 29 Aug 2007 @ 16:03:23 GMT

  <Prev Next>   <<First <Prev

Subj:   Re: Adding new columns to an 11 billion row table
From:   Geoffrey Rommel

At the risk of repeating material from earlier posts, here are three approaches.

1. create new-table (with new columns, PPI, etc.);

     insert into new-table
      select * from old-table;
     .if errorcode <> 0 then .quit;
     rename old-table as old-table-backup;
     rename new-table as old-table;

Pro: probably the fastest approach; old table is unavailable for only a few seconds.

Con: requires a lot of disk space.

I suspect that you have already considered this approach and rejected it because you don't have a terabyte available.

2. Alter table to add the new columns.

Pro: probably fast enough if the table header doesn't have to be expanded; uses less disk space.

Con: could lock the table for quite a while.

3. FastExport the table.

Drop the table.

Create the new table.

FastLoad into the new table. (You can code constants for the new columns in the INSERT.)

Pro: uses minimal disk space within Teradata.

Con: requires disk space or tapes outside Teradata; unknown outage time.

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023