Home Page for the TeradataForum

Archives of the TeradataForum

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

  <Prev Next>   <<First <Prev

Subj:   Re: NUSI Usage with LIKE
From:   Thomas F. Stanek


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.


Thomas F. Stanek
TFS Consulting

  <Prev Next>   <<First <Prev
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020