Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 25 Jul 2005 @ 17:23:38 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: ALTER TABLE of a 400 million row table
 
From:   Diehl, Robert

Bill,

This is hard to explain.

Alter tables can be very fast even on large tables if the right conditions are present. Basically they will be almost instantaneous if the data blocks don't have to be changed.

Here are the conditions that must be met to not alter the blocks.

1) add a varchar column withour a default value

or

add a fixed length column with compression

2) must have extra indicator bits for what is needed. 1 for not null and one or more for compression depending on values compressed.

If column is not null must add default phrase.

The hard part here is knowing if indicator bits are available, you will have to add them up and see if another byte is needed as the bits come 8 at a time. I don't know if EXPLAIN plans change based on indicator bits needed.

An alter table that does not need to alter the data blocks should run in under a minute depending on DBC table activity at the same time.

Some examples:

     Alter table dbase.tablenm
     Add Col1 varchar(30) ;


     Alter table dbaase.tablenam
     Add col1 char(2) compress;

     Alter table dbase.tblanme
     Add col1 char(2) not null default 'TM' compress 'TM';

If your alter table cannot meet these conditions, I would create a new table and load from the old one.


Thanks,

Bob Diehl
Travelcoity.com



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