Try OpenEdge Now
skip to main content
SQL Development
Optimizing Query Performance : Understanding optimization : Optimizer phases : Determining join order among adjacent join nodes
Determining join order among adjacent join nodes
After identifying a set of adjacent join nodes, the optimizer uses the available statistics to estimate the cardinality (the number of rows in the table or intermediate result) and selectivity (percentage of rows a predicate returns) for each subtree of the join nodes. It then uses the following criteria to determine the join order:
*The subtree with the lowest estimated cardinality is taken first. The SQL engine's cost manager estimates the cardinality of each subtree by multiplying table cardinality by the selectivity of the predicates applied to the table.
*The subtree that has the lowest estimated join cardinality (number of output rows produced by a join with the first subtree) is taken second. When determining join cardinality, the optimizer considers whether there is a join condition between the two subtrees. It gives preference to subtree pairs that have join conditions.
*The subtree with the next lowest estimated join cardinality is taken next, and so on.