QUERY EXECUTION. 15.3 Nested-Loop Joins. By: Saloni Tamotia (215). Introduction to Nested-Loop Joins. Used for relations of any side. Not necessary that relation fits in main memory Uses “ One-and-a-half” pass method in which for each variation: One argument read just once.
FOR each tuple s in S DO
FOR each tuple r in R DO
IF r and s join to make tuple t THEN
To decrease the cost
FOR each chunk of M-1 blocks of SDO
FOR each block b of RDO
FOR each tuplet of bDO
find the tuples of S in memory that join with t
output the join of t with each of these tuples
This means that the neither relation fits in the entire main memory.
B(S) + [B(S)B(R)/(M-1)]
or approximately B(S)*B(R)/M