UNION ALL - NO JOIN ELIMINATION - WHY?

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
MM
Enthusiast

UNION ALL - NO JOIN ELIMINATION - WHY?

Hi,
what is the reason for not joining into a union all?
For example:
A view containing a union all of 2 storesalestables (key of both tables a store_id).
This view is joined to a storelocationtable via the store_id.
The wherecondition is something like “where storelocationtable.village = 'Berndorf'“.
Instead of eliminating almost all records by joining the locationtable to both storesalestables, Teradata first spools the two storesalestables together (million of records) and then joins to the storelocationtable. Only a few records qualify for this join.
Yes, if you create foreign key constraints, Teradata does this join first.
But what is the reason that Teradata does not join into such a Union All without constraints?
Bye Martin
1 REPLY
Apprentice

Re: UNION ALL - NO JOIN ELIMINATION - WHY?

Hi Martin,

 

It looks like this is changed in TD16.00. The following is from the TD 16.00 Release Summary:

Pushing of aggregations and joins from the outer block into the UNION ALL branches, based on cost
and other factors.

 

Note that the above includes the phrase "based on cost and other factors".

 

On that point, do you have stats on the selection and join columns in your test. Are the estimated row counts accurate?

If they are not this won't help.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com