Archives of the TeradataForum

Message Posted: Tue, 11 Oct 2005 @ 17:00:08 GMT


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

Subj:		Re: Redestributing large tables instead of replicating a small table

From:		David Wellman

Howard Bradley wrote:

So, my original question still stands, can the optimiser detect this sort of query and mimic this processing and if so in which version does it appear and how do I take advantage of it ?

Hi all,

This doesn't answer Howard's question below (I don't know if the optimiser does what he wants or what version this may be in), but....

Something I've done before for this situation, and Yes, it is a work-around rather than a real solution...

In this situation, the small table typically has a PI of the code column. In the past I have changed the PI to be something else, such as the Description column. Now the optimiser is faced with a small table, large table join and neither table being joined on it's PI. In this case the optimiser "does the decent thing" and moves the small table prior to the join.

The beauty of this approach is that you don't have to muck around with the SQL, you can test each approach against the other and when/if the optimiser does incorporate the necessary code you only have to change the PI back and everything still works as we'd want it to.

Cheers,

Dave Wellman

Ward Analytics Ltd: Information in motion ( www.ward-analytics.com )


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

Attachments

Library

Quick Reference