This article covers Collaborative Query Execution (CQE) for filters. For information about CQE with joins 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 filters. As an example take the following query:
select EMPLOYEE.first_name from EMPLOYEE, ADDRESS where EMPLOYEE.FIRST_NAME = ‘Susan’
Just before the pass-down optimization step (described in the AETree optimization article), the AETree looks like this:
AELiteral: Susan; Character String Literal
and after the pass-down step, which Codebase handles, the AETree looks like this:
What has happened here is that Codebase has handled the filter (EMPLOYEE.FIRST_NAME = ‘Susan’) in the DSII and returned a CBFilterResult representing that result to the SQLEngine. The AETable node for the EMPLOYEE node actually represents this, and you can see this yourself if you modify the CBFilterResult::GetTableName() function to alter the returned table name. The SQLEngine uses this result returned from the DSII to do the filtering for it, and discards the AESelect node that was previously used.
The steps for how this happened are roughly as follows:
- Attempt to pass-down the entire condition for the AESelect node.
- Is passing down the condition successful (Passdown() returned true)?
- Yes, Passdown() returned true. Call TakeResult() and substitute the returned result in for the AESelect node.
- No, Passdown() returned false. Is there only one CNF clause (see below for more information on this step)?
- i. Yes, only one clause. Call TakeResult() and substitute the returned result, if any, for the AETable operand of the AESelect.
- ii. 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?
- Yes, Passdown() returned true. Record the clause and continue to the next clause. If no more clauses, go to step ii.3.
- 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.
- Call TakeResult() and substitute the returned result, if any, for the AETable operand of the AESelect. 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:
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 filter is to be applied on the result of a sub-section of the AETree, and that section 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 WHERE t1.FIRST_NAME = ‘Susan’
If the DSII can’t apply the filter “NUM_SALARY > 10000” then it would also not be able to apply the filter “FIRST_NAME = ‘Susan'”, as that filter would need to be applied to the result of “select * from EMPLOYEE where NUM_SALARY > 10000”.
The DSIExt classes that are actually involved in the above CQE process are:
- A DSII level IResult representing the filtered result
For Codebase, the actual classes are the following:
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 filtered result will be returned via TakeResult() on the IBooleanExprHandler subclass to allow for fully, or partially, 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
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.