lều của khối đệm được nối vào kết quả tập tin các tập tin trên đĩa có chứa các kết quả bất cứ khi nào tham gia nó được lấp đầy. Khối đệm là sau đó được tái sử dụng để giữ các bản ghi kết quả bổ sung. Trong vòng lặp lồng nhau tham gia, nó làm cho một sự khác biệt mà tập tin được chọn cho andwhich vòng ngoài cho vòng lặp bên trong. | 15 3 Algorithms for SELECT and JOIN Operations I 505 tents of this buffer block are appended to the result file the disk file that contains the join result whenever it is filled. This buffer block is then is reused to hold additional result records. In the nested-loop join it makes a difference which file is chosen for the outer loop and which for the inner loop. If EMPLOYEE is used for the outer loop each block of EMPLOYEE is read once and the entire DEPARTMENT file each of its blocks is read once for each time we read in nB 2 blocks of the EMPLOYEE file. We get the following . Total number of blocks accessed for outer file bE Number of times nB 2 blocks of outer file are loaded I bỵịin 2 1 Total number of blocks accessed for inner file bD rbg rig 2 1 Hence we get the following total number of block accesses bE pbg frig 2 1 bD 2000 r 2000 5 1 6000 block accesses On the other hand if we use the DEPARTMENT records in the outer loop by symmetry we get the following total number of block accesses bD i D nB 2 1 bE 10 io 5 1 2000 4010 block accesses The join algorithm uses a buffer to hold the joined records of the result file. Once the buffer is filled it is written to disk and If the result file of the join operation has bRES disk blocks each block is written once so an additional bRES block accesses should be added to the preceding formulas in order to estimate the total cost of the join operation. The same holds for the formulas developed later for other join algorithms. As this example shows it is advantageous to use the file with fewer blocks as the outer-loop file in the nested-loop join. Another factor that affects the performance of a join particularly the single-loop method J2 is the percentage of records in a file that will be joined with records in the other file. We call this the join selection factor11 of a file with respect to an equijoin condition with another file. This factor depends on the particular equijoin condition between the two files.
