-
1. Re: dependent join and aggregation pushdown
rareddy Sep 23, 2014 10:37 AM (in response to sudeepd)1 of 1 people found this helpfulTeiid will not pushdown like that, the right side has no knowledge of the schema, nor it may not have capabilities to handle such cases. What is leftTable mean on DB as is?
The alternative you can do is to create a FOREIGN TEMP TABLE as defined here Temp Tables - Teiid 8.9 (draft) - Project Documentation Editor, populate the needed columns from left side, then issue a query like above replacing the left with temp table. Here depending upon the table size you trade processing time vs population time. You should be able to accomplish this in virtual procedure.
alternatively, if you are writing custom connector take look at "Full Pushdown" here Dependent Join Pushdown - Teiid 8.9 (draft) - Project Documentation Editor
Ramesh..
-
2. Re: dependent join and aggregation pushdown
shawkins Sep 23, 2014 10:48 AM (in response to rareddy)To clarify, Teiid will not do a full dependent join pushdown or data shipment join by default. It is currently hint driven and requires the translator to support the feature. Our JDBC translator will support this with the enableDependentJoins option turned on and if they have a Hibernate dialect that supports temporary tables (which includes most common databases). If so then yes, we will create a temporary table on the source put the entirety of one side of the relation there and allow processing including aggregation and what else is able to be pushed down to be performed at the source.
-
3. Re: dependent join and aggregation pushdown
sudeepd Sep 23, 2014 12:37 PM (in response to shawkins)Thank you Ramesh and Steve for the response.
I understand the full dependent join pushdown, and that it needs appropriate translator support. What was not clear to me was this
If teiid sees my custom translator to support full dependent join pushdown , and if provided with the hint, then will the query planner decompose this to
a. Do a full dependent join pushdown, get the join results back , and then run aggregations
OR
b. Do a full dependent join pushdown AND push the aggregations to the source ( This means that teiid doesnt see the joined table, and doesnt run any aggregation, rather gets the aggregate results from the source)
If it is a. above , and the dep joins results in a very large resultset then its a problem for us,Since its the aggregate that I care about, I would rather have the source handle both the aggregation and the join together.
If it is b. then then thats exactly what I need.
Based on Stevens response, I believe its b (subject to translator support and hints) , would appreciate a confirmation just to make sure I got it right
-
4. Re: dependent join and aggregation pushdown
rareddy Sep 23, 2014 1:00 PM (in response to sudeepd)1 of 1 people found this helpfulIt will be (b) granted your custom translator supports the Aggregate functions that are being pushed down. If not they will be handled in the engine.