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 EXISTs and scalar subquery that are not pushed to the source can be pre-evaluated prior to source command formation.
- Correlated subqueries used in DELETEs or UPDATEs that are not pushed as part of the corresponding DELETE/UPDATE will cause 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, then an exception will be thrown.
WHERE or HAVING clause IN, Quantified Comparison, Scalar Subquery Compare, and EXISTs predicates can take the MJ (merge join), DJ (dependent join), or NO_UNNEST (no unnest) hints appearing just before the subquery. The MJ hint directs the optimizer to use a traditional, semi-join, or anti-semi-join merge join if possible. 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.
Merge Join Hint Usage
SELECT col1 FROM schm.tbl WHERE col2 IN /*+ MJ*/ (SELECT col1 FROM schm2.tbl2)
CODEDependent Join Hint Usage
SELECT col1 FROM schm.tbl WHERE col2 IN /*+ DJ */ (SELECT col1 FROM schm2.tbl2)
CODENo Unnest Hint Usage
SELECT col1 FROM schm.tbl WHERE col2 IN /*+ NO_UNNEST */ (SELECT col1 FROM schm2.tbl2)
CODEplease note
- Be sure that there is no whitespace between
/*
and+
> when using e.g./*+ MJ */
- Configure your SQL client not to remove multi line comments (in Squirrel: Session->Session Properties->SQL-> Remove multi line comment (
/* ... */
)
- Be sure that there is no whitespace between
- The system property
org.teiid.subqueryUnnestDefault
controls whether the optimizer will by default unnest subqueries. The default is false. If true, then 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 EXISTs and IN predicates that can be rewritten to a traditional join with the semantics of the semi-join can be preserved if the system property
org.teiid.subqueryUnnestDefault
is set to true or the subquery has an MJ hint. - The planner will always convert to antijoin or semijoin variants if costing is favourable. Use a hint to override this behaviour needed.
- EXISTs 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.