Archives of the TeradataForum
Message Posted: Fri, 12 Dec 2008 @ 10:43:12 GMT
| Subj: || || Re: How to avoid Product join |
| From: || || Howard Bradley |
The OP didn't give any detail about the nature of their actual query, but if there is significant aggregation of the large table involved I
find it far quicker to do the aggregation first (usually using a derived query) and then join to the smaller look up table to add referential
I read many years ago that one day the optimiser would be smart enough to recognise these opportunities and would generate a plan involving
"early aggregation" automatically, without me having to specifically code this approach (i.e the simple join between 2 tables may, if appropriate,
invoke early aggregation of the large fact table before performing the join). I think this early aggregation has also been referred to as "partial
group by". Unfortunately, I have never actually seen this working as it always seems to be in the "next release" no matter which version we
upgrade to :-) I also seem to remember that there is a DBS control setting to enable this feature, but we were advised at some point in the
distant past to disable the feature due to "technical issues with the release", so it wouldn't surprise me if we have had this for ages and just
haven't turned it on.
Can anyone tell me which version of Teradata has a stable implementation of this feature and if possible could someone post an explain plan for
a simple query to demonstrate it.