The Block Join mode of JOIN query execution is designed to reduce the number of queries done on inner tables to build the joined results. This mode also allows querying of small inner tables only once.
Note: Block Join mode does not apply if you are doing join pushdown.
Consider this query:
SELECT ... FROM dept, emp WHERE emp.deptno = dept.deptno
In the existing join implementation, the dept table is first queried. For each record of dept, the matching emp table records are queried. With block joins, the emp table is queried to get matching records for a block or group of dept records. This mode is ideal for use with backends that have a large cost that is associated with accessing a single record that is based on a column value. An example is a system that uses a network protocol to request data from a server. The cost of each request over the network can be more expensive than getting many records back in a single request.
