Archives of the TeradataForum

Message Posted: Mon, 02 Nov 2015 @ 19:55:31 GMT

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

<-- Anonymously Posted: Monday, November 02, 2015 15:37 -->

Hi Dave,

Thanks once again for your response.

If my "rationale" is believed to be correct regarding the technique involved in the technique employed by USI sub-tables, how could the USI be used even in the example of covering indexes. Where I am failing to understand is - if for a USI, the hash-value is generated by TD of the "combined values" of fname+lname and the same is used to determine which AMP sub-table will hold this entry, then logically speaking how can the Parser understand which AMP sub-table to look into when only a part of the Index column-set i.e. fname = 'John' is specified in the query. Because, hash-value of ('John' + 'Smith') is different from the hash-value of 'John'.

For e.g. say during data insert, the hash-value generated for (fname = 'John' + lname = 'Smith') = 5. TD counts the hash-buckets and determined that USI sub-table in AMP2 should hold this entry. Now, in the query specifying only a part of the key i.e fname = 'John' will generate a different hash-value = 8. My question remains, since 5 <> 8 how does TD determine which AMP's sub-table to look into (in case of a USI) ?

However, the fact that the concept of covering index absolutely exists today, there must be a gap in my understanding of how TD maintains USI sub-tables. (unless covering index applies only with NUSI and that NUSI subtables holds the hash-values of every column of the multi-column SI instead of their combined hash-values)

I appreciate your help. My apologies for being persistent.

Thanks

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