Archives of the TeradataForum

Message Posted: Tue, 25 Oct 2005 @ 17:01:59 GMT


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

Subj:		Re: Help on Join Index with Outer Joins, please.

From:		David Clough

Clay wrote (and I'm very much obliged for your comments, by the way):

1) Try creating a NUSI and collecting stats on the Division_Number and Account_Number fields in your JI. Also, for good measure, collect stats on those 2 JI columns individually.

Good idea - sadly, didn't work.

2) Try re-creating the JI without 'compression'...perhaps this is a bug specific to compressed JIs

Good thought - didn't work either.

3) Are the left outer joins necessary or could they (all or partly) be replaced with Inner joins and RI (to maintain flexibility)? In other words, is a one to many relationship between the tables guaranteed by the application? If not, then ignore this 3rd point.

Em, can't really do this one.

What I've inferred, Clay, from the Database Design manual, which states:

"There are several benefits in defining non-aggregate join indexes with outer joins.

Unmatched rows are preserved.

These rows allow the join index to satisfy queries with fewer join conditions than those used to generate the index.
..."

is that it's fine to use FEWER joins than defined in the Join Index but not MORE joins than in the Join index. If so, then I find it all a bit limiting, and I'm struggling to think conceptually why that should be such a restriction.

Anyway, I'll be attending the UK User Conference in November so I'll ask that question there.

For interest (snore), what I was trying to do was to materialize the joins between tables that are on different Amps and leave the co-located ones to be dynamic. Oh well.

_______________________
Dave Clough
Database Designer
Express ICS


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

Attachments

Library

Quick Reference