Block join execution

This section describes how block join execution works when inner tables are queried for join columns when they are indexed and when they are not indexed.

Join columns are indexed

SELECT ... FROM emp, dept WHERE emp.deptno = dept.deptno

Assume that the join order for the processing is emp, dept and dept has an index on deptno column.

1. The OpenAccess SDK SQL engine first queries for emp table records.
2. For each block of dept table records from the result of step 1, OpenAccess SDK queries the dept table and passes the search condition on deptno as deptno in (1, 2, 5). The IP uses indexed file access to return the matching dept records.
3. The OpenAccess SDK SQL engine combines the results of Steps 1 and 2 to prepare the final result.

Join columns are not indexed

SELECT ... FROM emp, dept WHERE emp.deptno = dept.deptno

Assume that the join order for processing is emp, dept and that the dept table’s deptno column is not indexed.

1. The OpenAccess SDK SQL engine first queries for emp table records.
2. For each block of emp table records from the result of Step1, OpenAccess SDK SQL engine queries the dept table. The IP does a full table scan and returns all records in dept. OpenAccess SDK SQL engine filters these by applying the condition on deptno(deptno IN(1,2,5)).
3. In Step 2, the IP can indicate to the OpenAccess SDK SQL engine that it is doing a full table scan. The OpenAccess SDK SQL engine caches the results to be used for the entire join processing. No further queries on dept table is sent to the IP.
4. When the IP is returning full table scan results of dept table, if it exceeds the configurable limit of MAX_TABLE_ROWSET_SIZE, the OpenAccess SDK SQL engine does not cache the rowset records; it only retains the records applicable for the current outer table block join rows. For the next set of outer table rows, the OpenAccess SDK SQL engine uses Step 2 to query the IP again.

If the inner table is being processed without search conditions, the OpenAccess SDK SQL engine checks the inner row to match the join condition with at least one of the outer table rows before adding them to BlockJoinRowset. This minimizes the BlockJoinRowset size.