Expression subqueries that are not correlated
A subquery can be materialized if it is a noncorrelated expression subquery.
A correlated subquery is one that references columns in the outer query, and so
has to be evaluated for each row in the outer query.
For example:
SELECT * FROM Staff
WHERE id = (SELECT MAX(manager) FROM Org)
In this statement, the subquery needs to be evaluated only once.
This type of subquery must return only one row. If evaluating the subquery
causes a cardinality violation (if it returns more than one row), an exception
is thrown when the subquery is run.
Subquery materialization is detected before optimization, which allows the
optimizer to see a
materialized subquery as an unknown constant value. The comparison is therefore
optimizable.
The original statement is transformed into the following two statements:
constant = SELECT MAX(manager) FROM Org
SELECT * FROM Staff
WHERE id = constant
The second statement is optimizable.
Subqueries that cannot be flattened
Materialization of a subquery can also occur when the subquery is
nonflattenable and there is an equijoin between the subquery and another FROM
table in the query.
For example:
SELECT i, a FROM t1,
(SELECT DISTINCT a FROM T2) x1
WHERE t1.i = x1.a AND t1.i in (1, 3, 5, 7)
In this example, the subquery x1 is noncorrelated because it
does not reference any of the columns from the outer query. The subquery is
nonflattenable because of the DISTINCT keyword.
does not flatten
DISTINCT subqueries. This subquery is eligible for materialization. Since there
is an
equijoin
predicate between the subquery x1 and the table
t1 (namely, t1.i = x1.a), the
optimizer will consider
performing a hash join between t1 and x1 (with
x1 as the inner operand). If that approach yields the best
cost, materializes the
subquery x1 to perform the hash join. The subquery is evaluated
only a single time, and the results are stored in an in-memory hash table.
then executes the join
using the in-memory result set for x1.