Archives of the TeradataForum

Message Posted: Fri, 20 Apr 2012 @ 21:35:15 GMT

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

Hi Nathan.

This has been a topic of discussion off and on in our company for a long time. I think the rationale had originally been to have -1 or something similar rather than null in the FK in order to enable inner joins in queries rather than outer joins. But this is problematic. It assumes that we will add a -1 default row to the parent table, but if we have a lot of -1 default values in the FK then we end up with a hot AMP during an inner join. So if we make it unique by multiplying the numeric PI or PK by -1, then will we insert a placeholder record in the parent record? I'm not sure I see the value in that, because it will never have any real meaning updated to it and it is of no use in BI. So if we don't insert a placeholder row, then this approach is no different than using a null. Either way you have to do an outer join, else split any SELECT statement into two UNION parts:

     inner join where FK is not null UNION no join at all where FK is null.

A DBA and explored a hybrid solution a couple of years ago, where we would insert 1000 negative placeholders into the parent table (-1 thru -1000) and then for FK default values randomly assign one of those negative values, thereby avoiding the hot AMP situation on inner joins. He tested it and it worked well. At the time I discussed this with on-site TD consultants and they said there's no simple, easy alternative they can offer and that our solution sounded pretty good. We never really came to any conclusion about a particular design pattern as a standard. It seems to me that outer joins remain the most straightforward solution if we have more than a small % of rows missing real values.

I'm interested on others' experiences and solutions to this situation.

Attila Finta

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