Skip to main content
Skip table of contents

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:

    SELECT col1
    FROM schm.tbl
    WHERE col2 IN /*+ MJ*/ (SELECT col1 FROM schm2.tbl2)

    2. Dependent join hint:

    SELECT col1
    FROM schm.tbl
    WHERE col2 IN /*+ DJ */ (SELECT col1 FROM schm2.tbl2)

    2. NO_UNNEST hint:

    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.
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.