Secondary Index questions

Database
Enthusiast

Secondary Index questions

In my sql server code, I create an secondary index for two columns. But I was wondering would be the syntax in Teradata Studio. For example, here a sql server code I have now:

 

create index [ndxID1]

on TEMP (ID1)

on [secondary]

 

create index [ndxID2]

on TEMP (ID2)

on [secondary]

 

 

 

4 REPLIES
Senior Apprentice

Re: Secondary Index questions

In Teradata the syntax is 

CREATE [UNIQUE] INDEX index_name (column_name(s)) ON table_name;

which results in 

CREATE INDEX ndxID1 (ID1) ON TEMP;

Don't forget to Collect Statistics and then check Explain if the index is actually used.

Enthusiast

Re: Secondary Index questions

And check if the index is really required! Teradata is creepy fast with full table scans. And a lot of queries run via de primary index. In general we do not have many indexes on our tables...

Enthusiast

Re: Secondary Index questions

Thanks for the quick replied back. I ran the secondary quiey code and it ran fine yesterday. But, when I tried to run this secondary index again today, I get the following error message:

Failed [5321 : HY000] Constraint, primary index, or secondary index with the same name 'ndxID1' already exists in table.

 

Anyway to resolve this issue?

Enthusiast

Re: Secondary Index questions

Nevermind, I revised my code and got it to work now.