title: Teiid not performing optimal join
body: <p>For our Teiid Springboot project we use a row filter in a where clause to determine what results a user gets.
  Example:</p>
  <pre><code>SELECT * FROM very_large_table WHERE id IN ('01', '03')
  </code></pre>
  <p>We want the context in the IN clause to be dynamic like so:</p>
  <pre><code>SELECT * FROM very_large_table WHERE id IN (SELECT other_id from very_small_table)
  </code></pre>
  <p>The problem now is that Teiid gets all the data from very_large_table and only then tries to filter with the where clause, this makes the query 10-20 times slower. The data in this very_small_tableis only about 1-10 records and it is based on the user context we get from Java.</p>
  <p>The very_large_table is located on a Oracle database and the very_small_table is on the Teiid Pod/Container. Somehow I can't force Teiid to ship the data to Oracle and perform filtering there.</p>
  <p>Things that I have tried:
  I have specified the the foreign data wrappers as follows</p>
  <pre><code>CREATE FOREING DATA WRAPPER &quot;oracle_override&quot; TYPE &quot;oracle&quot; OPTIONS (EnableDependentsJoins 'true');
  CREATE SERVER server_name FOREIGN DATA WRAPPER &quot;oracle_override&quot;;
  </code></pre>
  <p>I also tried, exists statement or instead of a where clause use a join clause to see if pushdown happened. Also hints for joins don't seem to matter.</p>
  <p>Sadly the performance impact at the moment is that high that we can't reach our performance targets.</p>