Movatterモバイル変換

10.2.2.1 Optimizing IN and EXISTS Subquery Predicates with Semijoin and Antijoin Transformations

10.2.2.2 Optimizing Subqueries with Materialization

10.2.2.3 Optimizing Subqueries with the EXISTS Strategy

10.2.2.4 Optimizing Derived Tables, View References, and Common Table Expressionswith Merging or Materialization

10.2.2.5 Derived Condition Pushdown Optimization

• For a subquery used with anIN,= ANY, orEXISTS predicate, the optimizer has these choices:

  • Semijoin

  • Materialization

  • EXISTS strategy

• For a subquery used with aNOT IN,<> ALL orNOT EXISTS predicate, the optimizer has these choices:

  • Materialization

  • EXISTS strategy

• Merge the derived table into the outer query block

• Materialize the derived table to an internal temporary table

A limitation onUPDATE andDELETE statements that use a subquery to modify a single table is that the optimizer does not use semijoin or materialization subquery optimizations. As a workaround, try rewriting them as multiple-tableUPDATE andDELETE statements that use a join rather than a subquery.