-
15. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 24, 2011 1:14 PM (in response to fihtisham)Farrukh,
Talking with SteveH, he said that the pushdown functions from projected columns is not supported except for simple cases in the Teiid currently. Teiid has enhancement JIRA on this https://issues.jboss.org/browse/TEIID-964, we may bump up priority on this depending upon the feature volume.
However, you can use the pushdown function on criteria, or in inline view that will get pushdown correctly. Also, another hackish workaround is, create view for every source table, and add extra column on that view that using this pushdown function on the specified column and create federation on top of that view. Since Teiid only fetches the columns required by the user query, when not used these extra columns get dropped and never calculated and thus not contribute to any degradation in the performance.
Hope this helps.
Ramesh..
-
16. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 26, 2011 8:59 AM (in response to rareddy)Hi Ramesh,
I am not able to fully understand the workaround suggested by you as
"create view for every source table, and add extra column on that view that using this pushdown function on the specified column and create federation on top of that view"
Could you please ellaborate it more? It would be great if you could make the sample working which I attached by applying the workarround. If possible!
Regards,
Farrukh
-
17. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Oct 26, 2011 12:46 PM (in response to fihtisham)See attached project set, looks for table "States3"
Ramesh..
-
DemoTeiidModelProject.tar.gz 14.5 KB
-
-
18. Re: How can I handle a pushdown=required user defined function in the custom translator
fihtisham Oct 27, 2011 1:51 AM (in response to rareddy)Thanks for the sample Ramesh!
We cannot use this approach as it hardcodes unit of measure at design time. We want this information to be taken from the user. Means the client application will create dynamic sql on the basis of user input and that sql will be executed on teiid. Is there any way by which I can accomplish this?
Thanks,
Farrukh
-
19. Re: How can I handle a pushdown=required user defined function in the custom translator
shawkins Oct 28, 2011 9:54 PM (in response to fihtisham)Hi Farrukh,
https://issues.jboss.org/browse/TEIID-964 has been implemented for 7.6. There is much more that can be done along these lines, but it does address the specific situation of having a must pushdown function in the select clause above a federation point.
I'm not sure there is a good workaround for this situation without the fix. If you have full control over the generated sql you could use an inline view that won't get removed. Since we don't have a hint to preserve an inline view, this can be done with a dummy ordered limit. This would look like: SELECT * FROM (SELECT func(x, y, z), ... from source_a ORDER BY col LIMIT 2147483647) as x, source_b where x.col = source_b.col
Steve
-
20. Re: How can I handle a pushdown=required user defined function in the custom translator
shawkins Nov 2, 2011 4:01 PM (in response to shawkins)Just to follow up, https://issues.jboss.org/browse/teiid-1805 applies the NO_UNNEST hint to inline or full views in 7.6 so we also have a way to preserve inline view layers when needed.
-
21. Re: How can I handle a pushdown=required user defined function in the custom translator
rareddy Nov 3, 2011 10:03 AM (in response to fihtisham)Steve,
So we can write the query with out the Order by or Limit like?
SELECT * FROM /*+NO_UNNEST*/(SELECT func(x, y, z), ... from source_a) as x, source_b where x.col = source_b.col
Ramesh..
-
22. Re: How can I handle a pushdown=required user defined function in the custom translator
shawkins Nov 3, 2011 10:19 AM (in response to rareddy)Ramesh,
In 7.6, yes you could. However for this simple situation there's no need since it's already covered by https://issues.jboss.org/browse/TEIID-964.
Steve