Hi,I'm new to teradata.I want to alter the PRIMARY INDEX of a table to UNIQUE PRIMARY INDEX. How to acheive this?Also I would like to know the full alter table syntax options of Teradata.
Hi,U can copy all the contents of a table into a new table and define unique primary index on the required column and later drop the old table(provided u have both create and drop table permissions)Syntax would be,CREATE TABLE NEW_TABLE AS( SELECT * FROM OLD_TABLE)UNIQUE PRIMARY INDEX(REQUIRED_COLUMN)WITH DATA;DROP TABLE OLD_TABLE;I am not very sure whether it is making any sense or not.You can find alter table syntax in Teradata documentation(SQL Reference:Data Defination statements)If u want, i can mail u a copy of that.
Here you have to consider few things.
You can always change the UNIQUE PRIMARY INDEX to PRIMARY INDEX no matter the table is populated or not using the following statement
ALTER TABLE Tabla_Name MODIFY NOT UNIQUE PRIMARY INDEX (Column_Name)
But if you want to change the PRIMARY INDEX to UNIQUE PRIMARY INDEX then table must be empty i.e. there should not be any row in the table. Use the following query to change the PI in case the table is empty
ALTER TABLE Tabla_Name MODIFY UNIQUE PRIMARY INDEX (Column_Name)
Following are the statements to change the NUP to UPI without loosing data
CREATE TABLE NEW_TABLE
UNIQUE PRIMARY INDEX(REQUIRED_COLUMN);
INSERT INTO New_Table_Name SEL * FROM Old_Table_Name;
Then Delete the old Table
Asad Ali Khan
There's no reason to copy to a new table.
Simply add a USI on the NUPI columns and then
ALTER TABLE xxx MODIFY UNIQUE PRIMARY INDEX (NUPI_cols)
This also drops the USI automatically.
I am intypical situation , there is table of 3 TB and the table is not partitation yet.
After looking at data in table, I came to conclussion partition on date is best fit.
but here is the problem:-
CREATE TABLE ORDERS
PRIMARY INDEX PIE(ENO)
CREAT_TS IS NOT DEFINED IN PRIMARY INDEX, upto my knowledge unless we have that field in PI , I can not create the partation.
I create a temp table
PRIMARY INDEX PIE(ORNUM,CREAT_TS)
PARTITION BY RANGE_N(CAST(CREAT_TS AS DATE) BETWEEN '2005-01-01' AND '2013-02-10' EACH INTERVAL '1' MONTH)
TRYING TO insert that data into temp table , but it is failing due to spool, so i decided to move year wise data like -CREAT_TS <= '2011-09-23 13:35:57.83' .
can some help , can i partition main table directly .. i tried with alter statments but it is not working ..
Please consider your primary index again.
Partitioning columns do not have to be columns in the primary index. If they aren't, then the primary index cannot be unique.
1) If you will be joining your order table with other table based on ornum then let the PI be ornum and let it be nupi, and partition it by creat_ts.
2) If the temp table and order table has the same PI then data of both tables will be on hte same AMP and it will not spool out.
I don't think there is any way to partiton the table without creating a new temp table.
Limitations with ALTER TABLE:
• Primary Index of a non-empty table may not be altered
• Partitioning of a non-empty table is generally limited to altering the “ends”.