> 1. Is it send the whole query to the real DB and fetch data for the query
> 2. Or it fetch whole table and then do the where condition in the teiid side
That depends upon the capabilities for the source. Each translator reports what SQL it understands to the engine for planning - Translator Capabilities - Teiid 8.11 - Project Documentation Editor
Also for more complex scenarios it can depend upon the structure of the sql since some predicates must be applied after a join, limit, etc. which may not have been pushed so the predicate won't have been pushed.
Yes, Teiid will pushown the query with criteria *if* the source is capable of handling the criteria. So, in the case of relational databases such as Oracle, DB2 etc, the criteria is pushed down. However, if the source is File or Web service it may not. Every translator written for Teiid defines their capabilities as to what they can support executing at the source, based on these Teiid query engine can intelligently plan the query and then rewrite the query that it can execute at source, if any part is left over that remaining operation is handled at the Teiid engine level. We call this "pushdown" functionality, this is one of main features that makes the Teiid. Teiid can pushdown joins, functions, sorting etc. For more advanced features see https://docs.jboss.org/author/display/TEIID/Federated+Optimizations
Note that Teiid even supports you to define many query hints, that can influence the optimizer to plan the query in certain way, if you think the query generated is not optimal. Also, having the cardinality information (how many distinct rows are there in that table in the source, see property on table in Designer) filled on all the tables will help generate optimal plans.
Thanks Steven and Ramesh,
This works for SqlServer translator or Sql Source, which we are using. So via JDBC data pull, this will be appropriate.
We are also using oData protocol to pull data out of the VDB. So will the filters be passed along with the query to the source using oData Protocol?
For example: http://xxx.yyy.com:8080/OData/OData.svc/Students?$filter=std_name eq 'John'
> We are also using oData protocol to pull data out of the VDB. So will the filters be passed along with the query to the source using oData Protocol?
incoming oData queries are first translated to an equivalent Teiid SQL query, then that query is executed - so all of the same statements and caveats apply from above.