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

Post a Comment

Popular posts from this blog

Top myths for NULL value in SQL Queries

Image
The below are some of the key points about nulls, and how to compare them. Just to make sure that you should know about what is NULL value. Interpretation is not exactly missing value. There could be many reasons why no value is present, e.g., value inappropriate. How to compare NULL to values In the below example the where condition just checks for TRUE condition. If not matches you will get UNKNOWN. Example: Table Name is: Sells bar beer price ======================= Joe's bar Bud NULL SELECT bar FROM Sells WHERE price < 2.00 OR price >= 2.00; ------------ ------------- UNKNOWN Conclusion Joe's Bar is not produced, even though the WHERE condition is correctly given. The reason is the price value is NULL. So, we should not compare NULL to values.
Read more