All Forums General
anagpure 8 posts Joined 07/12
28 Jul 2015
How to update a datatype of a column in a table having huge data

Hi,
we have one table in teradata having millions of records. Now we want to change a datatype of a column(having data in it). what is the best way to do this ?
while doing this which type of locks would be applicable to the table ?
Thanks in advance.
 

dnoeth 4628 posts Joined 11/04
28 Jul 2015

You should check first if it's allowed (see DDL manual) to change the datatype using Alter Table.
If it's actually allowed it might be possible without modification of the data (e.g. increase a VarChar size) and then it's really fast.
Otherwise do an insert/select in a new table plus drop/rename.

Dieter

anagpure 8 posts Joined 07/12
28 Jul 2015

Hi Dieter,
Thanks for your quick reply.
However,
In case it is a critical production table and we can not stop application running concerned with it then what is the quick solution we can perform.
is it possible without production downtime ? as it involves a BIG risk
Thanks
 
 

dnoeth 4628 posts Joined 11/04
28 Jul 2015

How big is the table, how many rows, any additional indexes?
What's the old and the new datatype?

Dieter

anagpure 8 posts Joined 07/12
29 Jul 2015

Hi Dieter,
The table is having 40 million records and we want to change the datatype of ramount column from DECIMAL(12,2) to DECIMAL(10,2)
the data present in the amount column is of length 10 digits including decimal. No additional indexex present.
Thanks.

dnoeth 4628 posts Joined 11/04
29 Jul 2015

You can't decrease the precision of a column using ALTER TABLE.
But why do you want to do that?
Both DEC(12,2) and DEC(10,2) need the same space and you could simply add a CHECK(ramount BETWEEN -99999999.99 AND 99999999.99)
This will require a single Full Table Scan, which should be quite fast on 40 million rows.

Dieter

LUCAS 56 posts Joined 06/09
29 Jul 2015

Hi Dieter,
About changing datatype from now when creating new tables for an application existing column (ID_COL):
i just wonder wether a use of INTEGER instead of BIGINT could impact join performances between historical tables and new tables ?
HIST_ID_COL (BIGINT) JOIN NEW_ID_COL (INTEGER)
Well, except the fact a same column with two datatypes is not suitable in a physical model ...
NB: BIGINT is a waste of space when ID value is never above 110 000 000.
Pierre

LUCAS 56 posts Joined 06/09
29 Jul 2015

Sorry, i copy this question in DATABASE forum !
Pierre

dnoeth 4628 posts Joined 11/04
29 Jul 2015

Hi Pierre,
as INT and BIGINT hash the same the join should be exactly the same.

same column with two datatypes is not suitable in a physical model

Even more in a logical model :-)

Dieter

LUCAS 56 posts Joined 06/09
29 Jul 2015

Thanks Dieter,
you are right,
an ultimate question (on this forum): is unuseful BIGINT a waste of space in spool too (8 bytes occupied instead of 4 for each value) ?
Pierre

dnoeth 4628 posts Joined 11/04
29 Jul 2015

Hi Pierre, 
of course it's wasting spool, too.
But it could be even worse, DEC(38,0) :-)

Dieter

Abhishektd 7 posts Joined 06/11
30 Jul 2015

Hi Aakash,
As you mentined that you have to update the data of a column of a production table, the best and safest way would be to create another table as per your requirement and load data into the new table. 
There must be a view built over the existing live table, that view ddl can be changed to to point to the modified table (you will be required to change the table name in all ETL load jobs also) 
or
simply archive the old table and then drop it. Then rename the new table with modified DDL as old one. 
You can identify the time to carry out this change based on DBQL analysis for past couple of months when none of the jobs access that table to minimise the impact of downtime.

Thanks & regards,
Abhi

You must sign in to leave a comment.