[teiid-issues] [JBoss JIRA] (TEIID-2555) Support pushdown of entire dependent joins

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-2555?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12790419#comment-12790419 ] 

Steven Hawkins commented on TEIID-2555:
---------------------------------------

The initial commit has been corrected and trivial scenarios have been verified end-to-end against oracle.  However a lot of testing will be needed to validate this for basically all types against all possible supported sources: DB2, Derby, H2, HSQL (2.0+), MySQL, Oracle, PostgreSQL, SQLServer, Sybase

The feature is safe-guarded - it has to be specifically enabled via the makedep join hint and requires the translator to have enableDependentJoins set.  With further testing we can begin to relax these conditions and/or add additional capabilities to ensure that the independent side of the relation will be correctly handled.

As mentioned earlier at some point the full and key set pushdown handling may be consolidated further.  The full pushdown style is a little easier to implement (the method in JDBCQueryExecution is about half as long) - but using the join form for the key pushdown requires that the independent set is distinct (which we are now ensuring).  However in the future there are some instances where the notion of a semi-join may be more appropriate and the current 8.5 will basically work in 8.4.1 should a backport be necessary.
                
> Support pushdown of entire dependent joins
> ------------------------------------------
>
>                 Key: TEIID-2555
>                 URL: https://issues.jboss.org/browse/TEIID-2555
>             Project: Teiid
>          Issue Type: Sub-task
>          Components: Connector API, Query Engine
>            Reporter: Steven Hawkins
>            Assignee: Steven Hawkins
>             Fix For: 8.5
>
>
> If the data volume is not too large then in many circumstances pushing down the entire independent side of the join to perform the entire join at the source can enhance performance.
> This would likely be built upon TEIID-2249 to make use of a make dep hint option.  It would also likely be an expansion of the common table pushdown logic - but will require more extensive planning changes as the default logic is geared toward only the equi-join columns.  
> It has also been requested that the default preference for pushdown be based upon the estimated data width.
> There is an issue with the form of the plan as with the existing logic it would be nearly impossible to back out of the decision to perform the full pushdown (which is why a hint is initially preferable).

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira