Pushing restrict operations close to the data origin
This stage consists of moving restrict operators as far down the query tree as possible. This reduces the number of tuples moving up the tree for further processing and minimizes the amount of data handled. When restrict operations on a join node cannot be moved below the join node, they are set as join conditions. When multiple predicates are moved down the tree to the same relative position, they are reassembled into a single restrict operation, as shown in the following example.
SELECT Name FROM Employee
WHERE Salary > 4000 AND Salary <= 6000
AND Employee.DeptNum = Department.DeptNum;
The optimizer takes the input tree and transforms it as shown below. The restrictions Salary > 4000 and Salary <= 6000 are moved down the tree, below the join node, since they apply to a single table. The restriction Employee.DeptNum = Department.DeptNum, applying to two tables, stays above the join node.
