How to Optimize outer JOIN


  • With the outer join, some table reordering is possible and recommended for efficiency. 
  • Normally, hierarchical structures are built top-down, but when subviews are used, right-sided nesting can cause the structure to be built bottom-up. 
  • Top-down execution is more efficient than bottom-up execution because bottom-up execution can cause throwaways. Throwaways are rows that are retrieved into the working set and then later discarded.


SQL Expanded (Bottom-Up):
SELECT* FROM Department LEFT JOIN
        Employee LEFT JOIN Dependent
          ON EmpNo=DpndEmpNo
           ON DeptNo=EmpDeptNo

  SQL Rewritten (Top-Down):
SELECT * FROM Department
LEFT JOIN Employee ON DeptNo=EmpDeptNo
LEFT JOIN Dependent ON EmpNo=DpndEmpNo

Comments

Popular posts from this blog

Top myths for NULL value in SQL Queries