Subquery Optimization

EXISTS subqueries are typically rewritten to SELECT 1 FROM ... to prevent unnecessary evaluation of SELECT expressions;
Quantified compare SOME subqueries are always turned into an equivalent IN predicate or comparison against an aggregate value. e.g. col > SOME (SELECT col1 FROM table) would become col > (SELECT min(col1) FROM table);
Uncorrelated EXIST queries and scalar subquery that are not pushed to the source can be pre-evaluated before source command formation;
Correlated subqueries used in DELETE or UPDATE queries that are not pushed as part of the corresponding DELETE/UPDATE will cause the Data Virtuality Server to perform row-by-row compensating processing. This will only happen if the affected table has a primary key. If it does not, an exception will be thrown;
WHERE or HAVING clause IN, quantified comparison, scalar subquery compare, and EXIST predicates can take the MJ (merge join), DJ (dependent join), or NO_UNNEST (no unnest) hints appearing just before the subquery. If possible, the MJ hint directs the optimizer to use a traditional, semijoin, or anti-semi-join merge join. The DJ is the same as the MJ hint, but additionally directs the optimizer to use the subquery as the independent side of a dependent join if possible. The NO_UNNEST hint, which supersedes the other hints, will direct the optimizer to leave the subquery in place.
Here are examples for each of them:
1. Merge join hint:
SQL
```
SELECT col1
FROM schm.tbl
WHERE col2 IN /*+ MJ*/ (SELECT col1 FROM schm2.tbl2)
```
2. Dependent join hint:
SQL
```
SELECT col1
FROM schm.tbl
WHERE col2 IN /*+ DJ */ (SELECT col1 FROM schm2.tbl2)
```
2. NO_UNNEST hint:
SQL
```
SELECT col1
FROM schm.tbl
WHERE col2 IN /*+ NO_UNNEST */ (SELECT col1 FROM schm2.tbl2)
```
Be sure that there is no whitespace between /* and +> when using e.g. /*+ MJ */.
Configure your SQL client not to remove multi-line comments.
Here is how to do this in Squirrel: Session -> Session Properties -> SQL -> Remove multi-line comment (/* ... */).
The system property org.teiid.subqueryUnnestDefault controls whether the optimizer will, by default, unnest subqueries. The default is FALSE. If TRUE, most non-negated WHERE or HAVING clause non-negated EXISTS or IN subquery predicates can be converted to a traditional merge join or as antijoin or semijoin variants;
WHERE clause EXIST and IN predicates that can be rewritten to a traditional join with the semantics of the semijoin can be preserved if the system property org.teiid.subqueryUnnestDefault is set to TRUE or the subquery has an MJ hint;
If the costing is favourable, the planner will always convert to antijoin or semijoin variants. Use a hint to override this behaviour if needed;
EXIST and scalar subqueries that are not pushed down, and not converted to merge joins, are implicitly limited to 1 and 2 result rows, respectively;
Conversion of subquery predicates to nested loop joins is not yet available.