Archives of the TeradataForum

Message Posted: Fri, 07 Jul 2006 @ 14:15:28 GMT

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

<-- Anonymously Posted: Friday, July 07, 2006 09:32 -->

I have over 30 Join Indexes defined in my Datawarehouse to automagically support my dimensional models and BI reports.

I stay away from using Join Indexes that have more than one table because they take an excessive amount of time and resources to update and IMHO they are almost as bad as USI's (resource wise). If a JI mentions 3 tables and each table is updated nightly the JI may be impacted 3 times.

For each JI create a view that mirrors the JI definition sans the COUNT(*) column. The SQL mentions the view rather than the base table. Case closed for the optimizer.

It's a piece of cake to use Join Indexes, but stay away from pre-joins on large tables as they are not worth the price of admission; then again that's why we have dimensional models.

Anonym

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