Joins

Database
Enthusiast

Joins

As we know the that "Explain" plan tells that which join plan the optimizer is choosing, but when writing the query can we sure that optimizer may choose which join plan(say ex: Merge, Nested, etc)
3 REPLIES
Junior Contributor

Re: Joins

There are no hints in Teradata, so you can't force the optimizer to choose a special join type.

Dieter
Enthusiast

Re: Joins

Dieter Thanks for reply but, Is it sure for Nested join, or can't say anything about it too.

Thanks in advance
Enthusiast

Re: Joins

The joins are picked up based on the costing. The whole plan is built by costing different paths. If you have enough stats for a NUSI-UPI join you may expect Nested Join. Similarly, if the demographics are upto date, you may expect merge join for equality conditions (if one of the columns is a primary index). In this case, the rows are already distibuted in hash order. In this case, typically you might expect a merge join.

As posted earlier, you can not give any hints to force a specific plan.