Archives of the TeradataForum

Message Posted: Fri, 06 Nov 2015 @ 13:03:00 GMT

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

Hi,

Firstly, my apologies for the delayed response, I've been onsite with a customer.

As other people have pointed out, if your query does not include all columns of the USI in the WHERE clause (using equality constraints and all constraints are AND'd together) then the USI cannot be used with direct lookup'. So your understanding is correct. However (again as other people have noted) the optimiser may well do an index scan which is likely to be much faster than a table scan.

Using your example from below, if the USI columns are fname + lname and your query only includes fname in the WHERE clause then you are correct. The optimiser cannot know which AMP holds the index entry. Therefore, this must generate an all-AMP query, but very possibly an all-AMP ** index ** scan and not an all-AMP ** table ** scan.

So the USI can cover a query.

We talked before about using the ORDER BY clause to sort index entries by data value rather than hash of all columns. Note that this option only applies to NUSI's and not to USI's.

Cheers,

Dave

Ward Analytics Ltd - Information in motion (www.ward-analytics.com)

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