Changing data type of Primary Index

[Ravenium]

Hello all,

We recently started development on a Teradata server, and have run into problems with making changes to the schema. We would like to change the data type of a field from DATE to TIMESTAMP(0), but alas it is a primary index column, and we are not allowed to drop it or the table (dependents). I was hoping I could issue an ALTER COLUMN command, but it doesn't appear as though this command is supported (according to Queryman's reference).

Does anyone have any idea how we can change this one field without having to kill the entire database and reload the schema from scratch? There is no data in the table currently, but there are some 100+ tables...

Thanks,

-Barry

 

[seidub]

ALTER table does not allow you to change the datatype of a column. Even if it did, I doubt that it will allow you to change the data type of a PI column.
--------
Your best bet is to
(1) "show table" for all tables
(2) Modify the datatype of the columns you want to change
(3) Save the "show table" output
(4)Drop all tables (since you don't have any data)
(5) ran the output in (3) to re-create your "new" tables

The ff may help to create a "show table" for all the tables
in your database:

-----------------------------------------------
.TITLEDASHES OFF
.export file=output_filename
select 'show table '||trim(tablename)||';' (title ' ')
from
dbc.tables
where
databasename='DATABASENAME'
and
tablekind='t'
order by tablename
;
.export reset
------------------------------------------------------
good luck

 

[tdatgod]

Hi,
Teradata HASHES all the rows to their AMP (Disk location) based upon the Prime Index chosen. A Date field is only 6 bytes and a timestamp(0) is 10 bytes.

Therefore the rows may need to be physically relocated to different AMPS (Disk Location) and reordered if you change the datatype.

( "May" because many HASH values are shared by the same AMP and therefore just because the HASH changes it doesn't necessarily move to a different AMP.)


Now depending on how BIG this table is you can do this conversion in place provided you don't mind reapplying all you INDEXES, Foriegn Key designations, and recollecting statistics.

Also this requires twice as much database diskspace as the Current table occupies because it makes a duplicate copy of the table.

You can get the current diskspace of the table from Queryman I think. I know you can get it easily from WINDDI.


This maybe a better alternative then reloading the table from scratch.



Create a new table "newtable" identical to the old table "oldtable" except for the data type of the Prime index.

Then you issue an insert select

Insert into newtable ( f1, f2, f3 .... )
select CAST (f1 as Timestamp(0)), f2,f3 ....
from oldtable;


Please use the correct columns and table names based upon your tables.

This will build the new table by sending all the rows from the oldtable to the newtable on the correct AMPS.

This is very LAN and DISK intensive operation and depending on the number of the rows in the table may take a while.

Unfortunately there is no way to monitor the progress of this operation. If the box crashes while this is going on, it will automatically restart ( unfortunately from scratch ) when box comes back up.


When it is done you would then simply issue....

rename oldtable as obsoleteoldtable;
rename newtable as oldtable;

then you will want to reapply all your indexes, collect all your statistics, and what not.

Once you are comfortable with this you can then

drop table obsoleteoldtable;

( just make sure you DROP the correct one ).


now while this operation is going on the base table will be available for READING but will not be available for UPDATE, INSERT or DELETE.

However once it is done people could UPDATE, INSERT or DELETE rows from the basetable and therefore invalidate the new copy of the table because the updates, inserts or deletes will not be reflected in the new table, so care needs to be tken to make sure that doesn't happen.


If you want to see how many rows are on each AMP you an execute....

sel hashamp(hashbucket(hashrow(primekey))),count(*)
from table
order by 1 group by 1;


If we have a good hash and you don't have many duplicates this should be equivlenet to

sel count(*) from table ;

divided by the number of AMPS you have on the system.


If Teradata would allow a user to change the PI of a table this is basically how we would have to implement it internally.

Hope this helps. Let me know if you need any further information.

 

[tdatgod]

Hi,
I am new at this response thing. I need to make sure I read the whole post before replying.

Since you don't have any data please follow seidub's suggestions about doing the show tables for all your tables and then rexecute the output.

There is a 32K limit on the SHOW TABLE output and therefore if the CREATE TABLE is > 32K the full output will not be displayed and therefore you won't be able to re-execute it.

Hopefully you won't run into that issue.