Database Reference
In-Depth Information
Full Outer Join
The purpose of full outer join , which is a heuristic-based query transformation, is to translate a full outer join into
a compound query that uses the UNION ALL set operator to combine the rows returned by two joins: an outer join
and an anti-join. In addition, if the predicate specified in the ON clause references not-nullable columns that define a
foreign key constraint which is either enforced or marked RELY , this query transformation can even translate the full
outer join into a query that at runtime will be executed as a left outer join.
even though the full outer join syntax is available as of version 9.0, prior to version 11.1 the SQL engine wasn't
able to natively execute a full outer join. as a result, queries containing full outer joins are translated into something that
the SQL engine can work on. as of version 11.1, with the introduction of native full outer joins, this is no longer the case.
Note
The following example, based on the full_outer_join.sql script, illustrates how this query transformation
works. Notice that the query uses the FULL OUTER JOIN syntax in the FROM clause:
SELECT *
FROM t1 FULL OUTER JOIN t2 ON t1.n = t2.n
The query transformation produces the following query. Notice that only the first of the two joins defined in the
inline view is an outer join. The second one contains, as explained earlier in the “Subquery Unnesting” section,
a special operator ( a= ) to implement the anti-join:
SELECT id1 AS id, n1 AS n, pad1 AS pad, id, t1_id, n, pad
FROM (SELECT t1.id AS id1, t1.n AS n1, t1.pad AS pad1, t2.id, t2.t1_id, t2.n, t2.pad
FROM t1, t2
WHERE t1.n = t2.n(+)
UNION ALL
SELECT NULL, NULL, NULL, t2.id, t2.t1_id, t2.n, t2.pad
FROM t1, t2
WHERE t1.n a= t2.n) vw_foj
Table Expansion
The purpose of table expansion , which is available as of version 11.2, is to enable the use of as many index scans as
possible by also leveraging partially unusable indexes. The essential thing to recognize is that this cost-based query
transformation is only considered when three basic conditions are fulfilled:
A partitioned table is involved.
The partitioned table has a local index having unusable partitions or, as of version 12.1,
a partial index.
The SQL statement to be optimized has to process data underlying both usable and unusable
index partitions.
Prior to version 11.2 in such a situation, an index scan based on the partially unusable index isn't possible, and
the whole index is completely ignored. Instead, a full table scan has to be used. But with table expansion, the query
optimizer can take advantage of usable index partitions when they exist and fall back to a full partition scan for
unusable index partitions.
 
 
Search WWH ::




Custom Search