Archives of the TeradataForum

Message Posted: Sun, 13 Aug 2001 @ 16:23:48 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Margus,

I have not done much analysis on NUSI's with a LIKE clause. However, I'll share with you a technique that we used to deliver much better performance. It's not perfect by any means, but it was very effective in forcing a NUSI look up and avoid a FTS.

On the PRODUCT table, create an additional column called Partial Name. Populate this column with the first 3 positions of the full product name. Create a NUSI on this new column and reference it in your queries. For example, instead of the following:

Where ProductName like 'Printer%'

The query would would include:

Where ProductName like 'Printer%'
and PartialName eq 'Pri'

Of course, this requires the user to provide the first 3 positions of the product name. But if that is acceptable, the equality on PartialName should initiate a NUSI lookup, with the LIKE condition being applied after the PRODUCT rows have been retrieved.

Hope this helps.

Regards,

Thomas F. Stanek
TFS Consulting

	<Prev	Next>		<<First	<Prev	Next>	Last>>