Algorithms: Block Nested Loop Join
From wikinotes
A buffered variation of Algorithms: Nested Loop Join.
Documentation
video https://www.youtube.com/watch?v=JGgwD05gM2A
+(outer)-----+ +-(results)--------+
| batch-1 | | results-1 |
+------------+ +------------------+-
| batch-2 | +-(buffer)-------+ | results-2 |
+------------+ | outer-batch-1 | +------------------+
+----------------+- | ... |
| outer-batch-2 | | |
+(inner)-----+ +----------------+ | |
| batch-1 | | inner-batch-1 | | |
+------------+ +----------------+ | |
| batch-2 | | results-batch | | |
+------------+ +----------------+ +------------------+
Instead of looping through all outer/inner items, batches of outer/inner items are processed at a time.
A batch-size for rows is defined.
N batches from the outer-loop, and Y batches from the inner loop are written to a buffer.
A regular Algorithms: Nested Loop Join is performed.
Instead of writing directly to the disk for every row, writes wait for finish, or for the batch to be full of rows.
