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
  • Database