Made in USA: Enterprise Application Services

Single Loop (using an index)

Single Loop (using an index)

If a primary index exists on one of the tables for the join column, then the DBMS can use this index and perform the join with a single loop. So for instance, if a hash-type index exists for the DeptNum column for the DEPARTMENT table, the database developer can hash it rather than perform searching, so an inner loop is unnecessary. Still, the choice of which table we want to loop with is important. Assume that an index exists for DeptNum for both the EMPLOYEE and DEPARTMENT tables.

Note: h is the number of block accesses required to retrieve a record given its hash value.

Using EMPLOYEE as the outer loop:
C = be+ (|DEPARTMENT|*h) = 10,000+25(1) = 10,025 block accesses.

Using DEPARTMENT as the outer loop:
C = bd+ (|EMPLOYEE|*h) = 5+100,000(1) = 100,005 block accesses.