Archives of the TeradataForum

Message Posted: Fri, 23 Jan 2004 @ 15:35:40 GMT

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

Unlike traditional indexes, join indexes do not store pointers to their associated base table rows. Instead, they are a fast path final access point that eliminates the need to access and join the base tables they represent. They substitute for rather than point to base table rows.

The Optimizer can choose to resolve a query using the index, rather than performing a join of two or more tables. Depending on the complexity of the join, this improves query performance considerably. To improve the performance of Join Index creation and Join Index maintenance during updates, consider collecting statistics on the base tables of a Join Index.

Join indexes are defined to reduce the number of rows processed in generating result sets from certain types of queries, especially joins. Like secondary indexes, users may not directly access join indexes. They are an option available to the optimizer in query planning. The following are properties of join indexes:

* Are used to replicate and "pre-join" information from several tables into a single structure.

* Are designed to cover queries, reducing or eliminating the need for access to the base table rows.

* Usually do not contain pointers to base table rows (unless user defined to do so).

* Are distributed based on the user choice of a Primary Index on the Join Index.

* Permit Secondary Indexes to be defined on the Join Index (except for Single Table Join Indexes), with either 'hash' or 'value' ordering.

Thanks & Regards,

Vivek.

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