Materialization

Materialization means that a subquery is evaluated only once. 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 will be thrown at the beginning of execution.

Subquery materialization is detected prior to optimization, which allows the optimizer to see a materialized subquery as an unknown constant value. The comparison is thus optimizable.

In other words, 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.