How do I create an index

Database
Enthusiast

How do I create an index

how do I create an index on a table I have created in Teradata? I have pushed the data from SAS using a proc copy to teradata. Now I need to add an index but do not know how.

thanks,

Joe
3 REPLIES
Enthusiast

Re: How do I create an index

Hi Joe,

Index can be created along with the Create table script.

For eg:

CREATE TABLE sample
( storeid INTEGER NOT NULL,
productid INTEGER NOT NULL,
totalorders INTEGER
) PRIMARY INDEX (storeid, productid)
PARTITION BY CASE_N ( totalorders < 10, totalorders < 100, NO CASE, UNKNOWN)
;

Regards,
Balamurugan
Enthusiast

Re: How do I create an index

When you say you have pushed the data to TD i assume it was moved to a TD table.

Every table would have a Primary index by default.(Either user defined or automatically assigned)

See if the PI column(s) is/are the one(s) on which you want to create an index.

If so its already there and you can use it.

If not then you will have to create a Secondary index on the columns you want with the following syntax.

-- For Unique SI
Create Unique Index

-- For Non Unique SI
Create Index

Regards,
Annal T
Enthusiast

Re: How do I create an index

For Unique SI

-- Create Unique Index IndexName (Col1,Col2,...Coln) on Tablename

For Non Unique SI

-- Create Index IndexName (Col1,Col2,...Coln) on Tablename

Regards,
Annal T