Can I make an Index with a Soundex?

Database
Enthusiast

Can I make an Index with a Soundex?

Hello,

I would like to know if there is a way to make a table index where one of the columns implied is a Soundex of a column:

  • CREATE INDEX  (COD_PROVIN, SOUNDEX(ENTIDADRETOC)) ON TRAMOS_BASE;

In Oracle you can do that but in Teradata it gives me back the following error:

CREATE INDEX Failed.  [3707] Syntax error, expected something like a name or a Unicode delimited identifier or a 'ROWID' keyword between ',' and the 'SOUNDEX' keyword.

Thanks and regards

Juan
3 REPLIES
Teradata Employee

Re: Can I make an Index with a Soundex?

The columns in an index have to be columns in that table, not derived columns.  You could conceivably add another column containing the soundex value and build the index on that.  You could also create a trigger on the table so that the soundex value column is automatically populated on inserts and updates, thus basically yielding the same functionality (and overhead!) as that Oracle index.

Junior Contributor

Re: Can I make an Index with a Soundex?

You can't create a NUSI, but a Join Index like this should work:

CREATE JOIN INDEX jindx AS
SELECT COD_PROVIN, SOUNDEX(ENTIDADRETOC)) as col
FROM TRAMOS_BASE;

 

Junior Supporter

Re: Can I make an Index with a Soundex?

Hi.

 

Your best bet is a global index:

 

CREATE JOIN INDEX TRAMOS_BASE_IDX AS
SELECT COD_PROVIN, SOUNDEX(ENTIDADRETOC) SNDEX_ENTIDADRETOC, ROWID FROM TRAMOS BASE
PRIMARY INDEX (COD_PROVIN, SNDEX_ENTIDADRETOC);

This way you can have an indexed access (via rowid) to the base table rows when necessary (i.e.: columns not in the JI needed).

Keep in mind that SOUNDEX() only allows ASCII chars (no spaces, no 'ñ's, etc...)

HTH.

Cheers.

Carlos