This article covers Collaborative Query Execution (CQE) for joins. For information about CQE with filters or aggregations please see the following:

It is assumed that the reader understands the rough idea of what CQE is and how it works. Please see  AETree optimization for information.

The Codebase sample driver is used to illustrate CQE for joins. As an example take the following query:

    select EMPLOYEE.first_name from EMPLOYEE INNER JOIN DEPT ON EMPLOYEE.DEPT = DEPT.DEPT_ID

Just before the pass-down optimization step (described in the AETree optimization article), the AETree looks like this:

 

and after the pass-down step, which Codebase handles, the AETree looks like this:

 

Note:  Codebase can’t properly filter out NULLs, so it allows the SQLEngine to continue processing the joined result that it returns to ensure the correct result is returned. If it was able to properly filter the result set, then the AETree would look like this:

 

What has happened here is that Codebase has handled the join condition (EMPLOYEE.DEPT = DEPT.DEPT_ID) in the DSII and returned a CBJoinResult representing that joined result to the SQLEngine. The CBJoinResult AETable node represents this as can be seen in the above AETree. When the DSII fully handles the join condition, the SQLEngine uses the returned result to do the join and discards the AEJoin node. As noted above, Codebase can’t fully handle the join condition so the SQLEngine works in collaboration with it to properly join the results. This can be seen in the first AETree where there is now an AESelect instead of an AEJoin node.

The steps for how this happened are roughly as follows:

  1. Attempt to pass-down the entire join condition for the AEJoin node.
  2. Is passing down the condition successful (Passdown() returned true)?
    1. Yes, Passdown() returned true. Call TakeResult() and substitute the returned result in for the AEJoin node.
    2. No, Passdown() returned false. Is there only one CNF clause (see below for more information on this step)?
          1. Yes, only one clause. Is there a result returned from TakeResult()?
            1. Yes, a result is returned. Create an AESelect node with the same filter condition as the AEJoin join condition, and use the returned result as the operand for the AESelect node. Replace the AEJoin with the AESelect node.
            2. No, no result is returned. Leave the AETree as is.
          1. No, more than one clause. Attempt to pass-down each CNF clause one-by-one. For each CNF clause passed down, is passing down the clause successful?
            1. Yes, Passdown() returned true. Record the clause and continue to the next clause. If no more clauses, go to step ii.3.
            2. No, Passdown() returned false. Attempt to break clause down according to step ii and pass-down, then continue to the next clause. If no more clauses, go to step ii.3.
            3. Create an AESelect node with the same filter condition as the AEJoin join condition, and use the returned result as the operand for the AESelect node. Replace the AEJoin with the AESelect node. Remove from the AESelect condition any clauses that were recorded in step 1.

A CNF clause would be any clause that is separated by an &&. For example, with the condition (A || B) && (C && D) we could break it down by one step to the following two clauses:

•    (A || B)
•    (C && D)

The first clause could not be broken down any further, but the second could be broken down into:

•    C
•    D

The SQLEngine will recursively try and pass-down CNF clauses to the DSII for it to handle, until the DSII either handles the full clause or there are no more clauses to pass down. Note that (A || B) could not be broken down and passed down to the DSII because if the DSII filtered out rows according to clause A, then it may have filtered out rows that would pass for clause B, thus causing an incorrect result to be returned.

Note:  That if a join is to be applied where one of the join operands had an operation which could not be handled via CQE by the DSII, then the filter will not be passed to the DSII. This is because if the DSII could not handle the operation that would result in the result set to be filtered, then it would not be possible for it to then apply a further filter on that result set. To illustrate, take the following example query:

select * from (select * from EMPLOYEE where NUM_SALARY > 10000) t1 INNER JOIN DEPT ON t1.DEPT = DEPT.DEPT_ID

If the DSII can’t apply the filter “NUM_SALARY > 10000” then it would also no be able to apply the join condition “t1.DEPT = DEPT.DEPT_ID”, as that condition would need to be applied to the result of “select * from EMPLOYEE where NUM_SALARY > 10000” and the DEPT table.

The DSIExt classes that are actually involved in the above CQE process are:

  • DSIExtOperationHandlerFactory
  • IBooleanExprHandler
  • A DSII level IResult representing the joined result.

For Codebase, the actual classes are the following:

  • CBOperationHandlerFactory
  • CBJoinCondHandler
  • CBJoinResult

The DSIExtOperationHandlerFactory, and subclasses, are responsible for creating the handlers for passing down filters, joins, and aggregations to the DSII.
The individual handlers will be created and used as needed by the SQLEngine during the pass-down optimization phase.

Finally, the joined result will be returned via TakeResult() on the IBooleanExprHandler subclass to allow for fully, or partially, joined and filtered results.

As noted in the break-down of steps above, when passing down a condition to the IBooleanExprHandler, the DSII may return false to the Passdown() function to indicate it is not fully handling the condition and the SQLEngine will still call TakeResult(). This allows for the DSII to partially handle a condition and return the partially filtered result while having the SQLEngine still apply the condition and filter out the rest of the results.

There are base classes supplied for the Boolean expression handler that can be used to simplify implementation. The hierarchy is:

IBooleanExprHandler -> DSIExtAbstractBooleanExprHandler -> DSIExtSimpleBooleanExprHandler

Note: This is the same hierarchy used by the CQE for filters.

Typically you will subclass the DSIExtSimpleBooleanExprHandler, which provides support for some common Boolean conditions. If you need more functionality then you should override other functions in the parent classes as needed, or subclass DSIExtAbstractBooleanExprHandler. The Passdown*() functions will take AETree nodes, which should be inspected to see if your DSII can handle the condition. If so, then Passdown() should return true and TakeResult() should return a result set representing the filtered result.