All Forums Database
26 Jun 2006
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)

dnoeth 4628 posts Joined 11/04
28 Jun 2006

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

Dieter

29 Jun 2006

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

Phani 15 posts Joined 07/04
29 Jun 2006

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.

You must sign in to leave a comment.