Made in USA: Enterprise Application Services
The nested loop strategy may be effective for database optimization when no indexes exist on either table for the join column (in this case, DeptNum). The database developer has two options here. We can either choose to use EMPLOYEE or DEPARTMENT in the outer loop, and the choice we make is very important. In general, the database developer will want to choose the table with the smaller number of records for the outer loop. This can drastically reduce the number of loop iterations. The DBMS will make this determination automatically, but it is helpful to be aware of what’s going on behind the scenes when writing queries.
Using EMPLOYEE as the outer loop:
C=be+ (be*bd)= 10,000+(10,000*5)= 60,000 block accesses.
Using DEPARTMENT as the outer loop:
C=bd + (bd*be)= 5+(5*10,000)= 50,005 block accesses.
Using DEPARTMENT as the outer loop cut the number of block accesses down by about 10,000, which is a substantial improvement.