When executing a JOIN query, the OpenAccess SDK SQL engine checks whether the IP supports IP_SUPPORT_BLOCK_JOIN, and verifies if the join query can be processed in block join mode. See Disabling block joins.
Note: The rules that the OpenAccess SDK SQL engine uses for deciding the order in which to process the tables remain the same even for block join query execution.
The following query is used to explain the block join execution.
SELECT ... FROM t1, t2, t3 WHERE t1.c1 = t2.c1 AND t2.c2 = t3.c2
Block Join execution sequence:
1. Processing starts by calling EXECUTE for records of the outermost table (t1). If the IP is cursor-based and returns FETCHBLOCK_SIZE of records, the OpenAccess SDK SQL engine proceeds to the next step of querying the inner tables. If the IP is not cursor-based and returns all matching records of t1, then these records are saved in memory and the OpenAccess SDK SQL engine proceeds to the next step of querying the inner tables.
2. The OpenAccess SDK SQL engine uses a Block of outer table records (based on the setting of JOINBLOCK_SIZE), sort the records on the columns used in the join conditions, and remove any duplicates. For example, records are sorted on the c1 column. If there are multiple join conditions (t1.c1 = t2.c1 AND t1.c3 = t2.c3,) the c1 and c2 columns are sorted. This unique set of outer table records are called as outer table BlockJoinRowList.
3. The OpenAccess SDK SQL engine calls EXECUTE for records of the next inner table, t2. When the IP requests conditions using dam_getOptimalIndexAndConditions(), dam_getSetOfConditionLists(), or dam_getRestrictionList(), the OpenAccess SDK SQL engine expands any join conditions to pass the column values of each record of the outer table BlockJoinRowList. This allows the IP to process matching records of the inner table for all rows in the outer table BlockJoinRowList. The implementation of block joins is transparent to the IP. The IP EXECUTE function should build result rows using the conditions that are passed and call dam_isTargetRow and dam_addRowToTable.
The OpenAccess SDK SQL engine checks the internal flag to see if the query is on an inner table in the block join mode and modifies the behavior of the OpenAccess SDK SQL engine functions.
• dam_isTargetRow() validates all conditions except join conditions.
• dam_addRowToTable() saves the row in the inner table BlockJoinRowList.
If the IP returns all matching records, the OpenAccess SDK SQL engine continues to the next step of combining the outer table records with the inner table BlockJoinRowList. If the IP is in cursor mode, the OpenAccess SDK SQL engine calls IP EXECUTE(DAM_FETCH) until all matching records of the inner table are returned.
4. The OpenAccess SDK SQL engine finds the matching inner table record (from the inner table BlockJoinRowList) for each outer table row being processed and build the join row for table t1t2.
5. The OpenAccess SDK SQL engine proceeds to process the next inner table. It uses a block of t1t2 table records to find matching t3 records using steps 2 through 4.
6. When processing of the last inner table is complete, the OpenAccess SDK SQL engine repeats steps 2 through 5 for the next block of outer table records until all outer table records are processed.
