Estimating the cost of join operations is a little more complicated than doing so for SELECT operations because we have to consider the type of indexing available for each table that participates in the join. We must also factor in the cost of writing the result file, but since the size of the result file remains constant despite altering the execution strategy, we can ignore it for the basis of comparison.

Assume that in addition to the EMPLOYEE table, we also have a DEPT table with a DeptNum field indicating the department’s number.

Other assumptions:

Field Value
Number of EMPLOYEE records (re) 100,000
Number of EMPLOYEE disk blocks (be) 10,000
Blocking factor (bfr) (records per block) 5
Number of DEPARTMENT records (rd) 25
Number of DEPARTMENT disk blocks (bd) 5

The query we wish to evaluate is:
SELECT * FROM EMPLOYEE AS E, DEPARTMENT AS D WHERE E.DeptNum = D.DeptNum;

We will compare the following execution strategies for this query:

  1. Nested-loop join (brute force)
  2. Single loop join (using an index)
  3. Sort-merge join

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 services can benefit your company.