Archives of the TeradataForum

Message Posted: Fri, 26 May 2000 @ 11:57:16 GMT

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

A Primary Index is more important for ACCESS than distribution, in some cases. Distribution can be forgiving even when you have, say 24 months, of synonyms. VAMP Local operations for Joins can be very lucrative, so several tables can be designed with the same PI, even if the demographics are not exactly the same or the distribution somewhat lumpy (I mean SOMEWHAT, not VERY).

Some confuse Index with Key, and this can be a devastating mistake. Keys are Logical concepts - Indices are Physical.

Implementing Unique Primary Indices purely because it's the Key of the table may give you GREAT distribution but LOUSY query performance. Do this ONLY if you MUST enforce uniqueness (generally not required in a Decision Support Data Warehouse), because of the problems associated with Unique Secondary Indices and MultiLoad.

Also, as Brian Marshall articulates much better than I in his book on Teradata Performance, multiple single column indices sometimes give significant benefit over multi-column indices - especially if you don't have all the columns in your query, since it won't use it at all - because of the dynamic bit map index generation (called BMSM in the EXPLAIN).

Hope you find this useful.

Sam Sterling
www.ssterling.com

Admin Comment: When last tried, the URL in this Post no longer functioned.

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