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.
Ayoka is a Made in USA enterprise application services company with one clear objective: delivering the best customer service to all of our clients. Ayoka’s commitment to Made in USA custom software development ensures that our client’s culture is understood, objectives are clearly communicated and allows us to provide tangible advice to our clients that are building custom enterprise applications that are essential to operating their modern business.
Ayoka’s custom software development culture combines the entrepreneurial feel of a start-up company with the confidence and stability of a successful professional services firm. Our vision is to become the ONLY choice for affordable enterprise software development and custom software development in AMERICA. We are proud of our consistent track record of delivering successful projects on time and on budget. We strive to provide custom software development projects that make our clients money. Get in touch today to see how Ayoka’s single loop services can be a huge benefit your company.
