Database Reference
In-Depth Information
Subquery Unnesting
The purpose of
subquery unnesting
is to inject semi- (
IN
,
EXISTS
), anti-join (
NOT IN
,
NOT EXISTS
), and scalar
subqueries into the
FROM
clause of the containing query block, and to transform them into inline views. Some
unnestings are performed as heuristic-based query transformations, and others are carried out as cost-based query
transformations. The main reason for applying this query transformation is to enable all available join methods.
In fact, without subquery unnesting, a subquery might have to be executed once for every row returned by the
containing query block (Chapter 10 provides more information about this). Subquery unnesting can't always be
applied, though. For example, unnesting isn't possible if a subquery contains some types of aggregation, or if it
contains the
rownum
pseudocolumn. Semi- and anti-join subqueries containing set operators can only be unnested as
of version 11.2. In addition, from version 12.1 onward, scalar subquery unnesting has been improved to process scalar
subqueries in
SELECT
clauses.
The following example, based on the
subquery_unnesting.sql
script, illustrates how this query transformation
works:
SELECT *
FROM t1
WHERE EXISTS (SELECT 1
FROM t2
WHERE t2.id = t1.id
AND t2.pad IS NOT NULL)
Unnesting a subquery can be summarized in two steps. The first step, as shown in the following query, is to
rewrite the subquery as an inline view. Note that what follows isn't a valid SQL statement, because the operator
implementing the semi-join (
s=
) isn't available in the SQL syntax (it's used internally by the SQL engine only):
SELECT *
FROM t1, (SELECT id
FROM t2
WHERE pad IS NOT NULL) sq
WHERE t1.id s= sq.id
The second step, as shown here, is to rewrite the inline view as a regular join:
SELECT t1.*
FROM t1, t2
WHERE t1.id s= t2.id AND t2.pad IS NOT NULL
Although the preceding example is based on a semi-join, this query transformation is also used for anti-joins.
The only difference is that instead of using the semi-join operator (
s=
), it uses the anti-join operator (
a=
). This is
another operator that's used internally by the SQL engine only.
Subquery Coalescing
The purpose of
subquery coalescing
is to combine equivalent semi- and anti-join subqueries into a single query block.
The main reason for applying this heuristic-based query transformation, which is available as of version 11.2, is to
reduce the number of table accesses, and thus to reduce the number of joins.
